Additional languages
« 11.3.1 The DATETIME, DATE, and TIMESTAMP Types
11.3.2 The TIME Type »
Section Navigation      [Toggle]

11.3.1.1. TIMESTAMP Properties as of MySQL 4.1

Note: In older versions of MySQL (prior to 4.1), the properties of the TIMESTAMP data type differed significantly in many ways from what is described in this section. If you need to convert older TIMESTAMP data to work with MySQL 5.1, be sure to see the MySQL 3.23, 4.0, 4.1 Reference Manual for details.

TIMESTAMP columns are displayed in the same format as DATETIME columns. In other words, the display width is fixed at 19 characters, and the format is YYYY-MM-DD HH:MM:SS.

The MySQL server can be also be run with the MAXDB SQL mode enabled. When the server runs with this mode enabled, TIMESTAMP is identical with DATETIME. That is, if this mode is enabled at the time that a table is created, TIMESTAMP columns are created as DATETIME columns. As a result, such columns use DATETIME display format, have the same range of values, and there is no automatic initialization or updating to the current date and time.

To enable MAXDB mode, set the server SQL mode to MAXDB at startup using the --sql-mode=MAXDB server option or by setting the global sql_mode variable at runtime: 

mysql> SET GLOBAL sql_mode=MAXDB;

A client can cause the server to run in MAXDB mode for its own connection as follows: 

mysql> SET SESSION sql_mode=MAXDB;

Note that the information in the following discussion applies to TIMESTAMP columns only for tables not created with MAXDB mode enabled, because such columns are created as DATETIME columns.

MySQL does not accept timestamp values that include a zero in the day or month column or values that are not a valid date. The sole exception to this rule is the special value '0000-00-00 00:00:00'.

You have considerable flexibility in determining when automatic TIMESTAMP initialization and updating occur and which column should have those behaviors:

  • For one TIMESTAMP column in a table, you can assign the current timestamp as the default value and the auto-update value. It is possible to have the current timestamp be the default value for initializing the column, for the auto-update value, or both. It is not possible to have the current timestamp be the default value for one column and the auto-update value for another column.

  • You can specify which TIMESTAMP column to automatically initialize or update to the current date and time. This need not be the first TIMESTAMP column.

The following rules govern initialization and updating of TIMESTAMP columns:

  • If a DEFAULT value is specified for the first TIMESTAMP column in a table, it is not ignored. The default can be CURRENT_TIMESTAMP or a constant date and time value.

  • DEFAULT NULL is the same as DEFAULT CURRENT_TIMESTAMP for the first TIMESTAMP column. For any other TIMESTAMP column, DEFAULT NULL is treated as DEFAULT 0.

  • Any single TIMESTAMP column in a table can be used as the one that is initialized to the current timestamp or updated automatically.

  • In a CREATE TABLE statement, the first TIMESTAMP column can be declared in any of the following ways:

    • With both DEFAULT CURRENT_TIMESTAMP and ON UPDATE CURRENT_TIMESTAMP clauses, the column has the current timestamp for its default value, and is automatically updated.

    • With neither DEFAULT nor ON UPDATE clauses, it is the same as DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP.

    • With a DEFAULT CURRENT_TIMESTAMP clause and no ON UPDATE clause, the column has the current timestamp for its default value but is not automatically updated.

    • With no DEFAULT clause and with an ON UPDATE CURRENT_TIMESTAMP clause, the column has a default of 0 and is automatically updated.

    • With a constant DEFAULT value, the column has the given default. If the column has an ON UPDATE CURRENT_TIMESTAMP clause, it is automatically updated, otherwise not.

    In other words, you can use the current timestamp for both the initial value and the auto-update value, or either one, or neither. (For example, you can specify ON UPDATE to enable auto-update without also having the column auto-initialized.)

  • CURRENT_TIMESTAMP or any of its synonyms (CURRENT_TIMESTAMP(), NOW(), LOCALTIME, LOCALTIME(), LOCALTIMESTAMP, or LOCALTIMESTAMP()) can be used in the DEFAULT and ON UPDATE clauses. They all mean “the current timestamp.” (UTC_TIMESTAMP is not allowed. Its range of values does not align with those of the TIMESTAMP column anyway unless the current time zone is UTC.)

  • The order of the DEFAULT and ON UPDATE attributes does not matter. If both DEFAULT and ON UPDATE are specified for a TIMESTAMP column, either can precede the other. For example, these statements are equivalent: 

    CREATE TABLE t (ts TIMESTAMP);
