Summary: in this tutorial, you will learn about MySQL TIMESTAMP and its features such as automatic initialization and automatic update to create auto-initialized and auto-updated columns for a table.
Introduction to MySQL TIMESTAMP
The MySQL TIMESTAMP is a temporal data type that hold the combination of date and time. The format of TIMESTAMPcolumn is YYYY-MM-DD HH:MM:SS which is fixed at 19 characters.
The TIMESTAMP value has a range of ’1970-01-01 00:00:01′ UTC to ’2038-01-19 03:14:07′ UTC.
When you insert a TIMESTAMP value, MySQL converts it from your connection’s time zone to UTC for storage. When you query a TIMESTAMP value, MySQL converts the UTC value back to your connection’s time zone. Notice that this conversion does not occur for other temporal data type such as DATETIME.
By default, the connection time zone is the MySQL database server’s time zone. You can use a different time zone when you connect to MySQL database server.
When you retrieve a TIMESTAMP value that were inserted by a client in a different time zone , you will get a value that is not the same as the value stored in the database. As long as you don’t change the time zone, you can get the same TIMESTAMP value that you store.
MySQL TIMESTAMP time zone example
Let’s look at an example to see how MySQL handles TIMESTAMP values.
First, created a new table named test_timestamp that has a TIMESTAMP column: t1;
CREATE TABLE IF NOT EXISTS test_timestamp ( t1 TIMESTAMP );
Second, set session the time zone to ‘+00:00′ UTC by using the SET time_zone statement.
SET time_zone='+00:00';
Third, insert a TIMESTAMP value into the test_timestamp table .
INSERT INTO test_timestamp VALUES('2008-01-01 00:00:01');Fourth, select the TIMESTAMP value from the test_timestamp table.
SELECT t1 FROM test_timestamp;
Fifth, set the session’s time zone to a different time zone and see what value we get back from the database server:
SET time_zone ='+03:00'; SELECT t1 FROM test_timestamp;
As you see, we got a different value that is adjusted to the new time zone.
Automatic initialization and automatic update features
The TIMESTAMP data type provides a very useful feature that allows you to:
Set the current timestamp for the inserted rows automatically if you don’t specify value for the timestamp column. This timestamp column is called auto-initialized column. This feature of the
TIMESTAMPis called automatic initialization.Set the current timestamp for the updated rows when the values in other column change. This timestamp column is known as auto-updated column. This feature of the
TIMESTAMPis called automatic update.
Let’s take an example to have a better understanding how auto-initialized column and auto-updated column work.
First, create a table named ts that has two timestamp columns:
CREATE TABLE ts( id INT AUTO_INCREMENT PRIMARY KEY, title VARCHAR(255) NOT NULL, created_on TIMESTAMP DEFAULT 0, changed_on TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP );
The data type of the created_on column is TIMESTAMP with the default value is CURRENT_TIMESTAMP. When you insert a new row into the ts table without specifying value for the created_on column, the created_on column will take the current timestamp as the default value.
The data type of the changed_on column is the TIMESTAMP with the default value is CURRENT_TIMESTAMP. In addition, it has a special attribute ON UPDATE CURRENT_TIMESTAMP. This allows the changed_on column to take the current timestamp value as the default value when the values of other columns change e.g., title.
Second, insert a new row into the ts table:
INSERT INTO ts(title) VALUES('Test MySQL timestamp');Third, select data from the ts table:
SELECT * FROM ts;
The created_on and changed_on column take the current timestamp as the default.
Fourth, update the title column value
UPDATE ts SET title = 'Test MySQL timestamp update' WHERE id = 1;
Fifth, query data from the ts table again:
SELECT * FROM ts;
MySQL updates the value of the changed_on column to the current timestamp automatically when the value of the title column changes by the UPDATE statement.
Note: since MySQL 5.6.5, the DATETIME data type also has automatic initialization and automatic update feature. In addition, the DEFAULT_CURRENT_TIMESTAMP and ON UPDATE CURRENT TIMESTAMP attributes can be applied to multiple columns, not just 1 column in the previous versions.
In this tutorial, we have introduced you to MySQL TIMESTAMP data type and shown you how to use automatic initialization and automatic update features of TIMESTAMP.