Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Dates and Timestamps retrieved from Postgres are offset incorrectly based on the local time zone #304

Open
AbhilakshSinghReen opened this issue Sep 11, 2023 · 0 comments

Comments

@AbhilakshSinghReen
Copy link

AbhilakshSinghReen commented Sep 11, 2023

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.

  1. This issue also occurs in the @databases/mysql package but it can be easily fixed by passing timeZone 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.
  2. 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.

@AbhilakshSinghReen AbhilakshSinghReen changed the title Dates and Timestamps retrieved from Postgres are offset based on the local time zone Dates and Timestamps retrieved from Postgres are offset incorrectly based on the local time zone Sep 11, 2023
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Labels
None yet
Projects
None yet
Development

No branches or pull requests

1 participant