CREATE TABLE t (ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP
                             ON UPDATE CURRENT_TIMESTAMP);
CREATE TABLE t (ts TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
                             DEFAULT CURRENT_TIMESTAMP);

  • To specify automatic default or updating for a TIMESTAMP column other than the first one, you must suppress the automatic initialization and update behaviors for the first TIMESTAMP column by explicitly assigning it a constant DEFAULT value (for example, DEFAULT 0 or DEFAULT '2003-01-01 00:00:00'). Then, for the other TIMESTAMP column, the rules are the same as for the first TIMESTAMP column, except that if you omit both of the DEFAULT and ON UPDATE clauses, no automatic initialization or updating occurs.

    Example. These statements are equivalent: 

    CREATE TABLE t (
    ts1 TIMESTAMP DEFAULT 0,
    ts2 TIMESTAMP DEFAULT CURRENT_TIMESTAMP
                  ON UPDATE CURRENT_TIMESTAMP);
CREATE TABLE t (
    ts1 TIMESTAMP DEFAULT 0,
    ts2 TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
                  DEFAULT CURRENT_TIMESTAMP);

You can set the current time zone on a per-connection basis, as described in Section 5.10.8, “MySQL Server Time Zone Support”. TIMESTAMP values are stored in UTC, being converted from the current time zone for storage, and converted back to the current time zone upon retrieval. As long as the time zone setting remains constant, you get back the same value you store. If you store a TIMESTAMP value, and then change the time zone and retrieve the value, the retrieved value is different than the value you stored. This occurs because the same time zone was not used for conversion in both directions. The current time zone is available as the value of the time_zone system variable.

You can include the NULL attribute in the definition of a TIMESTAMP column to allow the column to contain NULL values. For example: 

CREATE TABLE t
(
  ts1 TIMESTAMP NULL DEFAULT NULL,
  ts2 TIMESTAMP NULL DEFAULT 0,
  ts3 TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP
);

If the NULL attribute is not specified, setting the column to NULL sets it to the current timestamp. Note that a TIMESTAMP column which allows NULL values will not take on the current timestamp except under one of the following conditions:

  • Its default value is defined as CURRENT_TIMESTAMP

  • NOW() or CURRENT_TIMESTAMP is inserted into the column

In other words, a TIMESTAMP column defined as NULL will auto-initialize only if it is created using a definition such as the following: 

CREATE TABLE t (ts TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP);

Otherwise — that is, if the TIMESTAMP column is defined to allow NULL values but not using DEFAULT TIMESTAMP, as shown here… 

CREATE TABLE t1 (ts TIMESTAMP NULL DEFAULT NULL);
CREATE TABLE t2 (ts TIMESTAMP NULL DEFAULT '0000-00-00 00:00:00');

…then you must explicitly insert a value corresponding to the current date and time. For example: 

INSERT INTO t1 VALUES (NOW());
INSERT INTO t2 VALUES (CURRENT_TIMESTAMP);

Note that TIMESTAMP columns are NOT NULL by default.


User Comments

Posted by Kjell Arne Rekaa on April 14 2005 11:11pm[Delete] [Edit]

If you want the same view of a timestamp field in 4.1.x as it was in in earlier mysql versions, without the delimiter characters in date and time, simply add a "+0" to the column name:


mysql> create table date (remember timestamp);
Query OK, 0 rows affected (0.03 sec)

mysql> insert date values ('20050517120000');
Query OK, 1 row affected (0.00 sec)

mysql> select remember from date;

