Datetime - Timestamp - Timezone issues

[FredericLatour]

Introduction

This article will provide my results testing Timezone matters for various nodejs libraries/drivers when MySql does not hold datetime in UTC.

One could argue that it is certainly way better to store all dates in UTC (especially because UTC is DST independant) however there maybe a couple of reason not having dates in UTC:

• a legacy database with multiple apps where timezone considerations were not taken into account (and that's indeed why I've done this work)
• because one would prefer to manipulate date in its own timezone when directly using a query tool.

Because I was struggling with timezone and because the documentation does often leaves to be desired, I decided to to some testing with the following libraries:

• mikroorm
• mariadb connector
• mysql
• typeorm

The setup

• a separate Mysql server witht timezone configured to '+05:00'
• a table that hold both a DATETIME column and a TIMESTAMP column
• a separate server with a node application running the test. This server local time is GMT+1 based.

What I'm testing

• I'm retrieving an existing record that holds both a DATETIME and a TIMESTAMP column and see what I get and if it sounds coherent.
• I insert a record using the same date for both the DATETIME and TIMESTAMP and retrieve back the record and see what I get

notes

Having Mysql timezone configured to '+05:00' basically means that all dates in Mysql are GMT+5 based. This is examplified by the fact that CURRENT_TIMESTAMP will return a GMT+5 date.

In mysql, we can store date and time in DATETIME column and TIMESTAMP columns. DATETIME does not hold any timezone information. Therefore you have to know what you are doing by convention.
TIMESTAMP Will store dates in UTC and will display using whatever timezone is currently configured.
Initialising both type of columns with CURRENT_TIMESTAMP will display the exact same date. However, because TIMESTAMP are stored in UTC, if you change MySql current timezone, TIMESTAMP columns will display their DATETIME value according to the new timezone while DATETIME columns are left unchaged.

Mysql Configuration

UTC_TIMESTAMP()	CURRENT_TIMESTAMP()	@@system_time_zone	@@time_zone	@@GLOBAL.time_zone	@@SESSION.time_zone
2020-03-20 23:32:11	2020-03-21 04:32:11	CET	+05:00	SYSTEM	+05:00

Retrieving an existing record - Connection timezone configured to '+05:00'

MySql TimeZone: +05:00 Nodejs env Timezone: CET	mikroorm Cx timezone: +05:00		mariadb Cx timezone: +05:00		mysql Cx timezone: +05:00		typeorm Cx timezone: +05:00
	DateTimeColumn	TimeStampColumn	DateTimeColumn	TimeStampColumn	DateTimeColumn	TimeStampColumn	DateTimeColumn	TimeStampColumn
As Displayed In MySql	2020-03-17 11:11:11	2020-03-17 11:11:11	2020-03-17 11:11:11	2020-03-17 11:11:11	2020-03-17 11:11:11	2020-03-17 11:11:11	2020-03-17 11:11:11	2020-03-17 11:11:11
Date.toString()	2020-03-17 11:11:11 GMT+0100	2020-03-17 07:11:11 GMT+0100	2020-03-17 07:11:11 GMT+0100	2020-03-17 03:11:11 GMT+0100	2020-03-17 07:11:11 GMT+0100	2020-03-17 03:11:11 GMT+0100	2020-03-17 07:11:11 GMT+0100	2020-03-17 03:11:11 GMT+0100
Date.toUTCString()	2020-03-17 10:11:11 GMT	2020-03-17 06:11:11 GMT	2020-03-17 06:11:11 GMT	2020-03-17 02:11:11 GMT	2020-03-17 06:11:11 GMT	2020-03-17 02:11:11 GMT	2020-03-17 06:11:11 GMT	2020-03-17 02:11:11 GMT

Analysis

First of all, I would expect the exact same result for both DATETIME and TIMESTAMP column. The fact that MySql store TIMESTAMP values as UTC internally should not affect the expected result. Therefore I'm expecting the following results for both columns:

