What is the best way to customize fields with recent changes and add dates?

Question

Hi, I would like to set and forget two fields to track the date the record was added, as well as the date the record was last modified in the mySQL database.

I use "ON UPDATE CURRENT_TIMESTAMP" and was hoping I would just change UPDATE to INSERT.

Bad luck. Can someone give me a head on the best way to achieve this? - preferably inside the database itself.

Answer 1

This assumes MySQL 5. Just add two triggers:

create table foo (a1 INT, created timestamp, updated timestamp) engine=innodb; DELIMITER | CREATE TRIGGER foo_created BEFORE INSERT ON foo FOR EACH ROW BEGIN SET new.created := now(); SET new.updated := now(); END; | CREATE TRIGGER foo_updated BEFORE UPDATE ON foo FOR EACH ROW BEGIN SET new.updated := now(); END; | DELIMITER ; insert into foo (a1) values(7); select * from foo; update foo set a1=9; 

Answer 2

You basically need both columns to set as timestamps with the default values ​​of CURRENT_TIMESTAMP . Unfortunately, this is not allowed in MySQL:

 Error Code: 1293 Incorrect table definition; there can be only one TIMESTAMP column with CURRENT_TIMESTAMP in DEFAULT or ON UPDATE clause 

You cannot have two timestamp columns, although you only need to have the default value of CURRENT_TIMESTAMP and the other is UPDATE CURRENT_TIMESTAMP , this is still not allowed.

It’s best to specify here:

 CREATE TABLE `test` ( `addedDate` dateTime, `lastModified` timestamp on update CURRENT_TIMESTAMP ) 

Unfortunately, you will have to install the “added” manually insert using the NOW() function.

Answer 3

mySQL has a NOW () function that you can use, see the tutorial in the Tutorials Point to help you put it in place.

Answer 4

You can add a DATETIME column and set it when creating a data row. This will serve as the date the record was added.

Then add the TIMESTAMP column:

Automatic updating of the first TIMESTAMP column in a table occurs under any of the following conditions:

• You explicitly set the column to NULL.

• The column is not explicitly specified in the INSERT or LOAD DATA INFILE statement.

• The column is not explicitly specified in the UPDATE statement and some other column change value. UPDATE, which sets the column to a value that does not cause the TIMESTAMP column to be updated; if you set the column to the current value, MySQL ignores the update for efficiency.

The TIMESTAMP column will take care of your modified record date.