Timezone and timestamps

[hoylen]

SQLjocky5 needs to handle timezones correctly when the SQL TIMESTAMP datatype maps to and from Dart DateTime objects.

Currently (in SQLjocky5 v1.0.0) incorrect DateTime values can be selected or written to the database, if the MySQL server is running with a different timezone than the Dart client program. It only works correctly if both have the same timezone, but this is not always possible or guaranteed (e.g. the remote MySQL database might be in a different geographical location, or the timezone is accidentally/deliberately changed).

Cause

When performing queries, the SQL timestamp datatype is mapped into Dart local DateTime objects (i.e. ones in the "local" timezone).

When a session is established to the database, it initially has the session timezone set to "SYSTEM", which means the timestamp values are interpreted to be in the global timezone of the MySQL server. However, it could be set to any timezone depending on: how the server host has been set up; how MySQL has been configured; and it can be changed by other clients without the Dart client knowing about it. So without explicit action by the client, it is not safe for the Dart client to assume the session timezone is the same as the "local" timezone used by the Dart DateTime class.

For example, consider a database contained a timestamp value representing 2017-11-20 17:00 UTC (5pm in the UK), with the database located in Australia and the client located in California. The MySQL global timezone is set to UTC+10:00, so the timestamp value in the SYSTEM timezone is 2017-11-21 03:00 (3am the next day in Australia). The Dart client program runs with a timezone of UTC-8:00, so a query will return a local DateTime object with the value of 2017-11-21 03:00 (where local is UTC-8:00). That is wrong because the correct value should be a local DateTime object containing 2017-11-20 09:00 (9am in California is the same as 5pm in the UK).

There is also the reverse problem when inserting or updating timestamp values in the database. The Dart program uses DateTime values in its local timezone, but the database session interprets those numbers in the database's system timezone. The wrong timestamp will be written if the two timezones are not the same.

Also, it is possible to pass in UTC DateTime objects into the SQLJocky5 methods. It simply uses the numbers from the object, treating it as the same as a local DateTime object -- ignoring the fact that it represents a different moment in time (unless the local timezone happens to be the same as UTC). The wrong value is written to the database.

Suggested solution

The simplest solution is to set the database connection's session timezone to the same timezone as the "local" timezone used by DateTime. (Obviously, tampering with the database's global timezone is not a good idea.)

This can be done by running the MySQL command SET time_zone='...' before running queries involving timestamps, using an offset value obtained from converting new DateTime.now().timeZoneOffset into +/-HH:MM format.

This should be implemented by the SQLjocky5 package when a connection is created. Firstly, because it might not get done, if users are expected to issue the command in the code they write. This can be a trap for users, because their code might seem to work in testing (because the test database is in the same timezone), but is wrong in production (when timezones may change sometime in the future). Secondly, because when connection pools are created, it is very difficult (impossible?) for the user's code to ensure the command has been issued to every connection in the pool -- only the SQLjocky5 package is in a position to ensure it is done properly.

Also, the SQLjocky5 package should detect if a UTC DateTime object has been passed into queries and handle them properly: converting them to a local DateTime object before using its numbers.

[hoylen]

Since a connection pool may automatically create new connections (e.g. the server has timed them out or they have become disconnected), the session timezone setting must be done in sqljocky5. The application doesn't know when connections get created.

The only way for an application to reliably depend on the timezone being correct, is to use a RetainedConnection or transaction, and to explicitly set the timezone themselves every time it gets one. But that would be inefficient.

[hoylen]

Connection pooling has been removed from sqljocky5 release 2.2.1, so this issue no longer applies to it. But it still applies if a future release will provide a new implementation of connection pooling.

When using sqljocky5 2.2.1, the programmer has to implement their own connect pooling, and therefore they can explicitly set the timezone themselves (when they open a new connection).

I had to implement connection pooling for my application and simply run SET time_zone=? after opening each connection. When the connection is reused for another transaction, it doesn't have to be done again. The offset used is the Duration obtained from new DateTime.now().timeZoneOffset, converted into the "+HH:MM" format that MySQL/MariaDB requires.

A note for anyone implementing this sort of thing: there are two edge cases that should be tested. Firstly, when the operating system's timezone is changed while the program is running. Secondly, when the operation system's timezone has daylight saving, and the local time changes while the program is running.