Uploaded image for project: 'MariaDB Connector/node.js'
  1. MariaDB Connector/node.js
  2. CONJS-154

Timezone support correction and clarification

    XMLWordPrintable

    Details

    • Type: Bug
    • Status: Closed (View Workflow)
    • Priority: Major
    • Resolution: Fixed
    • Affects Version/s: None
    • Fix Version/s: 2.5.2
    • Component/s: other
    • Labels:
      None

      Description

      Actual timezone support has 2 issues,

      • use of moment.setDefault that can be changed by the environment.
      • when using timezone, connection default timezone is not set, possibly causing issue with function sensible to timezone, like NOW()

      Additionally, documentation is missing a detailed description.

      Here is the documentation change proposal:

      Timezone consideration

      Client and database can have a different timezone.

      The connector has different solutions when this is the case.
      the `timezone` option can have the following value:

      • 'local' (default) : connector doesn't do any conversion. If the database has a different timezone, there will be an offset issue.
      • 'auto' : connector retrieve server timezone. Dates will be converted if server timezone differs from client
      • IANA timezone / offset, example 'America/New_York' or '+06:00'.

      IANA timezone / offset

      When using IANA timezone, the connector will set the connection timezone to the timezone.
      this can throw an error on connection if timezone is unknown by the server (see mariadb timezone documentation, timezone table might be not initialized)
      If you are sure the server is using that timezone, this step can be skipped with the option `skipSetTimezone`.

      If timezone correspond to javascript default timezone, then no conversion will be done

      Recommendation.

      The best is to have the same timezone on client and database, then keep the 'local' default value.

      If different, then either client or server has to convert date.
      In general, that is best to use client conversion, to avoid putting any unneeded stress on the database.
      timezone has to be set to the IANA timezone corresponding to server timezone and disabled `skipSetTimezone` option since you are sure that the server has the corresponding timezone.

      example: client use 'America/New_York' by default, and server 'America/Los_Angeles'.
      execute 'SELECT @@system_time_zone' on the server. that will give the server default timezone.
      the server can return POSIX timezone like 'PDT' (Pacific Daylight Time).
      IANA correspondence must be found : (see IANA timezone List) and configure client-side.
      This will ensure DST (automatic date saving time change will be handled)

        Attachments

          Activity

            People

            Assignee:
            diego dupin Diego Dupin
            Reporter:
            diego dupin Diego Dupin
            Votes:
            0 Vote for this issue
            Watchers:
            1 Start watching this issue

              Dates

              Created:
              Updated:
              Resolved: