MySQL using two timestamps for created/updated values

October 24, 2007

I’ve seen this on and off for years, but never have it handy. So, here’s a quick demo:

mysql> create table sample ( timeUpdated TIMESTAMP, timeCreated TIMESTAMP, val INT );
Query OK, 0 rows affected (0.24 sec)

mysql> insert into sample (timeUpdated, timeCreated, val) values (NULL,NULL,1);
Query OK, 1 row affected (0.00 sec)

mysql> select * from sample;
+———————+———————+——+
| timeUpdated | timeCreated | val |
+———————+———————+——+
| 2007-10-24 15:25:03 | 2007-10-24 15:25:03 | 1 |
+———————+———————+——+
1 row in set (0.00 sec)

mysql> update sample set val=val+1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0

mysql> select * from sample;
+———————+———————+——+
| timeUpdated | timeCreated | val |
+———————+———————+——+
| 2007-10-24 15:25:23 | 2007-10-24 15:25:03 | 2 |
+———————+———————+——+
1 row in set (0.00 sec)

The basic idea is that by forcing NULLs in to the TIMESTAMP columns, they’ll both be triggered to use the current timestamp value. But when you don’t reference them, only the first TIMESTAMP column will be updated, and if you define it as the ‘last updated’ column, you’re good to go.

There are also ways to handle this via triggers, and loads of ways to handle this sort of logic in your app layer. I think the grails GORM layer will automatically add dateCreated and dateUpdated values behind the scenes, so if all your data is controlled by GORM, you’re set to go. If it doesn’t do it now I believe it was talked about being added soon.

Share and Enjoy: These icons link to social bookmarking sites where readers can share and discover new web pages.

Category Posted in Database, Development, MySQL, Software, Web, opensource

2 Responses to “MySQL using two timestamps for created/updated values”

Michael Pelz-Sherman said:

October 24th, 2007 at 2:39 pm

Thanks Mike. There’s a bit too much reliance on MySQL implementation logic (which could change in subsequent versions of MySQL) going on here for my taste to feel comfortable with this approach. I’d rather handle this in the application (Data Access) layer. It does seem unfortunate that you can’t have multiple current_timestamp columns in mysql though (see http://groups.google.com/group/mailing.database.mysql-internals/browse_thread/thread/4d4d005c6d01b815/70f0d1c10dda1cc3)
mgkimsal said:

October 24th, 2007 at 6:09 pm

The stronger way to do this is using triggers, imo. The notion of ‘timestamp’ itself is a bit mysql-specific (every DB has their own date stuff, I guess). Triggers would enforce this at the db layer, rather than at the app layer, which might be useful.

I’ll wonder out loud here why this sort of functionality hasn’t crept up in some database already. Perhaps MSSQL has it? Just automatically add a ‘date created’ and ‘date updated’ column to tables. Have it be an option of table creation that you don’t have to think about. Having it in built would be much faster than the app layer and even, dare I say, slightly faster than the overhead of dealing with userland triggers.

This is the level of non-standardness I’d have expected from MySQL already. Non-standard, but damn useful (think LIMIT).