MySQL Database learning



Home / Database Structure / Data Types / What is TIMESTAMP Data Type in MySQL?
Share this job:


What is TIMESTAMP Data Type in MySQL?


In MySQL 5 Certification Study Guide, the TIMESTAMP data type is described in detailed. Some useful text is as below:

The TIMESTAMP type, like DATETIME, stores date-and-time values, but has a different range and some special properties that make it especially suitable for tracking data modification times.

MySQL displays TIMESTAMP values using the same format as DATETIME values; that is, 'YYYY-MM-DD hh:mm:ss'. The range of TIMESTAMP values begins at 1970-01-01 00:00:00 (UTC) and extends partway into the year 2037. TIMESTAMP values actually represent the number of seconds elapsed since the beginning of 1970 and are stored using four bytes. This provides room for sufficient seconds to represent a date in the year 2037. MySQL Server stores TIMESTAMP values internally in UTC. It converts TIMESTAMP values from the server's current time zone for storage, and converts back to the current time zone for retrieval.

The TIMESTAMP data type in MySQL is special in that you can cause a TIMESTAMP column to be initialized or updated automatically to the current date and time without explicitly assigning it a value. That is, you can specify that any single TIMESTAMP column in a table should be initialized with the current timestamp when the record is created with INSERT or REPLACE, updated with the current timestamp when the record is changed with UPDATE, or both. (Setting a column to its current value doesn't count as updating it.)

It's important to know about the automatic initialization and update properties of TIMESTAMP. These properties make TIMESTAMP columns useful for tracking record modification times, but can be a source of confusion if you're not aware of them. Do not choose TIMESTAMP for a column on the basis of the fact that it stores date-and-time values unless you also understand the circumstances under which the column will update automatically when other columns in a record change.

To control the initialization and update behavior of a TIMESTAMP column, you add either or both of the DEFAULT CURRENT_TIMESTAMP and ON UPDATE CURRENT_TIMESTAMP attributes to the column definition when creating the table with CREATE TABLE or changing it with ALTER TABLE.

The DEFAULT CURRENT_TIMESTAMP attribute causes the column to be initialized with the current timestamp at the time the record is created. The ON UPDATE CURRENT_TIMESTAMP attribute causes the column to be updated with the current timestamp when the value of another column in the record is changed from its current value.

For backward compatibility with older versions of MySQL (before 4.1), if you do not specify either of the DEFAULT CURRENT_TIMESTAMP or ON UPDATE CURRENT_TIMESTAMP attributes when creating a table, the MySQL server automatically assigns both attributes to the first TIMESTAMP column:

 mysql> CREATE TABLE ts_test1 (
-> ts1 TIMESTAMP,
-> ts2 TIMESTAMP,
-> data CHAR(30)
-> );

mysql> DESCRIBE ts_test1;
+-------+-----------+------+-----+---------------------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-----------+------+-----+---------------------+-------+
| ts1 | timestamp | YES | | CURRENT_TIMESTAMP | |
| ts2 | timestamp | YES | | 0000-00-00 00:00:00 | |
| data | char(30) | YES | | NULL | |
+-------+-----------+------+-----+---------------------+-------+

mysql> INSERT INTO ts_test1 (data) VALUES ('original_value');

mysql> SELECT * FROM ts_test1;
+---------------------+---------------------+----------------+
| ts1 | ts2 | data |
+---------------------+---------------------+----------------+
| 2005-01-04 14:45:51 | 0000-00-00 00:00:00 | original_value |
+---------------------+---------------------+----------------+

mysql> . . . time passes . . .

mysql> UPDATE ts_test1 SET data='updated_value';

mysql> SELECT * FROM ts_test1;
+---------------------+---------------------+---------------+
| ts1 | ts2 | data |
+---------------------+---------------------+---------------+
| 2005-01-04 14:46:17 | 0000-00-00 00:00:00 | updated_value |
+---------------------+---------------------+---------------+
The same behavior occurs if you specify both DEFAULT CURRENT_TIMESTAMP and ON UPDATE CURRENT_TIMESTAMP explicitly for the first TIMESTAMP column. It is also possible to use just one of the attributes. The following example uses DEFAULT CURRENT_TIMESTAMP, but omits ON UPDATE CURRENT_TIMESTAMP. The result is that the column is initialized automatically, but not updated when the record is updated:
mysql> CREATE TABLE ts_test2 ( 
-> created_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
-> data CHAR(30) -> );

mysql> INSERT INTO ts_test2 (data) VALUES ('original_value');

mysql> SELECT * FROM ts_test2;
+---------------------+----------------+
| created_time | data |
+---------------------+----------------+
| 2005-01-04 14:46:39 | original_value |
+---------------------+----------------+

mysql> . . . time passes . . .
mysql> UPDATE ts_test2 SET data='updated_value';

mysql> SELECT * FROM ts_test2;
+---------------------+---------------+
| created_time | data |
+---------------------+---------------+
| 2005-01-04 14:46:39 | updated_value |
+---------------------+---------------+

Note that even though the record is updated, the created_time column is not. In versions of MySQL Server before 4.1, the UPDATE statement would have caused the created_time column to be updated as well.

The next example demonstrates how to create a TIMESTAMP column that is not set to the current timestamp when the record is created, but only when it is updated. In this case, the column definition includes ON UPDATE CURRENT_TIMESTAMP but omits DEFAULT CURRENT_TIMESTAMP:

 mysql> CREATE TABLE ts_test3 ( 
-> updated_time TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
-> data CHAR(30) -> );

mysql> INSERT INTO ts_test3 (data) VALUES ('original_value');

mysql> SELECT * FROM ts_test3;
+---------------------+----------------+
| updated_time | data |
+---------------------+----------------+
| 0000-00-00 00:00:00 | original_value |
+---------------------+----------------+

mysql> UPDATE ts_test3 SET data='updated_value';

mysql> SELECT * FROM ts_test3;
+---------------------+---------------+
| updated_time | data |
+---------------------+---------------+
| 2005-01-04 14:47:10 | updated_value |
+---------------------+---------------+

Note that you can choose to use CURRENT_TIMESTAMP with neither, either, or both of the attributes for a single TIMESTAMP column, but you cannot use DEFAULT CURRENT_TIMESTAMP with one column and ON UPDATE CURRENT_TIMESTAMP with another:

mysql> CREATE TABLE ts_test4 ( 
-> created TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
-> updated TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
-> data CHAR(30) -> );

ERROR 1293 (HY000): Incorrect table definition;
there can be only one TIMESTAMP column with CURRENT_TIMESTAMP in DEFAULT or ON UPDATE clause

Nevertheless, you can achieve the effect of having one column with the creation time and another with the time of the last update. To do this, create two TIMESTAMP columns. Define the column that should hold the creation time with DEFAULT 0 and explicitly set it to NULL whenever you INSERT a new record. Define the column that should hold the updated time with DEFAULT CURRENT_TIMESTAMP:

mysql> CREATE TABLE ts_test5 ( 
-> created TIMESTAMP DEFAULT 0,
-> updated TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
-> data CHAR(30) -> ); mysql> INSERT INTO ts_test5 (created, data)
-> VALUES (NULL, 'original_value');

mysql> SELECT * FROM ts_test5;
+---------------------+---------------------+----------------+
| created | updated | data |
+---------------------+---------------------+----------------+
| 2005-01-04 14:47:39 | 0000-00-00 00:00:00 | original_value |
+---------------------+---------------------+----------------+


mysql> UPDATE ts_test5 SET data='updated_value';

mysql> SELECT * FROM ts_test5;
+---------------------+---------------------+---------------+
| created | updated | data |
+---------------------+---------------------+---------------+
| 2005-01-04 14:47:39 | 2005-01-04 14:47:52 | updated_value |
+---------------------+---------------------+---------------+

By default, MySQL defines TIMESTAMP columns as NOT NULL and stores the current timestamp in the column if you assign it a value of NULL. If you want to be able to store NULL in a TIMESTAMP column, you must explicitly write the column definition to allow NULL when creating or altering the column:

mysql> CREATE TABLE ts_null (ts TIMESTAMP NULL); 

mysql> DESCRIBE ts_null;
+-------+-----------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-----------+------+-----+---------+-------+
| ts | timestamp | YES | | NULL | |
+-------+-----------+------+-----+---------+-------+

Note that specifying NULL for a TIMESTAMP column implicitly changes its default value from CURRENT_TIMESTAMP to NULL if no explicit default value is given.





Data Types related other questions

What is BIT Data Type in MySQL?

In MySQL 5 Certification Study Guide, the BIT data type is described as below:The BIT data type represents bit-field values. BIT column specifications take a width indicating the number of bits per value ...

What are numeric data types in MySQL?

In MySQL 5 Certification Study Guide, Numeric data types are described as below: For storing numeric data, MySQL provides integer data types, floating-point types that ...

What is MySQL default character set?

MySQL\'s default Character Set is latin1 that is also known as ISO-8859-1. The latin1 Character Set uses one byte per character.  To get the current database character set, you may run the following query i ...

What are integer data types in MySQL?

In MySQL 5 Certification Study Guide, integer data types are described as below:For storing numeric data, MySQL provides integer data types. ...

What are float data types in MySQL?

In MySQL 5 Certification Study Guide, Numeric data types are described as below:The floating-point data types include FLOAT and   DOUBLE. Each of these types may be used to ...

What is fixed point and decimal data type in MySQL?

In MySQL 5 Certification Study Guide, Numeric data types are described as below:The fixed-point data type is DECIMAL. It is used to  represent exact-value numbers that have an integer part, a fra ...

What is difference between binary and non-binary string data types?

Some text from \"MySQL 5 Certification Study Guide\" about binary and non-binary string data types is as below: Three data types store non-binary strings: CHAR, VARCHAR, and ...

What are ENUM and SET Data Types in MySQL?

In MySQL 5 Certification Study Guide, the ENUM and SET Data Types are well described as below: The ENUM and SET string data types are used when the values to be stor ...

What is difference between CHAR AND VARCHAR data types?

The CHAR and VARCHAR data types store non-binary strings (that is, strings of characters that have a character set and collation). These types differ in terms of their maximum allowable length and in how tr ...

What are string data types in MySQL 5?

The string data types include binary and non-binary string data.The CHAR, VARCHAR, and TEXT data types store non-binary strings (that is, strings of characters that have a character set and collation). ...

How to reformat date values in MySQL?

You can reformat date values into other display formats using the MySQL DATE_FORMAT() function.  Take Example:SELECT DATE_FORMAT(\'1997-10-04 22:23:00\', \'%W %M %Y\'); -> \'Saturday Octo ...

How to reformat Time values in MySQL?

You can reformat time values into other display formats using the TIME_FORMAT() function in MySQL. Take examples:TIME_FORMAT(time_expression, format_string)SELECT TIME_FORMAT( CURRENT_TIME, \'%f\' ...

What are Temporal Data Types in MySQL?

MySQL provides data types for storing different kinds of temporal information. The following table summarizes the storage requirements and ranges for the date and time data types.  In the following descr ...

What are DATE, TIME, DATETIME and YEAR Data Types in MySQL?

The DATE data type represents date values in \'YYYY-MM-DD\' format. The supported range of DATE values is \'1000-01-01\' to \'9999-12-31\'. You might be able to ...

What is TIMESTAMP Data Type in MySQL?

In MySQL 5 Certification Study Guide, the TIMESTAMP data type is described in detailed. Some useful text is as below:The TIMESTAMP type, like DATETIME, stores date-and-time values, but has a different range a ...