Dates and Timestamps retrieved from Postgres are offset incorrectly based on the local time zone
AbhilakshSinghReen opened this issue · 0 comments
In the @databases/pg
package, dates and timestamps returned on querying the Postgres DB are not the same as what is stored in the DB. Instead, they are offset by the negative of the local timezone.
- This issue also occurs in the
@databases/mysql
package but it can be easily fixed by passingtimeZone
in the config while creating a connection pool. However, after looking at the code, I can't find a similar timezone configuration in@databases/pg
. - This issue does not occur if we use the
TIMESTAMP WITH TIME ZONE
datatype.
Steps to reproduce
I've created a Git Repo that can be used for easy reproduction of this issue.
git clone https://github.com/AbhilakshSinghReen/atdatabases-timezone-bug/tree/postgres-timezone-bug
Go to the repo directory
npm install
node src\index.js
Expected Behavior
The dates and timestamps returned on running a SELECT
query should be the same as those displayed in PgAdmin.
Actual Behavior
The dates and timestamps returned on running a SELECT
query are offset by a certain amount depending on the local timezone.
@ForbesLindesay, if you could point me to the file where a change is required, I might be able to submit a PR to fix this issue.
Credit to @abdelrazzaq-dev for initially finding out about this issue.