Storing time in databases seems like a daunting task to many. The general principle with storing time in databases is – ALWAYS STORE TIME IN UTC. However for a lot of people this raises questions.. but my application expects time to be in US/Pacific, or my database server’s time zone in PDT or most of my users are in US/Eastern. Some also ask that the global setting for MySQL variable time_zone be set to US/Pacific or US/Eastern to match their requirement.
From a DBA’s perspective, I think 99% use cases meet the ALWAYS STORE TIME IN UTC guideline, and for 1% that don’t an understanding of the right way of storing and reading/writing time is necessary to ensure that you are doing the right thing.
MySQL’s datetime data types
MySQL provides two data types to store time – TIMESTAMP and DATETIME
TIMESTAMP stores time as unix timestamp, or seconds since epoch, regardless of what the time zone is.
DATETIME simply takes the date and stores it as a string like representation as YYYYMMDDHHmmSS
While this difference might not be very evident while writing data, it becomes clearer while reading data.
TIMESTAMP
Let’s take an example of a table with TIMESTAMP to store date and time. My time_zone variables has the global setting ‘UTC’
CREATE TABLE `test_timestamp` ( `id` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(255) DEFAULT NULL, `time_created` timestamp NULL DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
Inserting data into the table.
mysql> insert into test_timestamp (name, time_created) values ('a','2019-10-09 21:51:00');
Query OK, 1 row affected (0.01 sec)
Reading data.
mysql> select * from test_timestamp; +----+------+---------------------+ | id | name | time_created | +----+------+---------------------+ | 1 | a | 2019-10-09 21:51:00 | +----+------+---------------------+ 1 row in set (0.00 sec)
So far the difference isn’t quite clear, since we wrote the data in UTC and are reading it in UTC.
Let’s change the time zones around a little and read the same data.
mysql> set time_zone='US/Pacific'; mysql> select * from test_timestamp; +----+------+---------------------+ | id | name | time_created | +----+------+---------------------+ | 1 | a | 2019-10-09 14:51:00 | +----+------+---------------------+ 1 row in set (0.00 sec)
When the data is retrieved it is presented in the time zone, the session value of time_zone variable is set to. Well, what about if I write in one time zone (other than UTC) and read it in another.
mysql> set time_zone='US/Pacific';
Query OK, 0 rows affected (0.00 sec)
mysql> insert into test_timestamp (name, time_created) values ('b','2019-10-09 14:57:00');
Query OK, 1 row affected (0.01 sec)
mysql> select * from test_timestamp;
+----+------+---------------------+
| id | name | time_created |
+----+------+---------------------+
| 1 | a | 2019-10-09 14:51:00 |
| 2 | b | 2019-10-09 14:57:00 |
+----+------+---------------------+
2 rows in set (0.00 sec)
Lets switch back to UTC and read it.
mysql> set time_zone='UTC'; Query OK, 0 rows affected (0.00 sec) mysql> select * from test_timestamp; +----+------+---------------------+ | id | name | time_created | +----+------+---------------------+ | 1 | a | 2019-10-09 21:51:00 | | 2 | b | 2019-10-09 21:57:00 | +----+------+---------------------+ 2 rows in set (0.00 sec)
How about reading it in US/Eastern ?
mysql> set time_zone='US/Eastern'; Query OK, 0 rows affected (0.01 sec) mysql> select * from test_timestamp; +----+------+---------------------+ | id | name | time_created | +----+------+---------------------+ | 1 | a | 2019-10-09 17:51:00 | | 2 | b | 2019-10-09 17:57:00 | +----+------+---------------------+ 2 rows in set (0.00 sec)
TL;DR
- TIMESTAMP stores data in the format of seconds since epoch or unix time.
- If you want the time you store to follow the time zone of user or application server, use TIMESTAMP
- Change the time zone via session value of time_zone, rather than setting the global value
- If it is easier, you can add the time_zone setting to your connection parameters or even if you expect it to be global to your init_connect.
DATETIME
Now let’s take a look at what happens with DATETIME
CREATE TABLE `test_datetime` ( `id` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(255) DEFAULT NULL, `time_created` datetime DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
Writing data in US/Pacific time zone
mysql> set time_zone='US/Pacific';
Query OK, 0 rows affected (0.00 sec)
mysql> insert into test_datetime (name, time_created) values ('c','2019-10-09 15:59');
Query OK, 1 row affected (0.01 sec)
mysql> select * from test_datetime;
+----+------+---------------------+
| id | name | time_created |
+----+------+---------------------+
| 1 | c | 2019-10-09 15:59:00 |
+----+------+---------------------+
1 row in set (0.00 sec)
Now let’s try to read it in UTC.
mysql> set time_zone='UTC'; Query OK, 0 rows affected (0.00 sec) mysql> select * from test_datetime; +----+------+---------------------+ | id | name | time_created | +----+------+---------------------+ | 1 | c | 2019-10-09 15:59:00 | +----+------+---------------------+ 1 row in set (0.00 sec)
The data does not follow the time zone change. How about if we insert data in UTC and try to read it in other time zones?
mysql> set time_zone='UTC';
Query OK, 0 rows affected (0.00 sec)
mysql> insert into test_datetime (name, time_created) values ('d','2019-10-09 22:00');
Query OK, 1 row affected (0.01 sec)
mysql> select * from test_datetime;
+----+------+---------------------+
| id | name | time_created |
+----+------+---------------------+
| 1 | c | 2019-10-09 15:59:00 |
| 2 | d | 2019-10-09 22:00:00 |
+----+------+---------------------+
2 rows in set (0.00 sec)
mysql> set time_zone='US/Pacific';
Query OK, 0 rows affected (0.00 sec)
mysql> select * from test_datetime;
+----+------+---------------------+
| id | name | time_created |
+----+------+---------------------+
| 1 | c | 2019-10-09 15:59:00 |
| 2 | d | 2019-10-09 22:00:00 |
+----+------+---------------------+
2 rows in set (0.00 sec)
The time value you stored always remains what you stored regardless of time zone changes while reading or writing it.
TL; DR
- DATETIME stores the date time value as a string like literal YYYYMMDDHHmmSS
- No matter what the time zone changes are while reading or writing the data you will always see what you stored.
What about now()?
Someone happened to ask me, but what about now()? The answer is now() is now() depending on session value of time_zone and the data type you are storing in it. With TIMESTAMP it follows time zone changes, with DATETIME it will be whatever you store it as, as per your session time zone, always.
mysql> set time_zone='US/Pacific';
Query OK, 0 rows affected (0.00 sec)
mysql> insert into test_datetime(name,time_created) values ('t2',now());
Query OK, 1 row affected (0.01 sec)
mysql> insert into test_timestamp(name,time_created) values ('t2',now());
Query OK, 1 row affected (0.01 sec)
mysql> select * from test_datetime;
+----+------+---------------------+
| id | name | time_created |
+----+------+---------------------+
| 1 | c | 2019-10-09 15:59:00 |
| 2 | d | 2019-10-09 22:00:00 |
| 3 | c | 2019-08-01 06:00:00 |
| 4 | t1 | 2019-10-12 00:00:48 |
| 5 | t2 | 2019-10-11 17:01:45 |
+----+------+---------------------+
5 rows in set (0.00 sec)
mysql> select * from test_timestamp;
+----+------+---------------------+
| id | name | time_created |
+----+------+---------------------+
| 1 | a | 2019-10-09 14:51:00 |
| 2 | b | 2019-10-09 14:57:00 |
| 3 | t1 | 2019-10-11 17:00:59 |
| 4 | t2 | 2019-10-11 17:01:53 |
+----+------+---------------------+
4 rows in set (0.00 sec)
What to use when
- Use TIMESTAMP when you want the timestamp to follow time zone changes of the application users. Examples of this are – timestamps for log events, or a created at / modified at timestamp, timestamps for chat messages or comments etc., notifications
- Use DATETIME when you don’t want the timestamp to follow time zone changes. Examples of this are – Date of Birth
If using these two data types right, with setting time_zone on the session level cannot take care of your time zone issues, then and only then explore changing the time_zone variable globally.
On a lighter note, If you are in the business of using TIMESTAMP, have you heard of the Year 2038 problem? Nothing to freak out about yet, we are only 19 years away đŸ™‚ Anyone having Y2K flashbacks?
DB Geek Girl 
how about table partition where datetime datatype is preferred over timestamp
LikeLike
You can use timestamp in range based partitioning too, using the unix_timestamp function. Whether you use datetime or timestamp might depend on what value you are storing and if you want it to store the timezone context. For example, aggergated values for sales per day might be fine in datetime, but the actual invoice or transaction time, might be preferred to be timezone sensitive and use datetime. The MySQL documentation at https://dev.mysql.com/doc/refman/5.7/en/partitioning-range.html illustrates, how timestamp can be used in a range partition. Refer to the section .. “It is also possible to partition a table by RANGE, based on the value of a TIMESTAMP column, using the UNIX_TIMESTAMP() function,” In case of partitioning a lot of use cases, straight out prefer storing time as int, in seconds since epoch and range partition on int. But the choice might vary depending on the use case.
LikeLike