TL;DR: Set your server timezone and connection time zone to both ‘+00:00’ and use datetime instead of timestamp when you want to store dates outside the range supported by timestamp ('1970-01-01 00:00:01.000000' to '2038-01-19 03:14:07.999999').
Do’s when using datetime:
- Add a suffix to the column name to reflect the time zone in which the dates are stored. Thus instead of naming your column
registration_date name it registration_date_est to reinforce the fact that the column stores dates in EST for example. - Do any time zone conversion in your application code. When you execute the
insert statement it should have the date in “absolute” format (YYYY-MM-DD HH:MM:SS) without any time zone suffix or offset. This will skip any conversions MySQL would do on the date otherwise and will protect you from any changes to the server’s time zone your DB administrator might do. - When we insert a date in “absolute” form i.e.,
YYYY-MM-DD HH:MM:SS in a datetime or timestamp column, MySQL assumes the date is in the session’s time zone. And by default the session time zone is equal to the server’s time zone unless you override it explicitly. datetime column converts date into the session’s time zone at time of insertion. The date is stored in the form YYYY-MM-DD HH:MM:SS and has no time zone information associated with it. All subsequent select queries will return the stored string irrespective of whatever the session or server time zone is at time of select query. By contrast a timestamp column converts date to UTC offset in seconds. So the date is stored in UTC or Zulu time zone. But all select queries convert the stored date into the session’s time zone and return the date formatted as YYYY-MM-DD HH:MM:SS
where do we start? If you don’t have a MySQL server provisioned, you can provision one easily using Docker:
docker container create \
--name mysql \
--network bridge \
-p 3306:3306 \
-p 33060:33060 \
--log-opt max-file=3 \
--log-opt max-size=3m \
--workdir /home \
--env MYSQL_ROOT_PASSWORD=ib7U8Tz9K20= \
--env MYSQL_DATABASE=test \
--env MYSQL_USER=test_user \
--env MYSQL_PASSWORD=Hq5tPQnVM2k= \
--env TZ=UTC \
mysql:8.0.24 \
mysqld --default-authentication-plugin=mysql_native_password
docker start mysql
All examples in this post are using above MySQL container that uses MySQL version 8.0.24. The reason for using mysql_native_password is explained in https://github.com/docker-library/wordpress/issues/313
system_time_zone vs. global time_zone vs. session time_zone
The TZ environment variable is used to set the system time zone. The default value is UTC which is same as +00:00.
mysql> show global variables like '%zone%';
+------------------+--------+
| Variable_name | Value |
+------------------+--------+
| system_time_zone | UTC | --> this can be changed using TZ environment variable as per docs. this is the time zone of the OS.
| time_zone | SYSTEM | --> change this in my.cnf file under mysqld or using SET GLOBAL time_zone = <timezone>; this is the time zone of mysql server.
+------------------+--------+
2 rows in set (0.01 sec)
This page is must read:https://dev.mysql.com/doc/mysql-g11n-excerpt/5.7/en/time-zone-support.html MySQL Server maintains several time zone settings:
* The system time zone. When the server starts, it attempts to determine the time zone of the host machine automatically and uses it to set the system_time_zone system variable. The value does not change thereafter.
* The server current time zone. The global time_zone system variable indicates the time zone the server currently is operating in. The initial time_zone value is ‘SYSTEM’, which indicates that the server time zone is the same as the system time zone
assuming you have a MySQL server provisioned, log into the server and run:
mysql> set global time_zone='+01:00';
Query OK, 0 rows affected (0.00 sec)
The server or global time_zone can also be changed in /etc/mysql/conf.d/mysql.cnf. Add following lines to the file as example:
[mysqld]
default-time-zone='+00:00'
Below command shows how to set the session time zone:
mysql> set @@time_zone='-07:00';
Query OK, 0 rows affected (0.01 sec)
The server or global time zone is intrinsic to the server and the connection or session time zone is scoped to that connection or session. The two can be different but the learning in this lesson is not to do that to avoid confusion and keep things simple.
mysql> SELECT @@global.time_zone, @@session.time_zone;
+--------------------+---------------------+
| @@global.time_zone | @@session.time_zone |
+--------------------+---------------------+
| +01:00 | -07:00 |
+--------------------+---------------------+
1 row in set (0.00 sec)
The timezones can also be set using friendly names such as America/New York but this relies on the presence of a table what MySQL looks up internally to get the numerical offset. If this table does not exist named timezones will not work. From MySQL documentation:
Named time zones can be used only if the time zone information tables in the mysql database have been created and populated. Otherwise, use of a named time zone results in an error
https://dev.mysql.com/doc/mysql-g11n-excerpt/8.0/en/time-zone-support.html
Datetime vs. Timestamp
MySQL supports two data types to store datetime values – datetime and timestamp. There are subtle differences between the two. To illustrate the difference, create a table with a datetime and timestamp column in it:
mysql> create table test1 (row_id int unsigned auto_increment, timestamp timestamp, datetime datetime, primary key (row_id) ) engine=innodb;
Query OK, 0 rows affected (0.02 sec)
Next we insert same date (ISO formatted) in both the columns:
mysql> insert into test1 (timestamp, datetime) values ('2016-05-25T09:08:34.123+06:00', '2016-05-25T09:08:34.123+06:00');
Query OK, 1 row affected (0.01 sec)
Now what do you expect when you do a select?
mysql> select * from test1;
+--------+---------------------+---------------------+
| row_id | timestamp | datetime |
+--------+---------------------+---------------------+
| 1 | 2016-05-24 20:08:34 | 2016-05-24 20:08:34 |
+--------+---------------------+---------------------+
1 row in set (0.00 sec)
Explanation:
9-6-7+24
20
9 for the original 09:08:34 in our insert statement
-6 to convert to GMT from +06:00 (refer insert statement)
-7 to convert to session time zone which is -07:00 (refer @@session.time_zone)
24 for modulo 24 arithmetic
change the session time zone:
mysql> set @@time_zone='+03:00';
and do select again. what do you expect?
mysql> select * from test1;
+--------+---------------------+---------------------+
| row_id | timestamp | datetime |
+--------+---------------------+---------------------+
| 1 | 2016-05-25 06:08:34 | 2016-05-24 20:08:34 |
+--------+---------------------+---------------------+
1 row in set (0.00 sec)
Explanation:
9-6+3
6
the timestamp field changes but not datetime and that is the primary difference between timestamp and datetime.
change the server time zone:
mysql> set global time_zone='+05:00';
and do select again. what do you expect?
mysql> select * from test1;
+--------+---------------------+---------------------+
| row_id | timestamp | datetime |
+--------+---------------------+---------------------+
| 1 | 2016-05-25 06:08:34 | 2016-05-24 20:08:34 |
+--------+---------------------+---------------------+
1 row in set (0.00 sec)
we get back same result. so its the connection time zone that matters or overrides the server time zone in case the two are different.
we can see this with the CURRENT_TIMESTAMP function as well.
mysql> select current_timestamp();
+---------------------+
| current_timestamp() |
+---------------------+
| 2021-06-09 01:59:11 |
+---------------------+
1 row in set (0.00 sec)
change session time_zone
mysql> set @@time_zone = '-09:00';
Query OK, 0 rows affected (0.00 sec)
mysql> select current_timestamp();
+---------------------+
| current_timestamp() |
+---------------------+
| 2021-06-08 13:59:51 |
+---------------------+
1 row in set (0.00 sec)
change server time_zone:
mysql> set global time_zone='+04:00';
Query OK, 0 rows affected (0.00 sec)
mysql> select current_timestamp();
+---------------------+
| current_timestamp() |
+---------------------+
| 2021-06-08 14:00:33 |
+---------------------+
1 row in set (0.00 sec)
note the timestamp did not change in accordance with server time_zone. This is documented in MySQL manual as well:
https://dev.mysql.com/doc/refman/8.0/en/date-and-time-functions.html#function_now:
The value is expressed in the session time zone.
Another way to understand the essential difference between datetime and timestamp is that datetime converts time into session’s time zone at time of insertion whereas timestamp stores time in UTC and converts this time into the session’s time zone at time of retrieval.
Let’s explain this further.
When you insert a time (e.g., 2016-05-25T09:08:34.123+06:00) into a datetime column, datetime converts that time into the session’s time zone. And remember by default session time zone will equal the server’s time zone unless you change it explicitly using select @@time_zone query. So if the session time zone is UTC-07:00 for example, the time becomes
2016-05-24 20:08:34
as we saw earlier and this is the value that is stored and that you will get back from MySQL when you do a select query. There is no time zone stored. Time zone is essentially like units (km vs. mi if we were measuring distance e.g.). When you do a select query on a datetime column, to make sense of the returned time, you should know what the time zone was when the value was inserted into the datetime column. Because a datetime column does not store the time zone information in it, a good practice to adopt is to add a suffix to the column name which tells the user the time zone the date is in. Thus rather than naming your column registration_date name it registration_date_est to reflect the fact that the dates stored in the column are in US Eastern time zone.
Now contrast with timestamp. When you insert 2016-05-25T09:08:34.123+06:00 into a timestamp column, the time is converted to UTC – more accurately the offset from the UTC epoch (January 1, 1970, 00:00:00) in seconds which is 1,464,145,714 in this case and this is the value that gets stored internally. But when you do a select query on timestamp column, the stored time is converted into the current session’s time zone before being returned to the client. So if the current time zone is UTC+03:00 the returned value will be 2016-05-25T06:08:34.123 (explanation: 9-6+3 is 6). And the return value will change if we change the session’s time zone.
This is really the difference between datetime and timestamp and the reason timestamp can store time only up to 2038-01-19 03:14:07.999999 is because the max value a 4 byte signed integer can hold is 2,147,483,647 and that is the UTC offset corresponding to 2038-01-19 03:14:07.999999
WARNING: When using datetime you should never change the session time zone during the course of your application’s lifetime because if you do so, the two sets of dates will now be in different time zones. E.g., say the session time zone was EST and you stored 2021-06-10 19:20:19 in a row. Then a week later you – or the DB administrator – decided to change the server time zone to PST. Unless you are explicitly setting session time zone to EST in your application code (Node.js, Java etc.), the new dates will now be stored in PST while the earlier dates are in EST. This is likely to break your application!
Next, we will demo the fact that there is no conversion to the session’s time zone when inserting values into a datetime column if the input date does not have an explicit time zone offset. To demo this, we will run following command:
mysql> insert into test1 (datetime) values('2021-06-10 09:22:16');
Query OK, 1 row affected (0.00 sec)
Now let’s query the datetime:
| row_id | timestamp | datetime |
| 2 | NULL | 2021-06-10 09:22:16 |
We can see no conversion happened. This is because if no time zone offset has been explicitly specified, then MySQL assumes the input date is already in the session’s time zone – a reasonable assumption to make. We can use this fact to protect ourselves from our DB administrator who may change the server time zone. If our application is setup so that it inserts dates in “absolute” format, our dates will be immune to undesired MySQL conversions.
When we have a datetime defined like this for example:
`datetime` datetime default current_timestamp
it auto-inserts the current_timestamp in the datetime column when a row is added. Functionally, its equivalent to calling:
mysql> insert into test1 (datetime) values(CURRENT_TIMESTAMP());
And since current_timestamp will return the date in the session’s time zone with no explicit offset, MySQL will not do any conversion to the value while inserting it into the datetime column. This gives us the expected behavior and “does the right thing”.
How does the timestamp column behave if the date being inserted is in “absolute” format i.e., has no time zone offset included with it? Let’s try it (before running the command make sure your session time zone is set to something other than UTC):
mysql> insert into test1 (timestamp) values ('2021-06-10 09:40:25');
Query OK, 1 row affected (0.00 sec)
What do you expect when you do a select?
mysql> select * from test1;
+--------+---------------------+---------------------+
| row_id | timestamp | datetime |
+--------+---------------------+---------------------+
...
| 4 | 2021-06-10 09:40:25 | NULL |
+--------+---------------------+---------------------+
4 rows in set (0.00 sec)
we get back same result. Again, MySQL assumes that since we did not specify the time zone offset the given time is in the session’s time zone. And is the right thing to do and gives sensible results.
Using Node.js mysql2 library
In part 2 of this post we discover gotchas related to Node.js mysql2 library. I prefer to use this library over mysql for two reasons:
- first, it has support for Promises
- second, it supports prepared statements
Let’s read the values using Node.js mysql2 library:
const mysql2 = require('mysql2');
const conn = mysql2.createConnection({
host : 'localhost',
user : 'root',
password : 'xxx',
port : 3306,
database : 'test',
timezone : '+10:00'
});
conn.query('select * from test1', function(error, results, fields) {
if (error) throw error;
for (var row of results) {
console.log(row);
}
conn.end();
});
What do you expect? Before we run let’s check values of the two time zones:
mysql> select @@global.time_zone, @@session.time_zone;
+--------------------+---------------------+
| @@global.time_zone | @@session.time_zone |
+--------------------+---------------------+
| +04:00 | -09:00 |
+--------------------+---------------------+
1 row in set (0.00 sec)
bash-5.0# node read-values.js
TextRow {
row_id: 1,
timestamp: 2016-05-24T21:08:34.000Z,
datetime: 2016-05-24T10:08:34.000Z
}
Let’s understand the timestamp we got 2016-05-24T21:08:34.000Z:
9-6-10+4+24
21
9 for original 09:08:34.123 in our insert statement
-6 to convert it to GMT (refer offset in insert staement)
-10 comes from timezone in createConnection. This is opposite of correct behavior. The offset was positive but mysql2 applies it in the wrong direction (bug in mysql2)
+4 comes from @@global.time_zone. This is mysql2 doing its own thing. I don’t think it should be doing this.
+24 for modulo 24 arithmetic
But is this what we would expect? Based on our experiments with the CLI, we would have expected to get:
9-6+10
13
9-6 converts to GMT and then we apply the +10 offset in our timezone
Explanation of the difference: Reading the documentation and specifically as called out in this PR, the timezone property doesn’t set the session time_zone variable. Read and digest it again. The session time zone is set equal to the server’s time zone when the mysql2 connection is made. To change it, you have to execute a set @@time_zone query from mysql2 just like we did for the CLI. The node-mysql docs say that the timezone should be set equal to the time_zone configured on the MySQL server. Thus in our case it should be set to +04:00. And what happens if we do that? We get:
9-6-4+4
3
It cancels out the time_zone on the server and we get correct time in UTC. This also explains the Z suffix in the returned value if you observe carefully.
Now let’s understand how we get 2016-05-24T10:08:34.000Z for the datetime. In this case the value that got stored in the datetime column was 2016-05-24 20:08:34 and
20-10
10
where -10 is due to the timezone in the createConnection command. But is this the expected value? You might think the expected value should be 2016-05-24 20:08:34 because as in the CLI case the server returns 2016-05-24 20:08:34 irrespective of the session timezone. And it seems mysql2 incorrectly applies the timezone “correction” in this case. But hold on. What if you had set mysql2 timezone equal to the value of the time zone when the time was inserted into the datetime column? At time of insertion the session time zone was ‘-07:00’. Try making this change in the code snippet and running it again. What do you get?
bash-5.0# node read-values.js
TextRow {
row_id: 1,
timestamp: 2016-05-25T10:08:34.000Z,
datetime: 2016-05-25T03:08:34.000Z
}
The server would return 2016-05-24 20:08:34 and mysql2 applies timezone “correction” in the opposite direction to give 2016-05-25T03:08:34Z and this is the time in GMT that was inserted into the database. Note the Z suffix.
So the purpose of the timezone variable in mysql2 is to set it to what the session timezone was at time of insertion in case of datetime columns and set it to the current session’s timezone in case of timestamp columns. When you do this, mysql2 returns time from datetime or timestamp columns in GMT or Zulu timezone. Of course, if the current vs. past session timezones are different you can only do one and will only get either datetime correct or the timestamp correct. So the lesson is not to change your server’s time zone.
The timezone property in mysql2 has no effect on write operations when datetime or timestamps are inserted into the database. Its effect appears only on read (select) operations.
mysql> set global time_zone='-13:00';
Query OK, 0 rows affected (0.00 sec)
mysql> set time_zone='+05:00';
Query OK, 0 rows affected (0.00 sec)
mysql> select @@global.time_zone, @@session.time_zone;
+--------------------+---------------------+
| @@global.time_zone | @@session.time_zone |
+--------------------+---------------------+
| -13:00 | +05:00 |
+--------------------+---------------------+
1 row in set (0.00 sec)
bash-5.0# node read-values.js
TextRow {
row_id: 1,
timestamp: 2016-05-24T04:08:34.000Z,
datetime: 2016-05-24T10:08:34.000Z
}
Verify:
for the timestamp:
9-6-10-13+24
4
for the datetime changing the server time zone has no effect and we get back the same
2016-05-24T10:08:34.000Z
the @@session.time_zone we set in CLI has no effect on values returned from mysql2 as mysql2 is using a different session altogether from the CLI.
- This – https://github.com/sidorares/node-mysql2/issues/1336 – is a bug I filed where
timezone in connection makes no difference when updating a timestamp field using CURRENT_TIMESTAMP. But once we realize that mysql2 timezone does not set the connection time_zone, we can see why. The connection time_zone is always equal to the server timezone no matter what you set in mysql2 timezone property. Thus this property will have no effect on write operations. - following bug can also be understood in light of what we learned above: https://github.com/sidorares/node-mysql2/issues/1337
from mysql documentation, the range for DATETIME values is '1000-01-01 00:00:00.000000' to '9999-12-31 23:59:59.999999', and the range for TIMESTAMP values is '1970-01-01 00:00:01.000000' to '2038-01-19 03:14:07.999999'
The MySQL client/server protocol is explained here: https://dev.mysql.com/doc/internals/en/client-server-protocol.html
Its fun to read the 6,000 page MySQL manual: https://downloads.mysql.com/docs/refman-8.0-en.pdf
