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
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.