postgresql JDBC driver uses client time zone

Question

I am using postgresql-42.2.2.jar jdbc driver and postgresql 9.6 server.

How can I configure the connection to use server timezone instead of client timezone?

Query:

select id, TO_TIMESTAMP(tbl.ended_time / 1000)
FROM tbl
where TO_TIMESTAMP(tbl.ended_time / 1000) >= now()::date + interval '1m'

Running a query with pgadmin I am getting the following result (which is correct)

"2185"  "2018-06-22 00:02:02-05"
"2186"  "2018-06-22 00:03:01-05"
"2187"  "2018-06-22 00:13:02-05"

but running the same query with JDBC driver the result is :

2169 2018-06-22 07:13:01.0
2181 2018-06-22 08:33:02.0
2180 2018-06-22 08:23:01.0
2185 2018-06-22 09:02:02.0
2186 2018-06-22 09:03:01.0
2187 2018-06-22 09:13:02.0

without where the running query with pgadmin the result is:

"2169"  "2018-06-21 22:13:01-05"
"2181"  "2018-06-21 23:33:02-05"
"2180"  "2018-06-21 23:23:01-05"
"2185"  "2018-06-22 00:02:02-05"
"2186"  "2018-06-22 00:03:01-05"
"2187"  "2018-06-22 00:13:02-05"

Here is the code:

static String URL = "jdbc:postgresql://some_ip:7000/db";
static String USERNAME = "test";
static String PASSWORD = "test";
static String JDBC_DRIVER = "org.postgresql.Driver";

public static void main(String[] args) {
    String query = "select id, TO_TIMESTAMP(tbl.ended_time / 1000) FROM tbl where TO_TIMESTAMP(tbl.ended_time / 1000) >= now()::date + interval '1m'";
    Connection conn = null;

    try {
        Class.forName(JDBC_DRIVER);
        conn = DriverManager.getConnection(URL, USERNAME, PASSWORD);
        Statement st = conn.createStatement();          
        ResultSet rs = st.executeQuery(query);
        while (rs.next()) {             
            System.out.println(rs.getObject(1) + "--" + rs.getObject(2));
        }
        rs.close();
        st.close();
        conn.close();
    } catch (Exception e) {
        // TODO Auto-generated catch block
        e.printStackTrace();
    } 

}

Assuming you receive the data as a `java.sql.Timestamp`, there is no timezone associated to it. a `Timestamp` is just a number of millis since the epoch., Since JDBC 4.2, you can retrieve dates and times as java.time objects, such as an `OffsetDateTime` which will contain timezone information. — assylias, Jun 22 '18 at 10:01
Try `rs.getObject(2, ZonedDateTime.class)` or `rs.getObject(2, OffeDateTime)` — , Jun 22 '18 at 10:06
The simplest way in with no doubt to configure the client environment to use the server time zone. This can be configured per JVM... — Serge Ballesta, Jun 22 '18 at 11:26

assylias · Answer 1 · 2018-06-22T11:17:33.607

2

As explained here, the JDBC driver uses your local time zone and not the server time zone.

Unfortunately there is no easy way to change that behaviour or to query the server's time zone.

Also note that retrieving the data using an OffsetDateTime doesn't help much:

rs.getObject(2, OffsetDateTime.class)

because unfortunately, the driver returns all OffsetDateTimes with an offset of 0, so the timezone information is lost.

So it seems that your best bet is probably to either hardcode the database's timezone and calculate your time limit in Java or to store the timezone separately in your DB and query it.

edited Jun 22 '18 at 11:17

answered Jun 22 '18 at 10:06

assylias

321,522
82
660
783

Thank you for your answer, but the problem is in result count. As you can see in my example running with pgadmin returns 3 row running with JDBC returns 6 rows. I need at first get the correct number of result. – Lusi Jun 22 '18 at 10:11
@Lusi I see - I hadn't noticed that part of your question. – assylias Jun 22 '18 at 10:15
@Lusi This looks closely related https://stackoverflow.com/questions/18447995/postgresql-9-2-jdbc-driver-uses-client-time-zone – assylias Jun 22 '18 at 10:18
2

@assylias you are right that java.time classes are much more sane when dealing with time zones, however the current question arises from comparing `timestamp` and `timestamp with timezone`. It has nothing to do with `OffsetDateTime` or whatever – Vladimir Sitnikov Jul 12 '18 at 19:16

score -1 · Answer 2 · answered Jul 12 '18 at 12:44

TL;DR: it has nothing to do with JDBC driver. The SQL is flawed.

What do you expect when comparing timestamp WITH time zone and timestamp withOUT time zone? Your SQL is just plain wrong, and thus it produces weird results.

What do you expect out of now()::date? Note: PostgreSQL documentation does NOT state which timezone now() would produce. It specifies just that "timezone will be there", however it could even change from one PG version to another one. So you are basically requesting "a random value".

Please clarify what do you want to achieve, then write an appropriate SQL. For instance, if you want to get records with timestamps "after 00:04:00 of the current day in a Europe/Berlin time zone", then you'd use something like

where TO_TIMESTAMP(tbl.ended_time / 1000) >= ((now() at time zone 'Europe/Berlin')::date + interval '4m')::timestamp at time zone 'Europe/Berlin'

The thing is there's no "start of a day" unless you specify which time zone you mean. pgjdbc populates session with default timezone (TimeZone.getDefault()), so it is used in implicit conversions, however I would strongly advice you from comparing with and without timezone kind of datatypes in SQL no matter what you use to execute.

postgresql JDBC driver uses client time zone

2 Answers2

Linked