CODEWITHSUNDEEP
mysqlsqltimestampdatabase-triggermysql-error-1293
Matt Fenwick
Matt Fenwick

Reputation: 49085

Timestamp for row creation and last modification

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!

Upvotes: 7

Views: 11077

Answers (2)

Mike Purcell
Mike Purcell

Reputation: 19979

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()";

Upvotes: 5

Paulo H.
Paulo H.

Reputation: 1258

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.

Upvotes: 2

Related Questions