[CONJS-62] Support named timezones and daylight savings time Created: 2019-03-18  Updated: 2020-08-25  Resolved: 2019-04-25

Status: Closed
Project: MariaDB Connector/node.js
Component/s: other
Affects Version/s: 2.0.3-GA
Fix Version/s: 2.0.4

Type: Task Priority: Major
Reporter: Geoff Montee (Inactive) Assignee: Diego Dupin
Resolution: Fixed Votes: 0
Labels: None

Issue Links:
Problem/Incident
is caused by MDEV-19243 Fix timezone handling on Windows to r... Closed

 Description   

MariaDB Connector/node.JS only appears to have very limited timezone support at the moment:

timezone
Forces use of the indicated timezone, rather than the current Node.js timezone. Possible values are Z for UTC, local or ±HH:MM format

https://mariadb.com/kb/en/library/nodejs-connection-options/#other-options

https://github.com/MariaDB/mariadb-connector-nodejs/blob/0b2a9296f3b555baefe1fc631ae4fbb772bff8b5/lib/config/connection-options.js#L78

The connector should also support named time zones, just as the server does. Otherwise, the connector can't handle daylight savings time.

https://mariadb.com/kb/en/library/time-zones/#setting-the-time-zone

It looks like time zones might currently be implemented this way because the connector tries to do a simplistic form of time zone conversions on its own:

https://github.com/MariaDB/mariadb-connector-nodejs/blob/0b2a9296f3b555baefe1fc631ae4fbb772bff8b5/lib/cmd/common-text-cmd.js#L388

But why doesn't the connector just do SET SESSION time_zone = "..." and let the server handle the time zone conversions?

 Comments   
Comment by Diego Dupin [ 2019-04-25 ]

Solution is not as simple as one can think :
Issue is when Node.js current timezone differ to server timezone, causing timestamp shift.

Best solution would be to automatically adjust driver to server timezone.
Problem is that server default behaviour rely on operating system.
For Linux that means posix / Olson time. For windows, its own implementation called Microsoft Time Zone Database.
So different standard, and driver cannot even rely on what server send on system_time_zone : on windows, results are localized 'Romance Standard Time' on a french installation will result in 'Paris, Madrid (heure d’été' (not even talking of mysql implementation that doesn't even parse as utf8 => 'Paris, Madrid (heure d??t')

Another solution would be as suggest to set SESSION time_zone, but that means filling time zone tables to be sure of results, and maintaining those data afterwhile.

MDEV-19243 is created to unify implementation server side.

Timezone standard exists, named IANA (aka Olson timezone database) with another standard for[ short time zone IDs|http://unicode.org/repos/cldr/trunk/common/bcp47/timezone.xml] (aka bcp47)

Best solution for now, that will be in next release is changing javascript date timezone client side, relying on moment-timezone.js that support IANA time zones.

commit : https://github.com/MariaDB/mariadb-connector-nodejs/commit/d24c8ee99ac1ec380f650a0b304646587b4e2e20

Generated at Thu Feb 08 03:22:28 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.