Created Date/Time

[lyric0n]

Hello,
I would like to keep a row in my record that contains information on when the record was created and one when the record was last updated. What is the easiest way to go about doing this in MySQL?

Thanks,
Chris

 

[guelphdad]

two fields. One a timestamp which will update each time the row is updated or originally inserted (i.e. if never updated).

a second which is datetime which you use now() to insert the date and time when you originally insert your record.

 

[lyric0n]

Here is what I have tried, but I get errors...

CREATE TABLE splash (
id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
title VARCHAR(100),
desc_brief varchar(1024),
posted_dt as date default current_date
rev_dt as datetime on update current_timestamp
);

Any ideas?

 

[r937]

remove the word "as" (twice)

default values must be constants, so remove DEFAULT CURRENT_DATE

ON UPDATE is intended for foreign keys, so remove that clause too

r937.com | rudy.ca

 

[lyric0n]

Is there anything that I should add to make sure that the SQL table will automatically insert the proper data and time when a record is inserted/modified?

Thanks.

 

[r937]

read up on TIMESTAMP behaviour in the manual



r937.com | rudy.ca

 

[guelphdad]

yeah like I suggested above. :-D