As Displayed In MySql	2020-03-17 11:11:11
Date.toString()	2020-03-17 07:11:11 GMT+0100
Date.toUTCString()	2020-03-17 06:11:11 GMT

mariadb, mysql and typeorm

They have the same results and seem to use the same approach. I suppose that typeorm relies on the driver. The DATETIME column is handled properly and I get the expected results. The TIMESTAMP column gives wrong results Not sure what it does because there is a 4 hours shift.

mikroorm

Has it's own approach here.
Contrary to other libraries, TIMESTAMP provides the expected values here.
On the other hand DATETIME column provides wrong result. It seems to be handled as if the value was in the timezone of the running node app (that's pure hypothesis, would need to change the running timezone to confirm)

mikroorm only - Retrieving an existing record - timezone configured to '+05:00' - ForceUtcTimezone: true

MySql TimeZone: +05:00 Nodejs env Timezone: CET	mikroorm Cx timezone: +05:00 ForceUtcTimezone: true
	DateTimeColumn	TimeStampColumn
As Displayed In MySql	2020-03-17 11:11:11	2020-03-17 11:11:11
Date.toString()	2020-03-17 12:11:11 GMT+0100	2020-03-17 08:11:11 GMT+0100
Date.toUTCString()	2020-03-17 11:11:11 GMT	2020-03-17 07:11:11 GMT

Inserting a record with the following date: new Date('2020-03-17T07:11:11')

MySql TimeZone: +05:00 Nodejs env Timezone: CET	mikroorm Cx timezone: +05:00		mariadb Cx timezone: +05:00		mysql Cx timezone: +05:00		typeorm Cx timezone: +05:00
	DateTimeColumn	TimeStampColumn	DateTimeColumn	TimeStampColumn	DateTimeColumn	TimeStampColumn	DateTimeColumn	TimeStampColumn
As Displayed In MySql	2020-03-17 07:11:11	2020-03-17 11:11:11	2020-03-17 11:11:11	2020-03-17 15:11:11	2020-03-17 11:11:11	2020-03-17 15:11:11	2020-03-17 11:11:11	2020-03-17 15:11:11
Date.toString()	2020-03-17 07:11:11 GMT+0100	2020-03-17 07:11:11 GMT+0100	2020-03-17 07:11:11 GMT+0100	2020-03-17 07:11:11 GMT+0100	2020-03-17 07:11:11 GMT+0100	2020-03-17 07:11:11 GMT+0100	2020-03-17 07:11:11 GMT+0100	2020-03-17 07:11:11 GMT+0100
Date.toUTCString()	2020-03-17 06:11:11 GMT	2020-03-17 06:11:11 GMT	2020-03-17 06:11:11 GMT	2020-03-17 06:11:11 GMT	2020-03-17 06:11:11 GMT	2020-03-17 06:11:11 GMT	2020-03-17 06:11:11 GMT	2020-03-17 06:11:11 GMT

Analysis

In that scenario, I insert a record in the database with respective libraries and use for both DATETIME and TIMESTAMP column the following value: new Date('2020-03-17T07:11:11').
The node application being in a GMT+1 timezone, and considering how javascript date work, I'm expecting the following results for both columns:

As Displayed In MySql	2020-03-17 11:11:11
Date.toString()	2020-03-17 07:11:11 GMT+0100
Date.toUTCString()	2020-03-17 06:11:11 GMT

mariadb, mysql and typeorm

Once again, those libs have the same results.
The DATETIME column provides the expected results as far as I am concerned.
The TIMESTAMP column gives wrong results.

mikroorm

Has it's own approach here.
Again as opposed to the other libraries, TIMESTAMP provides the expected values here.
On the other hand DATETIME column provides wrong result. It looks like for DATETIME column, it will store the local time for the running node app.

Conclusion

I am by no mean an expert. Do not hesitate to comment, and correct me for whatever.
As far as I am concerned, I feel like all libraries tested have it wrong in a way or another but one may disagree and prove me wrong.

mikroorm

It has it right for TIMESTAMP column using connection driver timezone.
On the other hand I find their choice for DATETIME columns quite weird. Mikroorm considers DATETIME column value as local timezone date (local to the running application) unless you set forceUtcTimezone to true, in which case DATETIME column values will be considered utc dates. This is not consistent with the fact that CURRENT_TIMESTAMP as the default value of a DATETIME column will provide current MySql timezone date.

mariadb, mysql and typeorm

They got it rigt for the DATETIME column.
TIMESTAMP handling is completely wrong in my mind.
DATETIME column is stored as is by mysql. It is therefore a matter of convention. However, TIMESTAMP value is stored in UTC. Therefore there shouldn't be any amiguity regarding its value.

I would appreciate any comments, correction, whatever.

[dougwilson]

Thank you for your deep analysis. But there is some important information left out here to help us, for example, can you also show what the output this module provides in your testing when you set the dateStrings setting? Also what timezone setting did you give this module?

[FredericLatour]

Hi, The node application is running in a CET (GMT+1) timezone context it this is what you mean. This is indicated in my testing. If this is not what you mean, would you mind clarifying? Also I don't understand this:

can you also show what the output this module provides in your testing when you set the dateStrings setting?

That said, it doesn't change the fact that the TIMESTAMP column is necessarily wrong if DATETIME column is right.

[dougwilson]

I'm referring to the parameters documented at https://github.com/mysqljs/mysql/blob/master/Readme.md#connection-options .

The "timezone" option needs to be set to that of your mysql server, for example.

The question about dateStrings is because the Mysql protocol sends all dates over the wire in a single string format and the server is actually performing the conversation of UTC of the TIMESTAMP type into local time, so I just wanted to see what the raw values the server is sending back in your tests without this module trying to convert back into a javascript Date object.

[dougwilson]

The above questions are in order to help us troubleshoot and I suppose make changes to this module to work better, which I assume is the goal for opening this issue :)

