I'm using PostgreSQL 8.4, and the server is linux, and the linux's time zone is'EDT' not 'UTC'. The configuration of PostgreSQL make the DataBase's time zone to 'UTC'. The code is running on JBoss9.
I have one sql, select to_char(ts_entry.submitted_date, 'MM/DD/YYYY HH24:MM') as submitted_date_format from ts_entry where ....
If we run the sql in PostgreSQL, we will get the value, "07/10/2017 02:07"
But when I try to get the value from resultSet in java,
Statement st = connection.createStatement();
ResultSet rs = st.executeQuery(sql);
String value = rs.getString("submitted_date_format");
The value will be "07/09/2017 22:07".
The origin value in DB is "2017-07-10 02:02:25.268+00".
How can I handle the effect caused by linux server's timezone in code level?
BTW, I know an alternative solution, change the start up scripts of jboss, to make the jboss to start up using timezone 'UTC'. Can this issue be handled in code level?
The Database server machine's time zone has no direct impact on the behaviour, except that it is used to initialize timezone in postgresql.conf, which is the initial setting for the client time zone unless overridden by the database session.
PostgreSQL stores timestamp with time zone in UTC internally and converts it to the client's local time zone upon delivery.
So you should set the database session time zone the way you need with
SET TIME ZONE '<time zone name>';
That will convert dates to that time zone when you select then from PostgreSQL.