Different timestamp when querying with node-postgres and raw sql

Question

My postgres database is running in docker. I started nestjs server from host terminal. The database is created in another microservice with prisma.

My time zone is UTC+5. I am running query below with node-postgres:

INSERT INTO "Chat" default values RETURNING *

The inserted data is saved -10h instead of -5h.

Table definition:

CREATE TABLE "Chat" (
    "id" UUID NOT NULL DEFAULT gen_random_uuid(),
    "created_at" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,

    CONSTRAINT "Chat_pkey" PRIMARY KEY ("id")
);

What I am expecting is node-postgres should insert row with MY_TIME_ZONE - 5h, not MY_TIME_ZONE - 10h.

MOREOVER:

select current_timestamp; 
-- this returns:  2023-11-12 15:18:37.322506+00
-- while my time: 2023-11-12 20:18:37.322506+00

Answer 1

You can manually set up the timezone you're currently based on, in both postgresql as well as docker to synchronize it -

SET timezone='UTC+5';

for postgresql, and for docker you can check the current time zone setting in your Docker container by executing -

docker exec -it <container_name_or_id> date

If the time zone is incorrect, you may need to adjust it in your Dockerfile or docker-compose.yml. For example, you can set the environment variable TZ -

environment:
  - TZ=Asia/Kolkata

and, for the node.js application to know what's going on, use the process.env.TZ variable at the beginning of your script.

process.env.TZ = 'UTC+5';

having them all syncronized together shouldn't allow any room for errors.