MySQL auto-store datetime for each row

sql mysql datetime audit
mauris picture mauris · Dec 19, 2009 · Viewed 82.9k times · Source

In MySQL, I'm sick of adding the columns dt_created and dt_modified (which are date time stamps for creation and last modified respectively) to all the tables I have in my database.

Every time I INSERT or UPDATE the database, I will have to use the NOW() keyword. This is going all over my persistence.

Is there any efficient alternative where MySQL can automatically store at least the datatime of the row that is inserted and let me retrieve it?

Answer

OMG Ponies picture OMG Ponies · Dec 19, 2009

You can use DEFAULT constraints to set the timestamp:

ALTER TABLE
 MODIFY dt_created datetime DEFAULT CURRENT_TIMESTAMP

ALTER TABLE
 MODIFY dt_modified datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP

Then you wouldn't have to specify NOW() in your INSERT/UPDATE statements.

Reference: TIMESTAMP properties

Related questions

MySQL Query GROUP BY day / month / year

Is it possible to make a simple query to count how many records I have in a determined period of time like a year, month, or day, having a TIMESTAMP field, like: SELECT COUNT(id) FROM stats WHERE record_date.…

Read More
Datetime equal or greater than today in MySQL

What's the best way to do following: SELECT * FROM users WHERE created >= today; Note: created is a datetime field.

Read More
How to get current date & time in MySQL?

Is there a value or command like DATETIME that I can use in a manual query to insert the current date and time? INSERT INTO servers ( server_name, online_status, exchange, disk_space, network_shares ) VALUES( 'm1', 'ONLINE', 'ONLINE', …

Read More