{"id":183,"date":"2011-03-21T05:31:16","date_gmt":"2011-03-21T05:31:16","guid":{"rendered":"http:\/\/www.mysqltutorial.org\/?page_id=183"},"modified":"2023-12-27T19:19:57","modified_gmt":"2023-12-28T02:19:57","slug":"understanding-mysql-timestamp","status":"publish","type":"page","link":"https:\/\/www.mysqltutorial.org\/mysql-basics\/understanding-mysql-timestamp\/","title":{"rendered":"MySQL TIMESTAMP Data Type"},"content":{"rendered":"\n

Summary<\/strong>: in this tutorial, you will learn about MySQL TIMESTAMP<\/code> data type and how to use it to define columns that store timestamp data.<\/p>\n\n\n\n

Introduction to MySQL TIMESTAMP data type<\/h2>\n\n\n\n

The MySQL TIMESTAMP<\/code> is a temporal data type<\/a> that holds the combination of date<\/a> and time<\/a>. The format <\/a>of a TIMESTAMP<\/code> is YYYY-MM-DD HH:MM:SS<\/code> which is fixed at 19 characters.<\/p>\n\n\n\n

The TIMESTAMP<\/code> value has a range from '1970-01-01 00:00:01'<\/code> UTC to '2038-01-19 03:14:07'<\/code> UTC.<\/p>\n\n\n\n

When you insert <\/a>a TIMESTAMP<\/code> value into a table, MySQL converts it from your connection’s time zone to UTC for storing.<\/p>\n\n\n\n

When you query<\/a> a TIMESTAMP<\/code> value, MySQL converts the UTC value back to your connection’s time zone. This conversion does not occur for other temporal data types, such as DATETIME<\/a><\/code>.<\/p>\n\n\n\n

By default, the connection time zone is the MySQL Server’s time zone. You also have the option to use a different time zone when connecting to the MySQL Server.<\/p>\n\n\n\n

When you retrieve a TIMESTAMP<\/code> value that was inserted by a client in a different time zone, you will receive a value different from the one stored in the database. <\/p>\n\n\n\n

However, as long as you don’t change the time zone, you can retrieve the originally stored TIMESTAMP<\/code> value.<\/p>\n\n\n\n

MySQL TIMESTAMP time zone example<\/h2>\n\n\n\n

Let’s take an example to see how MySQL handles TIMESTAMP<\/code> values.<\/p>\n\n\n\n

First, created a new table<\/a> called t<\/code> that has a TIMESTAMP<\/code> column: t1<\/code>;<\/p>\n\n\n

CREATE<\/span> TABLE<\/span> t (\n   ts TIMESTAMP<\/span>\n);<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Second, set the session’s time zone to '+00:00'<\/code> UTC by using the SET time_zone<\/code> statement.<\/p>\n\n\n
SET<\/span> time_zone<\/span>='+00:00'<\/span>;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Third, insert a TIMESTAMP<\/code> value into the t<\/code> table.<\/p>\n\n\n
INSERT<\/span> INTO<\/span> t(ts)\nVALUES<\/span>('2008-01-01 00:00:01'<\/span>);<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Fourth, select the TIMESTAMP<\/code> value from the t<\/code> table.<\/p>\n\n\n
SELECT<\/span> ts FROM<\/span> t;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Output:<\/p>\n\n\n
+---------------------+\n| ts                  |\n+---------------------+\n| 2008<\/span>-01<\/span>-01<\/span> 00<\/span>:00<\/span>:01<\/span> |\n+---------------------+\n1<\/span> row in<\/span> set<\/span> (0.00 sec)<\/code><\/span>Code language:<\/span> JavaScript<\/span> (<\/span>javascript<\/span>)<\/span><\/small><\/pre>\n\n\n
Fifth, set the session’s time zone to a different time zone to observe what value we receive from the database server:<\/p>\n\n\n
SET<\/span> time_zone<\/span> ='+03:00'<\/span>;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Finally, query data from the table:<\/p>\n\n\n
SELECT<\/span> ts FROM<\/span> t;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Output:<\/p>\n\n\n
+---------------------+\n| ts                  |\n+---------------------+\n| 2008<\/span>-01<\/span>-01<\/span> 03<\/span>:00<\/span>:01<\/span> |\n+---------------------+\n1<\/span> row in<\/span> set<\/span> (0.00 sec)<\/code><\/span>Code language:<\/span> JavaScript<\/span> (<\/span>javascript<\/span>)<\/span><\/small><\/pre>\n\n\n
As you can see, we received a time value adjusted to the new time zone, which is different from the original.<\/p>\n\n\n\n
Automatic initialization and updating for TIMESTAMP columns<\/h2>\n\n\n\n
Consider the following example.<\/p>\n\n\n\n
First, create a table<\/a> named  categories<\/code>:<\/p>\n\n\n
CREATE<\/span> TABLE<\/span> categories (\n    id<\/span> INT<\/span> AUTO_INCREMENT PRIMARY KEY<\/span>,\n    name<\/span> VARCHAR<\/span>(255<\/span>) NOT<\/span> NULL<\/span>,\n    created_at TIMESTAMP<\/span> DEFAULT<\/span> CURRENT_TIMESTAMP<\/span>\n);<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
In the categories<\/code> table, the created_at<\/code> column is a TIMESTAMP<\/code> column whose default value is set to CURRENT_TIMESTAMP<\/code>.<\/p>\n\n\n\n
INSERT<\/span> INTO<\/span> categories(name<\/span>) \nVALUES<\/span> ('A'<\/span>);<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n
SELECT<\/span> * FROM<\/span> categories;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
\"MySQL<\/figure>\n\n\n\n
The output indicates that MySQL used the timestamp at the time of insertion as a default value for the created_at<\/code> column.<\/p>\n\n\n\n
So a TIMESTAMP<\/code> column can be automatically initialized to the current timestamp for inserted rows that specify no value for the column. This feature is called automatic initialization<\/strong>.<\/p>\n\n\n\n
Third, add a new column<\/a> named updated_at<\/code> to the categories<\/code> table:<\/p>\n\n\n
ALTER<\/span> TABLE<\/span> categories\nADD<\/span> COLUMN<\/span> updated_at \n  TIMESTAMP<\/span> DEFAULT<\/span> CURRENT_TIMESTAMP<\/span> \n  ON<\/span> UPDATE<\/span> CURRENT_TIMESTAMP<\/span>;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
The default value of the updated_at<\/code> column is CURRENT_TIMESTAMP<\/code>.<\/p>\n\n\n\n
And, we have a new clause ON UPDATE CURRENT_TIMESTAMP<\/code> that follows the DEFAULT CURRENT_TIMESTAMP<\/code> clause. Let’s see its effect.<\/p>\n\n\n\n
INSERT<\/span> INTO<\/span> categories(name<\/span>)\nVALUES<\/span>('B'<\/span>);<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Fifth, query data from the categories<\/code> table:<\/p>\n\n\n
SELECT<\/span> * FROM<\/span> categories;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
\"MySQL<\/figure>\n\n\n\n
The default value of the column created_at<\/code> is the timestamp when the row was inserted.<\/p>\n\n\n\n
Sixth, update the value in the column name<\/code> of the row id 2:<\/p>\n\n\n
UPDATE<\/span> categories \nSET<\/span> name<\/span> = 'B+'<\/span>\nWHERE<\/span> id<\/span> = 2<\/span>;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Seventh, query data from the categories<\/code> table to check the update:<\/p>\n\n\n
SELECT<\/span> *\nFROM<\/span> categories\nWHERE<\/span> id<\/span> = 2<\/span>;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
\"MySQL<\/figure>\n\n\n\n
Notice that the value in the updated_at<\/code> column changed to the timestamp at the time the row was updated.<\/p>\n\n\n\n
The ability of a TIMESTAMP<\/code> column to be automatically updated to the current timestamp when the value in any other column in the row changes from its current value is called automatic updating<\/strong>.<\/p>\n\n\n\n
The column updated_at<\/code> is referred to as an auto-updated column.<\/p>\n\n\n\n
Note that if you execute the UPDATE<\/code> statement to update the same value for the name<\/code> column, the updated_at<\/code> column will not be updated.<\/p>\n\n\n
UPDATE<\/span> categories \nSET<\/span> name<\/span> = 'B+'<\/span>\nWHERE<\/span> id<\/span> = 2<\/span>;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
The value in the updated_at<\/code> remains unchanged.<\/p>\n\n\n\n
\"MySQL<\/figure>\n\n\n\n
For more information on automatic initialization and updating, please check out the time initialization<\/a>.<\/p>\n\n\n\n
As of MySQL 5.6.5, the DATETIME<\/a><\/code> columns also have automatic initialization and updating features. In addition, the DEFAULT_CURRENT_TIMESTAMP<\/code> and ON UPDATE CURRENT TIMESTAMP<\/code> can be applied to multiple columns.<\/p>\n\n\n\n
Summary<\/h2>\n\n\n\n
    \n
  • Use the MySQL TIMESTAMP<\/code> data type to represent date and time values.<\/li>\n\n\n\n
  • Set the DEFAULT CURRENT_TIMESTAMP<\/code> attribute for a TIMESTAMP<\/code> column to automatically initialize the column with the current timestamp when a new row is inserted.<\/li>\n\n\n\n
  • Set the ON UPDATE CURRENT_TIMESTAMP<\/code> attribute to update the timestamp whenever the row is modified.<\/li>\n\n\n\n
  • MySQL stores the TIMESTAMP<\/code> values in UTC format but converts them to the current session timezone when displayed.<\/li>\n<\/ul>\n
    \n\t
    \n\t\t
    Was this tutorial helpful? <\/div>\n\t\t
    \n\t\t\t\n\t\t\t\t\n\t\t\t\t\t<\/path>\n\t\t\t\t<\/svg>\n\t\t\t\t Yes <\/span>\n\t\t\t<\/button>\n\n\t\t\t\n\t\t\t\t\n\t\t\t\t\t<\/path>\n\t\t\t\t<\/svg>\n\t\t\t\t No <\/span>\n\t\t\t<\/button>\n\t\t<\/div>\n\t<\/header>\n\n\t
    \n\t\t
    \n\t\t\t
    <\/div>\n\t\t\t\n\t\t\t
Fourth, insert<\/a> a new row into the categories<\/code> table.<\/p>\n\n\n
Second, insert<\/a> a new row into the categories<\/code> table without specifying the value for the created_at<\/code> column:<\/p>\n\n\n