MySQL TIMESTAMP with Examples

This tutorial explains MySQL TIMESTAMP and TIMESTAMP field characteristics such as automated initialization and updating. We’ll describe their usage with the help of simple examples.

Contents

MySQL TIMESTAMP Syntax MySQL TIMESTAMP Examples

Let’s now go through each of the sections one by one.

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, 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 with how MySQL automatically initializes and updates the TIMESTAMP column with an example.

Here is the MySQL statement to create a table named Articles:

CREATE TABLE Artciles
    (
        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 in more than one column.

DEFAULT_CURRENT_TIMESTAMP, and
UPDATE CURRENT TIMESTAMP

We hope that after wrapping up this tutorial, you will feel comfortable using the MySQL TIMESTAMP. However, you may practice more with examples to gain confidence.

Also, to learn SQL from scratch to depth, read our step-by-step MySQL tutorial.