The MySQL TIMESTAMP is a transient data type that contains a mixture of date and time. It is exactly 19 characters long. The structure of a TIMESTAMP field is as follows:
# MySQL Timestamp YYYY-MM-DD HH:MM:SS
The TIMESTAMP value shows in UTC by default. It starts from ‘1970-01-01 00:00:01’ to ‘2038-01-19 03:14:07’ UTC.
If you provide a TIMESTAMP value in the MySQL insert query, then it stores the value in UTC format. However, whenever you query the same field, then MySQL converts it as per the connection’s time zone setting. Please be informed that this behavior does not apply to other temporal data types such as DATETIME. It is valid only for TIMESTAMP values.
Also, MySQL database time zone derives its default mode from the connection setting. However, you can modify the same through configuration changes.
It also implies that any TIMESTAMP value inserted via a client from a different timezone would display in connection time zone format. So, if you keep the timezone same, then it ensures to fetch the exact value you stored.
MySQL TIMESTAMP Examples
Let’s check out how to use TIMESTAMP values in MySQL queries.
Time zone example
To demonstrate, let’s first create a table named as Sample with sample_ts as a TIMESTAMP field.
CREATE TABLE Sample ( sample_id int NOT NULL, sample_name VARCHAR(20), sample_ts TIMESTAMP );
Next, you have to set the timezone to ‘+00:00’ UTC by issuing the SET time_zone command.
SET TIME_ZONE = '+00:00';
After this, you have to insert a few rows while specifying TIMESTAMP values. See below.
INSERT INTO Sample ( sample_id, sample_name, sample_ts ) VALUES (1, 'First Sample', '2016-01-01 00:00:01'), (2, 'Second Sample', '2017-01-01 00:00:01'), (3, 'Third Sample', '2018-01-01 00:00:01'), (4, 'Fourth Sample', '2019-01-01 00:00:01');
Now, view the table content by running the SELECT statement.
SELECT * FROM Sample;
Its output is as follows:
1 First Sample 2016-01-01 00:00:01 2 Second Sample 2017-01-01 00:00:01 3 Third Sample 2018-01-01 00:00:01 4 Fourth Sample 2019-01-01 00:00:01
Finally, change the session’s timezone, rerun the SELECT, and observe the value you receive from the MySQL database:
SET time_zone ='+04:00'; SELECT * FROM Sample;
This time, you’ll get a result showing a different timestamp value:
1 First Sample 2016-01-01 04:00:01 2 Second Sample 2017-01-01 04:00:01 3 Third Sample 2018-01-01 04:00:01 4 Fourth Sample 2019-01-01 04:00:01
Auto Init and Update TIMESTAMP Field
Let’s begin how does MySQL automatically initialize and update the TIMESTAMP column with an example.
Here is the MySQL statement to create a table named Articles:
CREATE TABLE Articles ( articleId INT AUTO_INCREMENT PRIMARY KEY, articleTitle VARCHAR(60), dateCreated TIMESTAMP, datePublished TIMESTAMP DEFAULT CURRENT_TIMESTAMP );
In the above table, there are two TIMESTAMP fields:
- dateCreated – It represents the date when the author first wrote the article.
- datePublished – The date-time when the author updated the article.
Since the creation date is static, so we need to supply an appropriate value for it. However, the other field would change as per the current time, so we used the CURRENT_TIMESTAMP as the default.
Let’s validate the auto initialization of the datePublished field. Below is the insert command to run.
INSERT INTO Artciles ( articleTitle, dateCreated ) VALUES ('How to Use MySQL Insert Statement', '2019-07-21 00:00:01'), ('How to Use MySQL Select Statement', '2019-07-14 00:00:01'), ('How to Use MySQL Update Statement', '2019-07-07 00:00:01'), ('How to Use MySQL Delete Statement', '2019-07-01 00:00:01');
You can see that we only supplied the article title and creation date. MySQL will store default values for the rest of the fields.
SELECT * FROM Artciles;
The output of SELECT is:
1 How to Use MySQL Insert Statement 2019-07-21 00:00:01 2019-07-27 07:00:54 2 How to Use MySQL Select Statement 2019-07-14 00:00:01 2019-07-27 07:00:54 3 How to Use MySQL Update Statement 2019-07-07 00:00:01 2019-07-27 07:00:54 4 How to Use MySQL Delete Statement 2019-07-01 00:00:01 2019-07-27 07:00:54
Please note that you can use the auto init and update feature with DATETIME type as well in MySQL 5.6.5 and later versions. Besides, you can apply the below MACROs on more than one columns.
- DEFAULT_CURRENT_TIMESTAMP, and
- UPDATE CURRENT TIMESTAMP
We hope that after wrapping up this tutorial, you should feel comfortable in using the MySQL TIMESTAMP.