MySQL: Datetime Versus Timestamp Data Types

The temporal data types in MySQL can be confusing. Hopefully, this example and discussion will help to explain the differences in the timestamp and datetime data types.

From the MySQL reference:

The DATETIME type is used for values that contain both date and time parts. MySQL retrieves and displays DATETIME values in ‘YYYY-MM-DD HH:MM:SS’ format. The supported range is ’1000-01-01 00:00:00′ to ’9999-12-31 23:59:59′.

The TIMESTAMP data type is used for values that contain both date and time parts. TIMESTAMP has a range of ’1970-01-01 00:00:01′ UTC to ’2038-01-19 03:14:07′ UTC.

A major difference between these two data types is that TIMESTAMP data type values are converted from current time zone to UTC for storage purpose and converted back from UTC to current time zone when used. The datetime data type values are unchanged in relation to time zone.

This example is a good exercise in demonstrating the difference between these two data types.

mysql> show variables like '%time_zone%';
+------------------+---------------------+
| Variable_name    |  Value              |
+------------------+---------------------+
| system_time_zone | India Standard Time |
| time_zone        | Asia/Calcutta       |
+------------------+---------------------+
2 rows in set (0.00 sec)

 
You can see our current time zone information. Under this environment, let us create a table with the two data types and populate it with the same temporal information.

create table datedemo
(
 mydatetime datetime,
 mytimestamp timestamp
);

Query OK, 0 rows affected (0.05 sec)
insert into datedemo values ((now()), (now()));

Query OK, 1 row affected (0.02 sec)
select * from datedemo;
+---------------------+---------------------+
| mydatetime          | mytimestamp         |
+---------------------+---------------------+
| 2011-08-21 14:11:09 | 2011-08-21 14:11:09 |
+---------------------+---------------------+
1 row in set (0.00 sec)

 
At this point the datetime and timestamp data types have remained the exact same values. Let us change the time zone see the results.

SET TIME_ZONE = "america/new_york";

Query OK, 0 rows affected (0.00 sec)
 select * from datedemo;
+---------------------+---------------------+
| mydatetime          | mytimestamp         |
+---------------------+---------------------+
| 2011-08-21 14:11:09 | 2011-08-21 04:41:09 |
+---------------------+---------------------+
1 row in set (0.00 sec)

 
The above example shows how the TIMESTAMP date type changed the values after changing the time-zone to ‘america/new_work’ where DATETIME is unchanged.

 

About Vishwanath Dalvi

Vishwanath Dalvi is a gifted engineer and tech enthusiast. He enjoys music, magic, movies, and gaming. When not hacking around or supporting the open source community, he is trying to overcome his phobia of dogs.
View more articles by Vishwanath Dalvi

The Conversation

Follow the reactions below and share your own thoughts.

3 Responses to “MySQL: Datetime Versus Timestamp Data Types”

  1. April 06, 2012 at 7:24 am, Rahul Verma said:

    Very useful info with example.
    thank you.

    Reply

    • April 09, 2012 at 1:29 am, Vishwanath Dalvi said:

      You are welcome.

      Reply

  2. November 16, 2013 at 2:56 pm, Sudhakar Narra said:

    Thank you for this example

    Reply

Leave a Reply