(mysql.info.gz) TIMESTAMP 4
Info Catalog
(mysql.info.gz) TIMESTAMP pre-4
(mysql.info.gz) DATETIME
11.3.1.2 `TIMESTAMP' Properties as of MySQL 4.1
...............................................
In MySQL 4.1 and up, the properties of the `TIMESTAMP' column type
change in the ways described in this section.
From MySQL 4.1.0 on, `TIMESTAMP' display format differs from that of
earlier MySQL releases:
* `TIMESTAMP' columns are displayed in the same format as `DATETIME'
columns.
* Display widths (used as described in the preceding section) are no
longer supported. In other words, for declarations such as
`TIMESTAMP(2)', `TIMESTAMP(4)', and so on, the display width is
ignored.
Beginning with MySQL 4.1.1, the MySQL server can be run in `MAXDB'
mode. When the server runs in this mode, `TIMESTAMP' is identical with
`DATETIME'. That is, if the server is running in `MAXDB' mode 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;
As of MySQL 5.0.2, MySQL will not accept timestamp values that includes
a zero in the day or month column or values that are not a valid date.
(The exception is the special value '0000-00-00 00:00:00'.)
Beginning with MySQL 4.1.2, you have more flexible control over when
automatic `TIMESTAMP' initialization and updating occur and which
column should have those behaviors:
* You can assign the current timestamp as the default value and the
auto-update value, as before. But it is possible to have just one
automatic behavior or the other, or neither of them.
* You can specify which `TIMESTAMP' column to automatically
initialize or update to the current date and time. This no longer
need be the first `TIMESTAMP' column.
The following discussion describes the revised syntax and behavior.
Note that this information applies only to `TIMESTAMP' columns for
tables not created with `MAXDB' mode enabled. As noted earlier in this
section, `MAXDB' mode causes columns to be created as `DATETIME'
columns.
The following items summarize the pre-4.1.2 properties for `TIMESTAMP'
initialization and updating:
The first `TIMESTAMP' column in table row automatically is set to the
current timestamp when the record is created if the column is set to
`NULL' or is not specified at all.
The first `TIMESTAMP' column in table row automatically is updated to
the current timestamp when the value of any other column in the row is
changed, unless the `TIMESTAMP' column explicitly is assigned a value
other than `NULL'.
If a `DEFAULT' value is specified for the first `TIMESTAMP' column when
the table is created, it is silently ignored.
Other `TIMESTAMP' columns in the table can be set to the current
`TIMESTAMP' by assigning `NULL' to them, but they do not update
automatically.
As of 4.1.2, you have more flexibility in deciding which `TIMESTAMP'
column automatically is initialized and updated to the current
timestamp. The rules are as follows:
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 set to be the one that
is initialized to the current timestamp and/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 and with `ON UPDATE
CURRENT_TIMESTAMP' clause, the column has the given default and is
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 get auto-update without also
having the column auto-initialized.)
Any of `CURRENT_TIMESTAMP', `CURRENT_TIMESTAMP()', or `NOW()' can be
used in the `DEFAULT' and `ON UPDATE' clauses. They all have the same
effect.
The order of the two attributes does not matter. If both `DEFAULT' and
`ON UPDATE' are specified for a `TIMESTAMP' column, either can precede
the other.
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 you cannot omit both of the `DEFAULT' and `ON UPDATE'
clauses. If you do that, 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);
Beginning with MySQL 4.1.3, you can set the current time zone on a
per-connection basis, as described in `Time zone support'. `TIMESTAMP'
values still are stored in UTC, but are converted from the current time
zone for storage, and converted back to the current time zone for
retrieval. As long as the time zone setting remains the same, you will
get back the same value you store. If you store a `TIMESTAMP' value,
then change the time zone and retrieve the value, it will be different
than the value you stored. This occurs because the same time zone is
not used for conversion in both directions. The current time zone is
available as the value of the `time_zone' system variable.
Beginning with MySQL 4.1.6, 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
);
Before MySQL 4.1.6 (and even as of 4.1.6 if the `NULL' attribute is not
specified), setting the column to `NULL' sets it to the current
timestamp.
Info Catalog
(mysql.info.gz) TIMESTAMP pre-4
(mysql.info.gz) DATETIME
automatically generated byinfo2html