(mysql.info) timestamp-4-1
Info Catalog
(mysql.info) datetime
(mysql.info) datetime
11.3.1.1 `TIMESTAMP' Properties as of MySQL 4.1
...............................................
* 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.0, 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.
As of MySQL 5.0.2, 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.)
* Any of `CURRENT_TIMESTAMP', `CURRENT_TIMESTAMP()', or `NOW()' can
be used in the `DEFAULT' and `ON UPDATE' clauses. They all mean
`the current timestamp.'
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 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-update
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);
Info Catalog
(mysql.info) datetime
(mysql.info) datetime
automatically generated byinfo2html