The Ultimate Guide to High Availability with MariaDB

TIMESTAMP

You are viewing an old version of this article. View the current version here.

Syntax:

TIMESTAMP

Description:

The timestamp field is generally used to define at which moment in time a row was added or updated. It can hold values starting at '1970-01-01 00:00:01' (UTC) to '2038-01-09 03:14:07' (UTC) . This range is caused by MariaDB storing the TIMESTAMP values as the number of seconds since '1970-01-01 00:00:00' (UTC). If you do not set a value for the first TIMESTAMP column in a table, MariaDB will automatically assign it the current date and time when performing an UPDATE or INSERT query on the row(s) in question. To set the current time and date of a TIMESTAMP column, you can simply assign it a NULL value. More information on the TIMESTAMP datatype is described in http://dev.mysql.com/doc/refman/5.1/en/timestamp.html.

The value of a TIMESTAMP can be returned in two ways. By default, it is returned as a string in the format of 'YYYY-MM-DD HH:MM:SS'. However, by adding +0 to the timestamp column while selecting it will return the TIMESTAMP value as Epoch Seconds.

Limitations:

  • Because the TIMESTAMP value is stored as Epoch Seconds, the timestamp value '1970-01-01 00:00:00' (UTC) is reserved since the second #0 is used to represent '0000-00-00 00:00:00'.
  • Due to limitations in the FRM file format, there can only be one TIMESTAMP column that has CURRENT_TIMESTAMP defined as its default value.

Note: Prior to MySQL 4.1 a different format for the TIMESTAMP datatype was used. This format is unsupported in MariaDB 5.1 and upwards.

Comments

Comments loading...
Content reproduced on this site is the property of its respective owners, and this content is not reviewed in advance by MariaDB. The views, information and opinions expressed by this content do not necessarily represent those of MariaDB or any other party.