Database Reference
In-Depth Information
+------+---------------------+---------------------+---------------------+
| val | ts_both | ts_create | ts_update |
+------+---------------------+---------------------+---------------------+
| 6 | 2014-02-20 18:07:01 | 2014-02-20 18:06:45 | 2014-02-20 18:07:01 |
| 12 | 2014-02-20 18:07:01 | 2014-02-20 18:06:50 | 2014-02-20 18:07:01 |
+------+---------------------+---------------------+---------------------+
As stated previously, automatic
TIMESTAMP
properties also apply to
DATETIME
, with some
differences:
• For the first
TIMESTAMP
column in a table, if neither of the
DEFAULT
or
ON
UPDATE
attributes are specified, the column is implicitly defined with both. For
DATETIME
,
automatic properties never apply implicitly; only those specified explicitly. (To
suppress implicit attribute definition for
TIMESTAMP
columns, enable the
explic
it_defaults_for_timestamp
system variable.)
• You can set a
TIMESTAMP
column to the current date and time at any time by setting
it to
NULL
, unless it has specifically been defined to permit
NULL
values. Assigning
NULL
to a
DATETIME
column never sets it to the current date and time.
To prevent a
TIMESTAMP
column from being set to the current date and time when
assigned a
NULL
value, include the
NULL
attribute in the column definition. Then
assigning
NULL
to the column stores
NULL
.
To determine for any given
TIMESTAMP
column what happens when
NULL
is assigned to
it, use
SHOW
CREATE
TABLE
to see the column definition. If the definition includes the
NULL
attribute, assigning
NULL
stores
NULL
. If the definition includes the
NOT
NULL
at‐
tribute, you can specify
NULL
as the value to be assigned, but you cannot
store
NULL
because MySQL stores the current date and time instead.
See Also
To simulate
TIMESTAMP
auto-initialization and auto-update properties for other tem‐
poral types, you can use triggers (see
Recipe 9.6
). This technique applies to
DATE
and
TIME
, and can also be useful for
DATETIME
before MySQL 5.6.5 (when automatic prop‐
erties for that type were introduced).
6.8. Extracting Parts of Dates or Times
Problem
You want to obtain just a part of a date or a time.