+---------------------+
| remember            |
+---------------------+
| 2005-05-17 12:00:00 |
+---------------------+
1 row in set (0.00 sec)

mysql> select remember+0 from date;
+----------------+
| remember+0     |
+----------------+
| 20050517120000 |
+----------------+
1 row in set (0.00 sec)


Posted by [name withheld] on April 29 2005 1:54am[Delete] [Edit]

ALTER TABLE .. CHANGE COLUMN allows only literal values, using CURRENT_TIMESTAMP throws an error. This means the only two ways of setting CURRENT_TIMESTAMP is through CREATE TABLE and CREATE COLUMN.

Problem is, MySQL-Front (and no doubt other MySQL GUI's) handle this situation by *silently* updating your timestamp defaults to ZERO! (instead of throwing an error and rolling back) Fortunately, on our production database we were able to spot this inconsistency before it caused too many problems!

Our solution was to avoid using non-literal DEFAULTs altogether.

Posted by James Harvard on July 6 2005 11:14pm[Delete] [Edit]

What is not clear from the docs is that there can only be one TIMESTAMP column with CURRENT_TIMESTAMP in a DEFAULT or an ON UPDATE clause.

So if, like me, you would like a TIMESTAMP column to record the row creation time and another TIMESTAMP column to be updated on every modification of the row, then it appears this is not possible. (In other words such functionality needs to go in your application logic.)

If your first TIMESTAMP column definition in a table has neither a DEFAULT or ON UPDATE clause then this is the same as TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP.

The following returns an error (MySQL 4.1.12):

CREATE TABLE test (
id INT(10) UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
txt VARCHAR(16) DEFAULT NULL,
ts_modify TIMESTAMP,
ts_create TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

Posted by Klas Karlsson on October 25 2005 11:26am[Delete] [Edit]

Like J H says.
Apparently it is not possible to have the things you really want: one updated and one created column !

The solution seems to be to let the application handle the created column and have an automatically updated date column for updates.

Maybe you can also solve this with a trigger in mysql 5.0.x

Posted by Beat Vontobel on November 7 2005 11:02am[Delete] [Edit]

With MySQL 5.0 it's indeed possible to have more than one auto-TIMESTAMP column in one table using TRIGGERs.

You could for example do something like:

CREATE TRIGGER my_table_insert BEFORE INSERT ON `my_table`
FOR EACH ROW SET NEW.inserted = NOW(), NEW.updated = '0000-00-00 00:00:00';
CREATE TRIGGER my_table_update BEFORE UPDATE ON `my_table`
FOR EACH ROW SET NEW.updated = NOW(), NEW.inserted = OLD.inserted;

on the following table:

CREATE TABLE `my_table` (
`inserted` TIMESTAMP DEFAULT '0000-00-00 00:00:00',
`updated` TIMESTAMP DEFAULT '0000-00-00 00:00:00',
...);

For a more detailed explanation see http://www.futhark.ch/mysql/108.html

Posted by Michael McNally on May 7 2006 9:49pm[Delete] [Edit]

If you have data that requires both a creation date and an updated date...make sure the updated date field comes first in your table architecture. Then, with your application language(php,etc), write a query similar to this:

INSERT INTO tbl_name SET creation_date=CURRENT_TIMESTAMP;

Posted by Tim Hady on May 31 2006 7:35pm[Delete] [Edit]

How I solved the problem of not having a creation and update timestamp.

1. Make all my updates, my time_updated field is updated automatically.

2. UPDATE tablename set time_created = CURRENT_TIMESTAMP where time_created = 0

Only new records have a time_created value of 0, thus they get updated with the current time. This won't be accurate to the second, but will be suitable if your time can be approximate.

Posted by Mikkel Rostock on June 26 2006 10:13pm[Delete] [Edit]

If you execute an UPDATE query that doesn't actually update anything on a given row, the timestamp for that row is not updated automatically - since nothing has been updated I guess.

Took me a short while to figure out...

Posted by [name withheld] on February 26 2007 7:58pm[Delete] [Edit]

regarding comment "Posted by [name withheld] on April 29 2005 1:54am" about using alter table.

I used ALTER TABLE (on version 5.0) to change the first timestamp columns of my tables into "DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP" using:

ALTER TABLE mytable CHANGE colname colname TIMESTAMP

(i had panicked when i read him say "This means the only two ways of setting CURRENT_TIMESTAMP is through CREATE TABLE and CREATE COLUMN." - this is not correct)

Posted by [name withheld] on February 26 2007 7:47pm[Delete] [Edit]

I was hastily moving my data from version 3.22.22 to version 5.0 on a live application. (Not wise, if you can help it!). I discovered that my php code was doing a lot of comparisons of timestamps. These were not working because of the difference in the way they were retreived accross versions. The following function was a quick fix to ensure that a comparison worked.

function convert_SQL_timestamp_to_digitsonly($timestamp) {
if (strlen($timestamp) < 1) {
return 0;
}

//use a regular expression to replace anything thats not a digit with an empty string
$digit_timestamp = eregi_replace('[^[:digit:]]','',$timestamp);

return $digit_timestamp;
}

//example usage
if (convert_SQL_timestamp_to_digitsonly($result_array['col_last_updated']) > 20070214210304) {
//its after the time we introduced some feature...
//do stuff
}

Posted by Leo Vermaak on March 17 2007 12:05pm[Delete] [Edit]

I have found the following solution to work for me:

On table that have more updates then insert I have the following:
Table creation...
`ModifyDate` timestamp NOT NULL default CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

with the following trigger

CREATE TRIGGER articles_insert BEFORE INSERT ON `articles`
FOR EACH ROW SET NEW.CreationDate = NOW();

On a table that have more inserts then updates (a logging table) I change it that the ModifyDate is in the trigger and the CreationDate is as follows:
'CreationDate' timestamp NOT NULL default CURRENT_TIMESTAMP,

Posted by Benjamin Bittman on March 26 2007 11:52am[Delete] [Edit]

Re the ability to have date_created and date_updated -- setup as per below...use NULL when inserting the record for the first time. Tested in 4.1.2 and works as expected :)

From: http://www.mysql.com/certification/mysql-certification-41addendum.pdf

Note that you can choose to use CURRENT_TIMESTAMP with neither, either, or both of the attributes for a single TIMESTAMP column, but you cannot use DEFAULT CURRENT_TIMESTAMP with one column and ON UPDATE CURRENT_TIMESTAMP with another:

mysql> CREATE TABLE ts_test4 (
-> created TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
-> updated TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
-> data CHAR(30)
-> );

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

Nevertheless, you can achieve the effect of having one column with the creation time and another with the time of the last update. To do this, create two TIMESTAMP columns. Define the column that should hold the creation time with DEFAULT 0 and explicitly set it to NULL whenever you INSERT a new record. Define the column that should hold the updated time with DEFAULT CURRENT_TIMESTAMP:

mysql> CREATE TABLE ts_test5 (
-> created TIMESTAMP DEFAULT 0,
-> updated TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
-> data CHAR(30)
-> );

Query OK, 0 rows affected (0.01 sec)

mysql> INSERT INTO ts_test5 (created, data)
-> VALUES (NULL, 'original_value');

Query OK, 1 row affected (0.00 sec)

mysql> SELECT * FROM ts_test5; 

+---------------------+---------------------+----------------+ 
| created             | updated             | data           | 
+---------------------+---------------------+----------------+ 
| 2005-01-04 14:47:39 | 0000-00-00 00:00:00 | original_value | 
+---------------------+---------------------+----------------+
1 row in set (0.00 sec)

mysql> ... time passes ...
mysql> UPDATE ts_test5 SET data='updated_value';

Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0

mysql> SELECT * FROM ts_test5; 
+---------------------+---------------------+---------------+ 
| created             | updated             | data          | 
+---------------------+---------------------+---------------+ 
| 2005-01-04 14:47:39 | 2005-01-04 14:47:52 | updated_value | 
+---------------------+---------------------+---------------+
1 row in set (0.00 sec)

Add your own comment.