Skip navigation links

User Comments

Posted by mari a on March 12 2008 10:36pm[Delete] [Edit]

"TIMESTAMP columns are NOT NULL by default, cannot contain NULL values, and assigning NULL assigns the current timestamp."

What I do, is declare my created_at column as NOT NULL, then assign the column as NULL when I do the insert.. which populates it with the current timestamp.

Then on updates, the second timestamp column with the ON UPDATE...etc updates on it's own. However, the update code must not alter the created_at column at all (obviously).

created_at timestamp NOT NULL default '0000-00-00 00:00:00'
updated_at timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP

INSERT INTO table(created_at,updated_at) VALUES (NULL,NULL);

populates BOTH columns with same timestamp for creation.

Posted by Chris Heath on July 29 2008 3:32am[Delete] [Edit]

Note that using CURRENT_TIMESTAMP explicitly in an INSERT or UPDATE does not always insert the current timestamp. The only correct way is to insert NULL or to omit the timestamp column entirely.

With an explicit CURRENT_TIMESTAMP, MySQL converts the current timestamp to YYYY-MM-DD HH-MM-SS format and then back to a timestamp, which is a lossy conversion if you are in a timezone with daylight savings.

Example to demonstrate: (assumes US Eastern Time)
\! date 110400592007.59
Sun Nov 4 00:59:59 EDT 2007
\! sleep 2
CREATE TABLE test (id int, t timestamp);
INSERT INTO test VALUES (1, NULL), (2, CURRENT_TIMESTAMP);
SELECT test.*, UNIX_TIMESTAMP(t) FROM test;
+------+---------------------+-------------------+
| id | t | UNIX_TIMESTAMP(t) |
+------+---------------------+-------------------+
| 1 | 2007-11-04 01:00:01 | 1194152401 |
| 2 | 2007-11-04 01:00:01 | 1194156001 |
+------+---------------------+-------------------+

The second row is wrong: the unix timestamp is 1 hour (3600 seconds) later, a result of a lossy round-trip conversion.

Posted by michael splendid on August 27 2008 5:40pm[Delete] [Edit]

using ALTER to add a TIMESTAMP column with the DEFAULT CURRENT_TIMESTAMP attribute, will not add the current TIMSTAMP to that column (as expected)
Mike Trader

Posted by Bogdan Gusiev on May 11 2009 4:42pm[Delete] [Edit]

Here is a solution how to make both update and create timestamps in mysql in one table:
http://gusiev.com/2009/04/update-and-create-timestamps-with-mysql/

Posted by Stefan Zielinski on May 11 2009 11:07pm[Delete] [Edit]

The example in Bogdan Gusiev's link is essentially the same as from the 5th post above ("Posted by mari a on March 12 2008 10:36pm"), and while it does work, it can also be achieved by declaring the following table:

CREATE TABLE test.table (
`row_inserted` TIMESTAMP NULL,
`row_updated` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
)

...and then specifying CURRENT_TIMESTAMP explicitly in the insert statement:

INSERT INTO test.table (row_inserted) VALUES (CURRENT_TIMESTAMP)

...all of these solutions require that the code doing the insert explicitly set a value for the created / inserted timestamp column - whether it is NULL or CURRENT_TIMESTAMP - there doesn't seem to be a way for MySQL to handle this implicitly.

Ideally, these columns would never need to be explicitly referenced by any insert or update query, and instead be set automatically, with a declaration like this:

CREATE TABLE test.table (
`row_inserted` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
`row_updated` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
)

...and I'm not sure why MySQL chose not to support this - it doesn't seem like there should be any real technical limitation that prevents it.

Posted by Rob Desbois on July 29 2009 8:24am[Delete] [Edit]

We recently modified a DATETIME column to be of type TIMESTAMP as we wanted to take advantage of the timezone-related functionality, but found there's no way to create a TIMESTAMP column with no default and no 'on update' value.

The 2-step process to achieve this is as follows:
CREATE TABLE foo (ts TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP);
ALTER TABLE foo ALTER ts DROP DEFAULT;

Posted by Joseph Piché on August 4 2009 2:15pm[Delete] [Edit]

It may help someone that depending on table structure and the amount of data in the table, if you need to ORDER BY a TIMESTAMP column, it may speed up the query by using ORDER BY UNIX_TIMESTAMP(`field`).

