On Mon, 5 Jan 2004, Matthew Stuart wrote: > I am trying to create a couple of columns (one createddate and one > updateddate) in a MySQL table that are DATETIME or TIMESTAMP values, > but I am having trouble understanding how it works. I want both columns > to auto add a date and time when a record is first inserted, but only > the updateddate column to update when the record is updated.
I would use a DATETIME for the created date, and a TIMESTAMP for the changed. The reason being that the creation date is only inserted once, and it won't change, and changed using TIMESTAMP will automaticly update when the record is changed, so no complex application logic here (well, maybe not very complex, but I won't have to think about it, it's MAGIC!). > Could someone give me the code for these two columns please? This is > what I have: > > CREATE TABLE all_articles ( > id SMALLINT UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT, > createddate DATETIME DEFAULT "0000-00-00 00:00:00" > updateddate DATETIME DEFAULT "0000-00-00 00:00:00" > article TEXT > ); CREATE TABLE all_articles ( id SMALLINT UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT, createddate DATETIME DEFAULT '0000-00-00 00:00:00', updateddate TIMESTAMP, article TEXT ); Should work. > Obviously these aren't going to auto add/update because I am not stating > a NOW() or NULL value anywhere, but I thought this would be the cleanest > way to give it to you. The way the zeros, hyphens and colons are typed > is how I would like to store my dates if at all possible. The only issue here is that when you insert a new article you need to set the createdate column to NOW(). ie, INSERT INTO all_articles (createddate, article) VALUES (NOW(), 'Article text goes here'); cheers, Tobias -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