[FredericLatour]

The connection timezone is configured to '+05:00' for all libraries. I though it was clear from my titles. Maybe I need to be more explicit. Which is in sync with the MySql timezone. I would not expect any coherent results otherwise.

[dougwilson]

Ah, sorry. It was hard to tell, as i was not sure if you were referring to the timezone on this module or on the server. If would probably help if you removed all the information not pertaining to this module, and perhaps showing the code being in use.

But anyway, thanks again for your testing! I would love to take a deeper look into this. Is it possible to share the javascript you did the testing with here? I would like to replicate your set up in order to debug though the issue. You're also welcome to make a pull request as well if you're unable to share the replication but have an idea of what needs to be change :) ! Many thanks!

[FredericLatour]

Regarding your other question, isn't the value as displayed in Mysql good enough? And by the way, mikroorm seems to handle it correctly though in their case, they are not handling DATETIME correctly (well at least they are making discutable choice on how to handle it).
Basically, the handling of TIMESTAMP from mikroorm and the handling of DATETIME from mysql lib would make the perfect combination.
However, if in the case of mikroorm we could call that a weird choice, in the case of mysql and other libs, the TIMESTAMP value is plain wrong.

[dougwilson]

I'm asking in order to get more information. Getting that other value will help understand what is returned over the protocol before this module does timezone conversion, which is what is in question here. Basically I'm just asking for more information to help get closer to solving the issue, since you have not provided detailed steps for me to replicate the tests.

I have never heard of mikroorm before now. If you can detail how they are doing it, we can definitely implement the same thing if that is what you're looking for (perhaps even make a pull request with their implementation).

[FredericLatour]

That's quite difficult to provide the source:

• I'm using this project for other testings as well
• it's quite big because I'm generating json + convert into html table and this for multiple libs...
• it's written in typescript (not fan of javascript without typescript)
• the table I used is an existing table with additional fields that don't really make sense for you.

Honestly, it's quite easy to setup some testing:

• a table with both a DATEITME and TIMESTAMP column
• You can set Mysql timezone using this command: SET time_zone='+05:00';
• a simple node app that can reads and insert a record - using a connection timezone that equals the one you define for MySql.

