Mysql 3.23 Datetime Problem

[energylevel]

I'm trying to use a datetime field in a MySQL 3.23 to record the date when a record is created, I've tried setting default to NOW() CURRENT_TIMESTAMP and CURRENT_DATE but none of these seem to work and the default keeps automatically changing back to 0000-00-00 00:00:00 … has anyone got a suggestion of what is wrong please ??

Thanks ……

[peterlaursen]

3.23 does not support TIMESTAMP-defaults like CURRENT_TIMESTAMP (or the synonyms NOW() or localtimestamp). You cannot even use constants as defaults like '20060615210000'.

Also a functionality like 4.1 and 5.. '… on update CURRENT_TIMESTAMP' is not possible.

With 3.23 (and 4.0) your application(s) will have to handle that. An application can INSERT or UPDATE a TIMESTAMP with NOW() – the server itself has no functionality to do so! Not as 'default' and not as 'on update ..' .

Another (and common) way to set CURRENT_TIME with MySQL before 4.1 is to define the TIMESTAMP as NOT NULL and explicitly set it NULL from the application(s). That works with THE FIRST TIMESTAMP column of a table (when declared NOT NULL).

MySQL documentation at:

http://dev.mysql.com/doc/refman/4.1/en/tim…mp-pre-4-1.html (3.23 and 4.0)

http://dev.mysql.com/doc/refman/4.1/en/timestamp-4-1.html (4.1 – 5.x is basically the same but not quite!)

So basically you got yourself a nice coding job with doing that from your application(s) 😀

… there is SOME reason to upgrade software from time to time!

As far as SQLyog 5.1.x is concerned it supports the 4.1++ 'default' and 'on update' CURRENT_TIMESTAMP 'automatically' (that is it does not 'override' the server). With MySQL 3.23 and 4.0 you can right-click a cell and 'set NULL'. That is the fastest way I guess.

[peterlaursen]

a note on DATETIME vs. TIMESTAMP types.

The trick with 'setting NULL' only works with TIMESTAMPs (and only the first of a table) and not DATETIMEs.

However DATETIMES do accept constants as defaults.

Another difference is that DATETIMEs go 'longer back in time' than TIMESTAMPs do. DATETIMES basically support the time of the Gregorian Calendar. Timestamps start (as far as I remember) around 1970 … But don't 'hang me' on that .. check the docs yourself!

In MySQL 3.23 and 4.0 a TIMESTAMP can have different lenghts. TIMESTAMP(14) is default and is a YYYYMMDDHHMMSS -format. It is simply stored as a 14 character string like that. Just as a DATETIME is.

[Tuff Fidel]

how great would it be if this forum allowed AVATARS! 🙂

[peterlaursen]

AVATARS .. It does .. but restricted to 64*64 as far as I remember.

click “my controls”

[Author]

Posts