top of page
CerebroSQL

MySQL: 

TIMESTAMP

TIMESTAMP[(fsp)]

A timestamp. The range is '1970-01-01 00:00:01.000000' UTC to
'2038-01-19 03:14:07.999999' UTC. TIMESTAMP values are stored as the
number of seconds since the epoch ('1970-01-01 00:00:00' UTC). A
TIMESTAMP cannot represent the value '1970-01-01 00:00:00' because that
is equivalent to 0 seconds from the epoch and the value 0 is reserved
for representing '0000-00-00 00:00:00', the "zero" TIMESTAMP value.

An optional fsp value in the range from 0 to 6 may be given to specify
fractional seconds precision. A value of 0 signifies that there is no
fractional part. If omitted, the default precision is 0.

The way the server handles TIMESTAMP definitions depends on the value
of the explicit_defaults_for_timestamp system variable.

If explicit_defaults_for_timestamp is enabled, there is no automatic
assignment of the DEFAULT CURRENT_TIMESTAMP or ON UPDATE
CURRENT_TIMESTAMP attributes to any TIMESTAMP column. They must be
included explicitly in the column definition. Also, any TIMESTAMP not
explicitly declared as NOT NULL permits NULL values.

If explicit_defaults_for_timestamp is disabled, the server handles
TIMESTAMP as follows:

Unless specified otherwise, the first TIMESTAMP column in a table is
defined to be automatically set to the date and time of the most recent
modification if not explicitly assigned a value. This makes TIMESTAMP
useful for recording the timestamp of an INSERT or UPDATE operation.
You can also set any TIMESTAMP column to the current date and time by
assigning it a NULL value, unless it has been defined with the NULL
attribute to permit NULL values.

Automatic initialization and updating to the current date and time can
be specified using DEFAULT CURRENT_TIMESTAMP and ON UPDATE
CURRENT_TIMESTAMP column definition clauses. By default, the first
TIMESTAMP column has these properties, as previously noted. However,
any TIMESTAMP column in a table can be defined to have these
properties.

Example

bottom of page