mysql insert timestamp and update timestamp

Question

When we create MySQL table, we usually add column insert_timestamp and update_timestamp to save insert time and update time, we can set update_timestamp DEFAULT CURRENT_TIMESTAMP, make update_timestamp update timestamp as soon as update a row, but how to set insert_time with the time when it inserted?

Answer 1

You can do that passing it throught your Insert Query like this:

"INSERT INTO table_name (column1, column2, column3, insert_timestamp)
VALUES (value1, value2, value3, insertTime)"

Where insertTime will be replaced by your local time. The other option is to create a before_insert trigger for your table, for example:

delimiter //
CREATE TRIGGER bi_tableName BEFORE INSERT ON tableName
FOR EACH ROW
BEGIN
    SET NEW.insert_timestamp = NOW();
END;//

See: https://dev.mysql.com/doc/refman/5.0/en/trigger-syntax.html, http://dev.mysql.com/doc/refman/5.6/en/insert.html

Answer 2

You can either set it "by hand" with your insert statement, or let the database do it with triggers.