Timestamp for row creation and last modification

Question

I need to keep track of the time a row was inserted into the database, and the time it was last modified.

I tried to create two separate columns, and use CURRENT_TIMESTAMP:

create table def (
  id int, 
  creation timestamp 
    default CURRENT_TIMESTAMP, 
  modification timestamp 
    on update CURRENT_TIMESTAMP
);

However, this produced an error:

ERROR 1293 (HY000): Incorrect table definition; there can be only one TIMESTAMP column with CURRENT_TIMESTAMP in DEFAULT or ON UPDATE clause

What is the best way to do this?

I'm thinking stored procedure, but looking for a standard solution. I'm also concerned with access privileges -- as few programs/things should be able to touch the timestamps as possible.

---

Although I would prefer MySQL answers, solutions for other RDBMS's are also appreciated!

Solution 1

Ya this is a lame limitation on MySQL. If you are going through an application you can add a time() call for the created_at column, and let the updated_at column use the CURRENT_TIMESTAMP.

$sql = "INSERT INTO my_table SET name = 'Mike', created_at = " . time();

I'd opt to do this on the created_at column as it probably won't be touched as often as the updated_at column.

-- Edit --

Better yet, use MySQL's built in now() function. This way you only need to be concerned with the timezone of the mysql server, and not the timezones of the app server AND the mysql server.

$sql = "INSERT INTO my_table SET name = 'Mike', created_at = NOW()";

Solution 2

You can use a trigger. The application can also set the value, but if do, it will be overwritten by the database.

delimiter //
CREATE TRIGGER def_bef_update BEFORE UPDATE ON def FOR EACH ROW BEGIN
    SET NEW.modification = CURRENT_TIMESTAMP;
END//
delimiter ;

You can also use it to check the data and update your modification date only if has important changes.

Comments

[Mike Purcell]

@kevin: You are correct, and tbh, NOW() is the preferred alternative, that way you are only dealing with the mysql server's timezone, instead of the server running the app code AND the mysql server.