Timezone config not being respected.

[belizariogr]

Using the mariadb connector, if i query:

SELECT NOW();

It will be returning the time with timezone from the system. Even though I explicit set the timezone to UTC.

import mariadb from 'mariadb';
import Config from '../../config.js';

const connectionOptions ={
            host: Config.databaseHost || '127.0.0.1',
            port: Config.databasePort || '3306',
            user: Config.databaseUser || 'root',
            password: Config.databasePass || '',
            database: Config.databaseName,
            //dateStrings: true,
            connectionLimit: Config.databaseConnectionLimit || 250,
            multipleStatements: true,
            insertIdAsNumber: true,
            bigIntAsNumber: true,
            decimalAsNumber: true,
            timezone: 'Z'
        };
const pool = mariadb.createPool(connectionOptions);

Using the old mysql connector, everything works fine.

[rusher]

This is tested either using a single connection or pool. Testing it, this works ok.
Could you tell the version of the connector you use?

[belizariogr]

Im using 3.4.0. Just to let you know... Im testing on Windows, with system timezone = -03:00 and MariaDB 11.3, and mariadb timezone set to SYSTEM.

On the old connector... If I change the timezone on the connector config, and query, the result Date will be returned in the timezone specified. Using mariadb connector, it will always return the same result.

Its suposed to return like the example down below:

Example:

Timezone = Z (UTC)
Stored = 2024-11-5T00:00:00
Returned = 2024-11-5T00:00:00

Timezone = -03:00
Stored = 2024-11-5T00:00:00
Returned = 2024-11-4T21:00:00

[Orel-A]

I suspect you are using the toISOString() function.
With this function the date will always print with UTC timezone.
Also check the output of getTimezoneOffset() function

[belizariogr]

There is no point in set timezone on the connection if it will always return the same value. As I said before, using the old connection it works as it should.

[Orel-A]

Check with mysql2 instead. What you get?

[belizariogr]

The real problem is... I have 2 servers: One in Brazil and another one in US. The US server is configured to UTC timezone at system level and the braziliam one -3:00. Im trying to replicate data between those two servers.

The problem is.. When i retrive the date fields on the US server, it will give me using UTC timezone. When I try to retrive the same record on the braziliam server, the backends gets a different result.

Using the old connector, I can get the same result on both servers... Using the mariadb connector, I got different results.

[belizariogr]

Wich means that the timezone option on the connector is doing nothing.

[Orel-A]

Do you have access to PhpMyAdmin to see how the dates are stored?

[belizariogr]

Yes... as I said before.. the dates are the same on both servers.. The problem is not how it is stored. If you change the timezone to another one like +12:00, the result still the same.

[belizariogr]

Just tested... using mysql2... works like charm. Also, using the old mysql works as well.

[Orel-A]

The timezone isn't documented that well: https://github.com/mariadb-corporation/mariadb-connector-nodejs/blob/master/documentation/promise-api.md#timezone-consideration
Maybe try to set timezone to Etc/UTC
Or use skipSetTimezone: true

[belizariogr]

Just tested ans same results using :

skipSetTimezone: true

Just to clarify:

Running the code with System Timezone (-03:00) and Connector Timezone ("Z"):

SELECT NOW();

mysql: 2024-11-14T16:27:13.000Z
mysql2: 2024-11-14T16:27:13.000Z
mariadb: 2024-11-14T19:27:13.000Z

System Timezone (-03:00) and Connector Timezone ("+06:00"):

mysql: 2024-11-14T10:35:09.000Z
mysql2: 2024-11-14T10:35:37.000Z
mariadb: 2024-11-15T01:36:19.000Z

[Orel-A]

The Node.JS application and the Database service are running on the same server?
Also try the Etc/UTC

[belizariogr]

Yes, they are running in the same server.

[Orel-A]

@rusher do you have any idea?

[belizariogr]

No effect using "Etc/UTC"

[belizariogr]

Looking at the implementation of mysql2 of readDateTime:

