List:General Discussion« Previous MessageNext Message »
From:Paul DuBois Date:February 26 2002 10:56pm
Subject:Re: TIMESTAMP with DEFAULT broken?
View as plain text  
At 14:31 -0800 2/26/02, John D. Kirkpatrick wrote:
>I have tried to create a MySQL table with the following lines:
>
>$query[] = "CREATE TABLE member (
>ID        bigint(20) NOT NULL AUTO_INCREMENT PRIMARY KEY,
>userName             varchar(40) NOT NULL,
>.
>.
>.
>
>RecordCreationTime   TIMESTAMP NOT NULL DEFAULT '0',
>LastLogin            TIMESTAMP NOT NULL DEFAULT 'now()',
>RecordTouchTime      TIMESTAMP NOT NULL DEFAULT 'now()',
>)";
>
>No matter what I set the defualt values to, I get the timestamp value in the
>first timestamp field and zeros in all after the first one.

According to the manual, default values must be constants, not functions
as you are attempting to use:

http://www.mysql.com/doc/C/R/CREATE_TABLE.html

Although actually, 'now()' is a literal string, not a function.

Also, the other behavior you're observing about which TIMESTAMP
columns are updated and when, is exactly as documented in the manual:

http://www.mysql.com/doc/D/A/DATETIME.html


>
>The above give the following result:
>RecordCreationTime: 20020224233339
>LastLogin: 00000000000000
>RecordTouchTime: 00000000000000
>
>I had to currently use a separate insert statement after the table creation
>and it works fine... but the default doesn't seem to work with timestamp...
>yet no matter what I do, I always get the current timestamp if I don't
>update the first timestamp field.
>
>$query[] = "CREATE TABLE member (
>ID        bigint(20) NOT NULL AUTO_INCREMENT PRIMARY KEY,
>userName             varchar(40) NOT NULL,
>.
>.
>.
>
>RecordCreationTime   TIMESTAMP,
>LastLogin            TIMESTAMP,
>RecordTouchTime      TIMESTAMP,
>)";
>
>This STILL update the first TIMESTAMP field as a defualt when updating
>another column... but only the first TIMESTAMP field.
>I've had to put a dummy field as TIMESTAMP as the first one... the rest are
>fine.

Thread
TIMESTAMP with DEFAULT broken?John D. Kirkpatrick26 Feb
  • Re: TIMESTAMP with DEFAULT broken?Paul DuBois26 Feb