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'
.
TIMESTAMP
values are converted
from the current time zone to UTC for storage, and converted
back from UTC to the current time zone for retrieval. (This
occurs only for the TIMESTAMP
data type, not for other types such as
DATETIME
.) By default, the
current time zone for each connection is the server's time.
The time zone can be set on a per-connection basis, as
described in Section 9.6, “MySQL Server Time Zone Support”. 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 from 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.
The TIMESTAMP
data type offers
automatic initialization and updating. You can choose whether
to use these properties and which column should have them:
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.
Any single TIMESTAMP
column
in a table can be used as the one that is initialized to
the current date and time, or updated automatically. This
need not be the first
TIMESTAMP
column.
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.
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 and is not automatically
initialized to the current timestamp. If the column
also has an ON UPDATE
CURRENT_TIMESTAMP
clause, it is
automatically updated; otherwise, it has a constant
default and is not automatically updated.
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.) The following column
definitions demonstrate each of the possibilities:
Auto-initialization and auto-update:
ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
Auto-initialization only:
ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP
Auto-update only:
ts TIMESTAMP DEFAULT 0 ON UPDATE CURRENT_TIMESTAMP
Neither:
ts TIMESTAMP DEFAULT 0
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:
CREATE TABLE t ( ts1 TIMESTAMP DEFAULT 0, ts2 TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP);
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);
The examples that use DEFAULT 0
will not
work if the NO_ZERO_DATE
SQL mode is enabled because that mode causes
“zero” date values (specified as
0
, '0000-00-00
, or
'0000-00-00 00:00:00'
) to be rejected. Be
aware that the TRADITIONAL
SQL mode includes
NO_ZERO_DATE
.
TIMESTAMP
columns are
NOT NULL
by default, cannot contain
NULL
values, and assigning
NULL
assigns the current timestamp.
However, a TIMESTAMP
column can
be allowed to contain NULL
by declaring it
with the NULL
attribute. In this case, the
default value also becomes NULL
unless
overridden with a DEFAULT
clause that
specifies a different default value. DEFAULT
NULL
can be used to explicitly specify
NULL
as the default value. (For a
TIMESTAMP
column not declared
with the NULL
attribute, DEFAULT
NULL
is illegal.) If a
TIMESTAMP
column allows
NULL
values, assigning
NULL
sets it to NULL
,
not to the current timestamp.
The following table contains several
TIMESTAMP
columns that allow
NULL
values:
CREATE TABLE t ( ts1 TIMESTAMP NULL DEFAULT NULL, ts2 TIMESTAMP NULL DEFAULT 0, ts3 TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP );
Note that a TIMESTAMP
column
that 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 CURRENT_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);
The MySQL server can 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;
User Comments
The only way to have more than one TIMESTAMP column, when one is declared with either DEFAULT CURRENT_TIMESTAMP or ON UPDATE CURRENT_TIMESTAMP or both, is to force the other timestamp column default to a valid timestamp value, such as 20070101000000.
I believe this is due to a TIMESTAMP column without a declared default uses the CURRENT_TIMESTAMP and thus throws this error
#1293 - Incorrect table definition; there can be only one TIMESTAMP column with CURRENT_TIMESTAMP in DEFAULT or ON UPDATE clause.
even though it appears you are only trying to use it on one column.
nope, all you have to do is to declare TIMESTAMP column with DEFAULT CURRENT_TIMESTAMP before any other TIMESTAMP columns.
short explanation from someone who knows why is this so, would be nice.
`user_created_date` timestamp default CURRENT_TIMESTAMP COMMENT 'creation timestamp',
`user_updated_date` timestamp default '20070101000000' COMMENT 'edit timestamp',
This will work. If you try to set the second timestamp field to ON UPDATE CURRENT_TIMESTAMP it will then report
#1293 - Incorrect table definition; there can be only one TIMESTAMP column with CURRENT_TIMESTAMP in DEFAULT or ON UPDATE clause
It seems to me the function of having both a creation timestamp set once by the default and an edit timestamp set repeatedly on update is NOT SUPPORTED by MySQL.
You do not have to default it to some constant, you can also default it to NULL, like so...
`creationtime` timestamp NULL default NULL,
`lastupdate` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
An explanation as to why they cannot have one column default to CURRENT_TIMESTAMP on creation and another column be the CURRENT_TIMESTAMP on update would be nice. I find myself in creation of many tables where I need this functionality on two separate columns.
"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.
If the value(s) you are trying to update are identical, MySQL will not auto-update the timestamp alone. You will have to use NOW() instead.
These last two posts are the only mechanism that worked for us.
In any system, hand coded, phpmyadmin, mysql query browser or otherwise :
`created` timestamp NOT NULL default '0000-00-00 00:00:00',
`modified` timestamp NOT NULL default CURRENT_TIMESTAMP on update NOW(),
although, phpmyadmin exports this out without the NOW, replacing it with CURRENT_TIMESTAMP instead.
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.
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
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/
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.
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;
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`).
Add your own comment.