15

I am creating some tables where I want to store the time when a record was created and when it was last updated. I thought I could have two timestamp fields where one would have the value CURRENT_TIMESTAMP and the other would have CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP. But I guess I can't do this because you can have only 1 timestamp field with a default value in a table?

How would you recommend I get and store the two times? Thanks!

Aishwar
  • 9,284
  • 10
  • 59
  • 80

6 Answers6

31

A good way to create fields like 'created' and 'updated' is

CREATE TABLE `mytable` ( 
`id` INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY, 
`created` TIMESTAMP DEFAULT '0000-00-00 00:00:00', 
`updated` TIMESTAMP DEFAULT now() ON UPDATE now(),
`myfield` VARCHAR(255)
);

And its necessary to enter nulls into both columns during "insert":

INSERT INTO mytable (created,updated,myfield) VALUES (null,null,'blablabla');

And now, in all updates, the 'updated' field will have a new value with actual date.

UPDATE mytable SET myfield='blablablablu' WHERE myfield='blablabla';

Source : http://gusiev.com/2009/04/update-and-create-timestamps-with-mysql

derloopkat
  • 6,232
  • 16
  • 38
  • 45
albert
  • 1,766
  • 1
  • 21
  • 24
  • 2
    This is the best solution I've found. The magic is that NULL is translated to NOW() on the insert. In a typical application, there will be more ways to update a row than to create it, so this feels like less maintenance than using up your "one now" on create and manually handling updates, using a separate trigger, or other approach. – botimer May 29 '15 at 21:49
  • 2
    Worked wonderfully! Although in my situation I did not seem to need to pass in the null values and just the updated column changed. Seems to work well. `created_on TIMESTAMP DEFAULT CURRENT_TIMESTAMP, updated_on TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,` ... – Jordan Nov 07 '17 at 01:29
  • 1
    this is best solution among all answers here!! Thanks a lot mate – Ashish Bainade Oct 19 '18 at 10:02
27

As of MYSQL version 5.6.5 you can do this using DEFAULT and ON UPDATE. No triggers are needed.

ts_create TIMESTAMP DEFAULT CURRENT_TIMESTAMP,

ts_update TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP

Dimitry
  • 4,503
  • 6
  • 26
  • 40
  • 2
    Right now this is the best answer! – v010dya Sep 24 '17 at 07:34
  • Note that `TIMESTAMP` has an upper limit of `2038-01-19 03:14:07`. In my experience 18 years pass rather quickly. The problem with `DATETIME` on the other hand is that with `UPDATE x='y', ts_update=NULL` the update column is *not* converted to the current timestamp but set to `NULL` whereas a `TIMESTAMP` column is still set to the current timestamp (At least that's what I found with MySQL 5.7). So decide for yourself which of the two is more acceptable for your use case. – Michael Härtl Mar 05 '20 at 08:19
  • Alternatively, you can use the synonym `NOW()` instead of `CURRENT_TIMESTAMP` for brevity. `ts_create TIMESTAMP DEFAULT NOW(), ts_update TIMESTAMP DEFAULT NOW() ON UPDATE NOW()` – Peter Tarlos Apr 05 '20 at 12:25
14

You can have two columns of type timestamp in one table.

The following works for MySQL 5.0

create table t 
(
  id integer, 
  created_at timestamp default current_timestamp, 
  updated_at timestamp
);

I think you are confusing this with SQL Server (where timestamp is not really a "time stamp" and there is indeed a limit on a single "timestamp" column)

Edit: But you will need a trigger to update the update_at column each time the row is changed.

  • That is correct, SQL Server's timestamp is really just a synonym for rowversion – SQLMenace Aug 15 '10 at 18:12
  • This would execute, but the updated_at field doesn't get autoupdated on change. I thought adding the ON UPDATE CURRENT_TIMESTAMP on the updated_at column would take care of that. That is when it complains that this cannot be done. – Aishwar Aug 15 '10 at 18:14
  • 1
    Yes, as I said: you need to use a trigger to populate the updated_at column –  Aug 15 '10 at 18:51
2

As far as I know, there's no workaround for that restriction. You'll need to manually set (at least) one of the timestamps, the easiest way is just add updated = NOW() to the UPDATE-query.

Guido Hendriks
  • 5,706
  • 3
  • 27
  • 37
  • +1 That does seem like a workable solution. I would have really loved it, if the server could do it automatically :) – Aishwar Aug 15 '10 at 18:17
  • Could be possible with triggers though, but I think that's not a great solution. – Guido Hendriks Aug 15 '10 at 19:59
  • Usually it's done vice-versa: you manually specify `created=NOW()` and `updated` is set automatically. The reason is that you create a record 1 time but updates can be unlimited. – Stalinko Feb 23 '18 at 07:44
0

You'll need two columns: CREATE_TIME and UPDATE_TIME.

You might want to add CREATE_USER and UPDATE_USER.

Perhaps you'd want to have a 1:many relationship with name of column changed, old and new values.

It's all part of change data capture. You could have CDC tables that are updated using triggers.

duffymo
  • 305,152
  • 44
  • 369
  • 561
  • I do have 2 columns to store create time and update time - but I can't set the two default values I indicated above. I will look into what is CDC. Shouldn't there be a simple solution to this? I'd image this is a common scenario. – Aishwar Aug 15 '10 at 18:16
  • It is common, but I don't believe that MySQL has a built-in solution for it. Oracle and SQL Server do. Shouldn't be hard to do: a few tables and triggers. – duffymo Aug 15 '10 at 18:18
0

I would leave the current timestamp the way you suggested and fill in the created_at field with current date on insert.

silvo
  • 4,011
  • 22
  • 26