Posted by Nasantsogt Baasanjav on May 9 2010 1:06pm[Delete] [Edit]

I've managed the issue of having tables with create/update timestamps with the help of triggers defined on DATETIME columns (instead of TIMESTAMP):

CREATE TABLE table (
id INT(11) NOT NULL auto_increment,
value VARCHAR(32) DEFAULT NULL,
created_at DATETIME DEFAULT NULL,
updated_at DATETIME DEFAULT NULL
)

CREATE TRIGGER table_created_at
BEFORE INSERT ON table
FOR EACH ROW
SET NEW.created_at = UTC_TIMESTAMP, NEW.updated_at = UTC_TIMESTAMP

CREATE_TRIGGER table_updated_at
BEFORE UPDATE ON table
FOR EACH ROW
SET NEW.updated_at = UTC_TIMESTAMP, NEW.created_at = OLD.created_at

This way i'm not forced to set explicitly NULL on those fields, i just omit them entirely. Even more, any explicit value specified on those fields in insert/update SQL statements is simply ignored (it is exactly the purpose of the "NEW.created_at = OLD.created_at" segment in the second trigger: to avoid being overwritten).

Secondly, i could choose any function, not just NOW or CURRENT_TIMESTAMP, for example UTC_TIMESTAMP or anything else. In the particular case of a project i worked on, i prefered to deal directly with UTC times and let timezone issues be managed on geographically distributed (RIA) clients..

Posted by Shelby Moore on August 18 2010 3:38am[Delete] [Edit]

Here is how I achieve created and updated timestamps:

ChangeDate timestamp(14) NOT NULL,
CreationDate timestamp(14) NOT NULL,

where ChangeDate is the very first column in the table (maybe it only needs to be the first timestamp in the table).

Then on record creation, I set both timestamps to NOW(). On record update, I do not assign any value to the timestamps. The ChangeDate is automatically updated.

If I want to update the record and not change the ChangeDate automatically, I set it to itself as follows:

UPDATE user SET ChangeDate = ChangeDate, ...

Posted by Shelby Moore on August 18 2010 3:43am[Delete] [Edit]

I believe the reason other commenters above were having problems is because they were declaring the automatically updated timestamp, to be the 2nd timestamp in the table. The automatically updated timestamp must be the 1st timestamp (maybe column?) in the table.

Posted by Sam Chet on February 16 2012 11:16pm[Delete] [Edit]

"using ALTER to add a TIMESTAMP column with the DEFAULT CURRENT_TIMESTAMP attribute, will not add the current TIMSTAMP to that column (as expected)
Mike Trader"

Check the bug report and discussion on
http://bugs.mysql.com/bug.php?id=17392

Posted by M. Hamed A. on September 28 2011 10:59am[Delete] [Edit]

It seems the suggestion of Nasantsogt Baasanjav is the most perfect solution.

The only drawback for this solution is there is no examination for record change. this means even if the value of other columns of a record is not changed at all, the updated_at field is updated.

I suggest to make an automatic check in the update trigger to improve this workaround.

Posted by Jeffrey Kurtock on February 4 2012 11:35pm[Delete] [Edit]

Use of current_timestamp is a trap, and should always be avoided.

Whenever you might think you want current_timestamp, you can always use:
INSERT INTO tablename SET [..., ...], record_added=now()
or
UPDATE tablename SET [..., ...], record_updated=now()

This is self-documenting; you don't have to guess how the datetime fields are set.

The other suggestions made all rely on undocumented side-effects of MySQL (assigning null to a field?!), or complex add-ons like Triggers. Remember: if it isn't documented, you can't count on it happening the same way on the next release.

But for my money, the biggest reason to avoid current_timestamp is how it runs without any control by you, the developer. Someday you're going to get a call at 3am, and you'll quickly realize you can fix the problem by changing all the "Pmt-Type" (or whatever) fields to lower-case. This would be a simple fix using something like phpMyAdmin, but if you do it your "current_timestamp" fields will all update and you can't stop it. (Well, you can avoid it, if you remember in advance, but it is a real pain.)

Production code doesn't rely on people entering SQL "by hand," so you have no need to force yourself to update a record_updated field. Just use "=now()" in your code instead and you will be safe.