i have two environments as below
pre_prod - ubuntu , postgres:12.18
prod - ubuntu, postgres:12.18
I am facing very peculiar issue in both environments.
in pre_prod.
my timestamptz value is `
2024-03-27 07:00:00.000 +0530
which has its timestamp absolute value with utc timezone.
In prod I do have same value…
2024-03-27 07:00:00.000 +0530
But while rendering in Java, I see two different behavior's ..one is converting into IST, and another one is not converting into IST timezone..
----------------------------------------------------
pre_prod | prod
----------------------------------------------------
2024-03-27 07:00:00.000 | 2024-03-27 12:30:00.000
----------------------------------------------------
as per below postgresql documentation
For timestamp with time zone, the internally stored value is always in UTC (Universal Coordinated Time, traditionally known as Greenwich Mean Time, GMT). An input value that has an explicit time zone specified is converted to UTC using the appropriate offset for that time zone. If no time zone is stated in the input string, then it is assumed to be in the time zone indicated by the system's TimeZone parameter, and is converted to UTC using the offset for the timezone zone.
i am seeing that, postgres has not converted my localtime stamp into utc, while storing it in database, 2024-03-27 07:00:00.000 +0530 supposed to converted to utc and while reading it should convert back to IST timezone, which i have set as local time in postgres.
Asia/kolkata
so in preprod env, it is giving me absolute value as 2024-03-27 07:00:00.000 which is actually correct behavior's as per my requirement, so it has not converted to utc and while reading it is giving me correct value withou utc conversion, but as per above documentation ,it should convert to IST timezone, if it gets converted then it produce wrong timezone, which will be 2024-03-27 12:30:00.000.
so what is the correct behavior of posgres, seems like postgres is behaving differently, so i am considering to convert to timestamptz to timestamp due to this issue.
Edit:- i have set timezone to 'Asia/kolkata' in ubuntu and same has been configured in my docker-compose, so both postgres and java service has IST timezone.
my docker-compose.yml.
java-service:
image: prod:latest
container_name: java-prod
ports:
- 8080:8080
build:
context: .
dockerfile: /Dockerfile
depends_on:
- prod_db
volumes:
- /etc/timezone:/etc/timezone:ro
- /etc/localtime:/etc/localtime:ro
restart: always
prod_db:
image: 'postgres:12.18-alpine'
container_name: prod_db
ports:
- 5433:5432
volumes:
- /etc/timezone:/etc/timezone:ro
- /etc/localtime:/etc/localtime:ro
- ./postgres:/var/lib/postgresql/data:rw
root@srv529197:/opt/mas/prod# docker exec -it prod_db sh
/ # date
Thu May 23 16:55:38 IST 2024
/ #
Make sure that the parameter timezone is set to Asia/Kolkata in the database session in your production environment. You can either start the Java virtual machine with that setting:
java -Duser.timezone=Asia/Kolkata ...
or set timezone in the PostgreSQL connection URI:
jdbc:postgresql://localhost:5432/test?options=-ctimezone=Asia/Kolkata