Compatible timezone value (and missing docs)

[glensc]

Coming from mysql to mysql2 has learning curve, how to set timezone?

Currently, there's zero documentation about a such option, but using it, the library prints a warning:

Ignoring invalid timezone passed to Connection: UTC. This is currently a warning, but in future versions of MySQL2, an error will be thrown if you pass an invalid configuration option to a Connection

from searching the source code I found that equivalent would be Z.

perhaps add UTC to the list as well (to be identical to Z)?

[sidorares]

perhaps add UTC to the list as well (to be identical to Z)?

probably. What's mysqljs/mysql behavior with UTC?

[glensc]

Their docs:

timezone: The timezone configured on the MySQL server. This is used to type cast server date/time values to JavaScript Date object and vice versa. This can be 'local', 'Z', or an offset in the form +HH:MM or -HH:MM. (Default: 'local')

• https://github.com/mysqljs/mysql#connection-options

however, browsing the source, I can't figure out what they do with it other than converting javascript date objects. I was expecting its value being sent out as set @@time_zone=options.timezone

from my practice UTC just worked with mysql.

[sidorares]

I haven't put much thought in that, I think currently the intention is to handle time zone conversions client side ( mysql DATETIME response does not include any "original" time zone information ). set @@time_zone is also a possible approach.

Do you know what other clients do @glensc ? Maybe do the other way around and fetch variables at the beginning including time_zone and also track any change to it like we track charset changes

[glensc]

handling only on client side seems to be the case, as I could not find code that would send timezone to server.

so, the issue here is to add UTC as a valid value (to be the same as Z) to deal with DateTime objects conversion to javascript Date objects.

[sidorares]

the more I think about it the more I like "read time_zone variable ( and track updates to it ) and apply the value to returned DATETIME results"

On the other hand just applying timezone from client config is a better solution for "I know what TZ in my data should be, and its unrelated to server or client current time zone"

We could do both unless this is causing confusion. Not set in the config = use value from time_zone system variable, if set override that.

[haroldiedema]

@sidorares Our webserver runs on the "Europe/Amsterdam" timezone while our MySQL server is set to UTC. I can't figure out how to configure mysql2 in my node application to apply the correct timezone. Even the following query fails:

UNIX_TIMESTAMP(scheduled_at) <= UNIX_TIMESTAMP()

scheduled_at is a DATETIME column. The data has been inserted by mysql2 in the same application. I have to manually add an offset to make this work as it should like so, to account for the 2 hour difference:

UNIX_TIMESTAMP(scheduled_at) <= (UNIX_TIMESTAMP() + 7200)

This isn't a viable solution however, nor is setting the timezone configuration to +02:00 since this doesn't take Daylight Savings Time (DST) into account.

[sidorares]

UNIX_TIMESTAMP(scheduled_at) <= UNIX_TIMESTAMP()

this should work identical in any client.

What I expect the query to do: apply current session timezone to scheduled_at, and calculate unix timestamp; then compare to "now" timestamp
Not 10% sure about session timezone though, might be ignored and server system TZ is used

[haroldiedema]

Could it be then that when inserting a new record and passing new Date() to the scheduled_at column applies the local timezone (since this is what was configured), meaning the value in the database is actually stored based on the session timezone, or the timezone of the machine that executed the query? That would explain the offset.

I just can't figure out how to work around this issue with queries like these.

[sidorares]

Could you check what is actually stored in the server? Set session timezone to utc and select scheduled_at

[haroldiedema]

Here is one example:

SELECT scheduled_at, UNIX_TIMESTAMP(scheduled_at)  FROM task ORDER BY id DESC LIMIT 1;

Which outputs:
2023-04-12 09:42:26, 1681292546

[haroldiedema]

It seems that the date has been stored in UTC format, while the first column is actually already in the correct timezone (Europe/Amsterdam). The internally stored timestamp however, is 2 hours ahead.

[sidorares]

whats "date" and "internally stored timestamp"? 1681292546 is the same as 2023-04-12 09:42:26 UTC

[haroldiedema]

The problem is that the time 09:42:26 is correct if the timezone would be set to Europe/Amsterdam. But the it is in UTC format. This means that when I select this from the server, it converts to 11:42:26 instead.

I would expect this to show 07:42:26 instead.

If I insert a record now using new Date() and then select the row like so: UNIX_TIMESTAMP(scheduled_at) <= UNIX_TIMESTAMP(), the record will show up in 2 hours from now. This is not how its supposed to work.

[sidorares]

Whats the schema type you have for scheduled_at?