MySQL TIMESTAMP

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:

Syntax

# 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.

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');

SELECT 
    *
FROM
    Artciles;

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