readDateTime(timezone) {
    if (!timezone || timezone === 'Z' || timezone === 'local') {
      const length = this.readInt8();
      if (length === 0xfb) {
        return null;
      }
      let y = 0;
      let m = 0;
      let d = 0;
      let H = 0;
      let M = 0;
      let S = 0;
      let ms = 0;
      if (length > 3) {
        y = this.readInt16();
        m = this.readInt8();
        d = this.readInt8();
      }
      if (length > 6) {
        H = this.readInt8();
        M = this.readInt8();
        S = this.readInt8();
      }
      if (length > 10) {
        ms = this.readInt32() / 1000;
      }
      // NO_ZERO_DATE mode and NO_ZERO_IN_DATE mode are part of the strict
      // default SQL mode used by MySQL 8.0. This means that non-standard
      // dates like '0000-00-00' become NULL. For older versions and other
      // possible MySQL flavours we still need to account for the
      // non-standard behaviour.
      if (y + m + d + H + M + S + ms === 0) {
        return INVALID_DATE;
      }
      if (timezone === 'Z') {
        return new Date(Date.UTC(y, m - 1, d, H, M, S, ms));
      }
      return new Date(y, m - 1, d, H, M, S, ms);
    }
    let str = this.readDateTimeString(6, 'T');
    if (str.length === 10) {
      str += 'T00:00:00';
    }
    return new Date(str + timezone);
  }

they are considering the timezone to display the value.

The mariadb implementation is alot simpler:

readDateTime() {
    const len = this.buf[this.pos++];
    if (len === 0xfb) return null;
    this.pos += len;
    const str = this.buf.toString('ascii', this.pos - len, this.pos);
    if (str.startsWith('0000-00-00 00:00:00')) return null;
    return new Date(str);
  }

[belizariogr]

The problem that I see is... because my server has the system timezone -03:00, the MariaDB connector is assuming that it always will be UTC.

[belizariogr]

The problem lies on the code:

new Date(str);

If you just create a new Date without considering the system timezone, it will generate a wrong result.

[belizariogr]

I'll make the changes and push a PR to fix this.

[belizariogr]

Change the function in:

lib/io/packet.js

  readDateTime() {
    const len = this.buf[this.pos++];
    if (len === 0xfb) return null;
    this.pos += len;
    const str = this.buf.toString('ascii', this.pos - len, this.pos);
    if (str.startsWith('0000-00-00 00:00:00')) return null;
    const date = new Date(str);
    date.setMinutes(date.getMinutes() - date.getTimezoneOffset())
    return date;
  }

this must fix the issue.

[Orel-A]

I've found this following information:
The local timezone is not stored in the date object, but is determined by the host environment (user's device).
The getTimezoneOffset() method returns the difference between UTC and the local time in minutes. Note that the timezone offset does not only depend on the current timezone, but also on the time represented by the Date object, because of daylight saving time and historical changes. In essence, the timezone offset is the offset from UTC time, at the time represented by the Date object and at the location of the host environment.
Keep in mind that +02:00 is an offset. Europe/Berlin is a timezone (that has two offsets).

[belizariogr]

Thats exactly the problem... It should not store and retrieve dates using local timezones... as I showed you, in the old connector they do convert the dates from local to UTC before retrive and store dates. In that way, it will always store and retrive it correctly.

[Orel-A]

I'm not the author of this library, but for me I solved the problem like this:
Instead of using the toISOString() function I use the daysjs library ike this dayjs(date).format('YYYY-MM-DD HH:mm:ss')

So in conclusion it doesn't matter what Node.js think the UTC date is but to show the correct time. Remember that the toISOString() function will subtract the timezone offset.

In MySQL the datetime is stored as UTC. But the timezone variable SELECT @@session.time_zone; will affect the output.

[belizariogr]

I believe that the problem has nothing to do about the mariadb itself. In javascript, when you create a new Date, it will always be using the local system timezone. Thats the real problem. So, using toISOString() it will be returning in a universal format, independent of the system timezone, to create the Date object in JS.