How to fix DateTime- and TimeZone- issues between PostgreSQL and a NodeJS/NestJS application?

Question

I have a Postgres database and a nodeJS application (NestJS framework, for the sake of completeness). And I need help with timezone issues.

This is my table storing blog posts:

create table if not exists public.posts
(
    id      serial constraint "PK_40808690ebab9150a6558c0f82c" primary key,
    created_at      timestamp default now() not null,
    last_updated_at timestamp default now(),
    title   varchar(256),
    pub_date  timestamp,
    ...
 )

First, I want to query the latest date of all posts:

select max(pub_date)::date from posts;


-> 2023-11-05

Then I want to query all blog posts for that particular date :

Inside Postgres CLI, the following query

select * from daily where pub_date::date = '2023-11-05'::date;

will return all posts that have been published on 2023-11-05:

However, when I perform the queries from my NodeJS-based backend, utilizing the return value from the first Query, passing it as an argument to the second query, it will not work as expected.

Passing the result from the first query inside the backend will return a different date from the previous day:

const sql = 'select max(pub_date)::date from posts';
const d = await this.repository.query(sql);
console.log('D', d);

This will receive a date showing the day before the expected date (or exact 1 hour and 23 min before the expected date) :

D [ { max: 2023-11-04T23:00:00.000Z } ]

When I use this value to perform the other query from above to retrieve all posts from that specific date, it will not work as it is querying posts from the day before 2023-11-05 = 2023-11-04

I assume it has something to do with some weird TimeZone magic.

Using PostgreSQL CLI (or any database tool like pgAdmin) will return the right values. My nodeJS backend will return the wrong date. I use TypeORM with the postgresql-extension.

How can I fix that?

Answer 1

Check and correct your client set timezone='utc'; and/or use at time zone to explicitly state where those dates are supposed to match before downcasting to date. Also, see if you can merge the two queries, saving yourself a round-trip: demo

select current_setting('timezone') as client_timezone,* 
from daily 
where             (pub_date at time zone 'utc')::date
     = (select max(pub_date at time zone 'utc')::date from posts);