Mysql Timestamp

[spadin]

Hi,

 

I'm designing a new table and I would like for MySQL to set a default timestamp everytime a record is created. Normally you can use DEFAULT NOW() but I keep getting all zeroes when I use this. Is there something wrong with my sql? Or do the TCH servers not support this feature? Here is my sql.

 

>CREATE TABLE `[edited_out]`.`users` (
 `id` VARCHAR(20) NOT NULL DEFAULT '',
 `password` VARCHAR(20) NOT NULL DEFAULT '',
 `date_created` DATETIME NOT NULL DEFAULT 'CURRENT_TIMESTAMP',
 `tolerance` INT(1) NOT NULL DEFAULT 4,
 `real_name` VARCHAR(40) DEFAULT NULL,
 PRIMARY KEY(`id`)
)
ENGINE = MYISAM
CHARACTER SET utf8;

 

I tried the above with and without the quotations, nothing seems to work.

[spadin]

Okay,

 

I decided to play around a little more with the database and was able to make it set a default timestamp. I had to make the field a TIMESTAMP, instead of DATETIME. Then erase everything from the DEFAULT clause. It now automatically enters the current time when creating a new record.

[Samrc]

Glad you got it working!

And thanks for posting your resolution. Might come in handy for someone else too!

[spadin]

So I came upon a different problem. The timestamp column that I setup which is called "Created_At" keeps updating everytime I update the record which is not very useful I wanted that column to have the date and time of when it was created and no more changes thereafter.

 

A workaround to this problem is to create two TIMESTAMP fields. I called mine "Update_At" and "Created_At". The trick is to have the "Updated_At" column before the "Created_At" column and set the default of both to NULL.

 

What MySQL will do is update the "Updated_At" column everytime the record is updated, but it will only add a timestamp to "Created_At" when the record is first created. Which is exactly what I need.

 

You need this workaround for MySQL servers that are pre-4.1.2 and TCH currently has version 4.1.18

 

Now I understand when the server techs say updating the servers can get tricky... I believe if I use this workaround and then MySQL is upgraded then my data can become unreliable.

 

Hope this serves well for future reference,

Sandro

 

EDIT: More information can be found here:

http://dev.mysql.com/doc/refman/4.1/en/timestamp-4-1.html

Edited May 17, 2006 by spadin