Parsing Date from database to Js Date result in an off date by one day (debanding on local time)!

[abdelrazzaq-dev]

The bug is when we parse a date from a Postgres column like 2023-06-01 the result date in javascript will be something like 2003-04-30T22:00:00.000Z, now we are in a different month.

I investigated the issue a little and it's related to @databases/db, the ConnectionPoolConfig has a timeZone property and I think it defaults to local so the date gets parsed thinking it's a local date, though the data it's a copy from the real data and it's in UTC format.

I think we need to add the timeZone property to PlatformaticDB config file!

Issue reproduction https://github.com/abdelrazzaq-dev/date-parse-bug

After a second verification, I only see the timeZone in MySql!

[mcollina]

A few things:

1. we should set that timezone value to UTC anyway (instead of local)
2. we should make that configurable

Would you be able to send a PR?

[AbhilakshSinghReen]

@mcollina I can make a PR for this.

Please note that as mentioned by @abdelrazzaq-dev, timeZone is only available in @databases/mysql and not in @databases/pg.

We can default the timeZone to 'utc' and can also make it configurable. @databases/mysql offers to set timeZone for both the client and the DB server. Should the PlatformaticDB configuration also have the option to set client and DB server time zones separately? Let me know and I'll go ahead and open a PR.

[mcollina]

Take a look at this:

platformatic/packages/sql-mapper/test/helper.js

Lines 3 to 5 in 690d165

	// Needed to work with dates & postgresql
	// See https://node-postgres.com/features/types/
	process.env.TZ = 'UTC'

[AbhilakshSinghReen]

@mcollina thank you for pointing me to that. It fixes the issue.

However, this only works when process.env.TZ is set to UTC. The bug still occurs even if we set both the database's timezone and the platformatic application's timezone to the same value which is not UTC.
For example, setting the database timezone to +05:30 in PgAdmin and also setting process.env.TZ to the corresponding value of Asia/Kolkata still creates the issue.

So, please let me know if I should make it configurable or just default it to UTC?

[abdelrazzaq-dev]

don't you think this should be fixed @databases/sql? and make the TZ also configurable for pg?

…

On Tue, Sep 12, 2023 at 4:19 PM AbhilakshSinghReen ***@***.***> wrote: @mcollina <https://github.com/mcollina> thank you for pointing me to that. It fixes the issue. However, this only works when process.env.TZ is set to UTC. The bug still occurs even if we set both the database's timezone and the platformatic application's timezone to the same value which is not UTC. For example, setting the database timezone to +05:30 in PgAdmin and also setting process.env.TZ to the corresponding value of Asia/Kolkata still creates the issue. So, please let me know if I should make it configurable or just default it to UTC? — Reply to this email directly, view it on GitHub <#1451 (comment)>, or unsubscribe <https://github.com/notifications/unsubscribe-auth/AFRS4BZYZH2GUUATODC6VW3X2BVQNANCNFSM6AAAAAA4FAOBUA> . You are receiving this because you were mentioned.Message ID: ***@***.***>

[AbhilakshSinghReen]

@abdelrazzaq-dev you're right, I've created an issue on the @databases GitHub repo.

The way the @databases/sql package handles this is that on setting timeZone to utc, it constructs JS dates in UTC using Date.UTC().

However, there is a workaround that is already being used in platformatic. As pointed out above, we can set process.env.TZ = "UTC" and this effectively changes the timezone of the entire node application, so that all dates constructed are in UTC only.

But still, I agree, that the issue is from @databases.