It will be very easy to see that TIMESTAMP column is completely messed up:

• the value returned from an existing record is wrong...
• the value inserted from some date is wrong.

[dougwilson]

I know you said it would be simple and that is what I did and I got all the result you wanted without any distortion that you are seeing... that is why I'm trying to get more infor from you, as I am not able to replicate what you are seeing even though I have tried.

If you're not able to provide any additional information I've asked for, and I tried to replicate without success, what would you suggest as the next step?

[FredericLatour]

Interesting. You tried to replicate and you got it right?

[FredericLatour]

I still don't understand what you are asking with the "DateString" stuff (maybe the language barrier). Would you mind trying to clarify even more (sorry for that)?

[FredericLatour]

Ok, I got it , you are talking of a Connection option. Let me try out..

[dougwilson]

Interesting. You tried to replicate and you got it right?

That's correct. And to be clear: I believe you are seeing what you claim. So I agree you are getting the wrong results, but the question is what is causing it (be it in this module or otherwise) so we can get it fixed :+1:

I still don't understand what you are asking with the "DateString" stuff (maybe the language barrier). Would you mind trying to clarify even more (sorry for that)?

So there is an option in this module called dateStrings (https://github.com/mysqljs/mysql/blob/master/Readme.md#connection-options) which will return the date times as the raw string ad sent over the mysql text protocol without this module performing any kind of timezone version on them to get them to be a javascript Date object. Basically it would be to do mysql.createConnection({ timezone: '+05:00', dateStrings: true }) and perform the same tests to see what is coming back over the protocol directly.

[FredericLatour]

Same configuration with dateString to true:

RowDataPacket {
  id: 3275944,
  created: '2020-03-18 04:43:06',
  modified: '2020-03-18 04:43:06',
  createdDt: '2020-03-17 11:11:11',
  createdTs: '2020-03-17 07:11:11' }

Now I tried changing both Connection and Mysql timezone and it did not affect the result.

So the for the datetime I get the value stored in Mysql but for the timestamp, I do not get the UTC value. Are those values raw data returned by MySql server?

[dougwilson]

but for the timestamp, I do not get the UTC value.

Right, because mysql server itself will convert the timestamp column to the server's timezone on retrieval from storage. The utc is only a nature of the storage mechanism, but the server will still do timezone conversation on timestamp columns.

Are those values raw data returned by MySql server?

That's correct. If you used something like Wireshark you would see those are the values sent over the wire from the server. The server only provides the date and time that that exact format you see with no time zone data.

[FredericLatour]

When you say :

mysql server itself will convert the timestamp column to the server's timezone on retrieval from storage.

Do you mean Mysql timezone or the timezone of the machine/vm/container holding the Mysql server?

[dougwilson]

Do you mean Mysql timezone or the timezone of the machine/vm/container holding the Mysql server?

I'm not 100% sure one. From the MySQL server documentation https://dev.mysql.com/doc/refman/8.0/en/datetime.html :

MySQL converts TIMESTAMP values from the current time zone to UTC for storage, and back from UTC to the current time zone for retrieval. (This does not occur for other types such as DATETIME.) By default, the current time zone for each connection is the server's time. The time zone can be set on a per-connection basis. As long as the time zone setting remains constant, you get back the same value you store. If you store a TIMESTAMP value, and then change the time zone and retrieve the value, the retrieved value is different from the value you stored. This occurs because the same time zone was not used for conversion in both directions. The current time zone is available as the value of the time_zone system variable.

[FredericLatour]

From what I get, the value is not converted using Mysql Timezone. If it was the case, the value should be 2020-03-17 11:11:11 (which the value that is displayed in mysql) and not 2020-03-17 07:11:11.

I'm going to make some tests by changing :

• Global Mysql timezone first and see the results
• Server hosting mysql timezone And will post the results.

I'm wondering how mikroorm gets it right for TIMESTAMP. Maybe just a lucky combination of circumstances. I'll have to dig further ...

[dougwilson]

I'm wondering how mikroorm gets it right for TIMESTAMP. Maybe just a lucky combination of circumstances. I'll have to dig further ...

Yea, I'm curious on the same thing. It's the only one that is different as well, so perhaps the circumstances just make it appear that the timestamp works right on accident, not sure without understanding what that module is doing in that case.

Over the protocol itself, the columns have no difference and they are all transferred in the same format of YYYY-MM-DD HH:mm:ss without any time zone data.

[FredericLatour]

I've got something here. It looks like if need to set either Mysql Global timezone (which is set to SYSTEM by default) to the desired timezone ('+5' in my testing) or alternatively set the server/vm/container timezone instead (and leave MySql Global timezone to SYSTEM).

With that configuration, I get the expected result.

RowDataPacket {
  id: 3275944,
  created: '2020-03-18 04:43:06',
  modified: '2020-03-18 04:43:06',
  createdDt: '2020-03-17 11:11:11',
  createdTs: '2020-03-17 11:11:11' }

Which sounds like the expected result.

It was misled because I was changing session time zone only within my query editor and had coherent results. Maybe it could have worked by issuing the following command just after creating a connection :

• SET time_zone='+05:00';

Anyway, it looks like I was wrong. I will re-run the tests with this configuration change.

[FredericLatour]

New results

I removed Typeorm that seems to be relying entirely on Mysql driver.

Everything seems to be working as expected now except for mikroorm that seems to be completely wrong. I'll see with the maintainer of the library. Those timezone configuration matters can easily get very tricky.

Retrieving an existing record - Connection timezone configured to '+05:00'

MySql TimeZone: +05:00 Nodejs env Timezone: +05	mikroorm Cx timezone: +05:00		mariadb Cx timezone: +05:00		mysql Cx timezone: +05:00
	DateTimeColumn	TimeStampColumn	DateTimeColumn	TimeStampColumn	DateTimeColumn	TimeStampColumn
As Displayed In MySql	2020-03-17 11:11:11	2020-03-17 11:11:11	2020-03-17 11:11:11	2020-03-17 11:11:11	2020-03-17 11:11:11	2020-03-17 11:11:11
Date.toString()	2020-03-17 11:11:11 GMT+0100	2020-03-17 11:11:11 GMT+0100	2020-03-17 07:11:11 GMT+0100	2020-03-17 07:11:11 GMT+0100	2020-03-17 07:11:11 GMT+0100	2020-03-17 07:11:11 GMT+0100
Date.toUTCString()	2020-03-17 10:11:11 GMT	2020-03-17 10:11:11 GMT	2020-03-17 06:11:11 GMT	2020-03-17 06:11:11 GMT	2020-03-17 06:11:11 GMT	2020-03-17 06:11:11 GMT

Inserting a record with the following date: new Date('2020-03-17T07:11:11')

MySql TimeZone: +05:00 Nodejs env Timezone: +05	mikroorm Cx timezone: +05:00		mariadb Cx timezone: +05:00		mysql Cx timezone: +05:00
	DateTimeColumn	TimeStampColumn	DateTimeColumn	TimeStampColumn	DateTimeColumn	TimeStampColumn
As Displayed In MySql	2020-03-17 07:11:11	2020-03-17 07:11:11	2020-03-17 11:11:11	2020-03-17 11:11:11	2020-03-17 11:11:11	2020-03-17 11:11:11
Date.toString()	2020-03-17 07:11:11 GMT+0100	2020-03-17 07:11:11 GMT+0100	2020-03-17 07:11:11 GMT+0100	2020-03-17 07:11:11 GMT+0100	2020-03-17 07:11:11 GMT+0100	2020-03-17 07:11:11 GMT+0100
Date.toUTCString()	2020-03-17 06:11:11 GMT	2020-03-17 06:11:11 GMT	2020-03-17 06:11:11 GMT	2020-03-17 06:11:11 GMT	2020-03-17 06:11:11 GMT	2020-03-17 06:11:11 GMT