Comparsion between PostgreSQL timestamp without timezone and java.util.Date with timezone

Question

I would like to know how the comparsion in the following query will be performed:

StringBuilder queryStr = new StringBuilder();
queryStr.append("SELECT o FROM PRDBook as o WHERE ")
        .append("o.publicationDate < :now");

Query query = entityManager.createQuery(queryStr.toString());
Date now = new Date();
query.setParameter("now", now);

In the database, column publicationDate has type timestamp without time zone. Sample value in this column:

2018-03-01 18:00:00

The result of now.toString() is:

Wed Aug 22 16:14:03 CEST 2018

Will the comparsion between mentioned data be performed in that way:

2018-03-01 18:00:00 < 2018-08-22 16:14:03

or that way:

2018-03-01 18:00:00 < 2018-08-22 14:14:03

A java.util.Date does not have a timezone; it's just an abstract point in time. Have you tried running your code to see what actually happens? So, I vote for the first version being what will happen. — Tim Biegeleisen
– Tim Biegeleisen, Commented Aug 22, 2018 at 14:36
@TimBiegeleisen I have run the code having a book in database with publication date 2018-08-22 16:58:17. The value of now was Wed Aug 22 16:59:11 CEST 2018. The mentioned book object has appeared on the result list. So it seems, that you voted correctly. — Czarek
– Czarek, Commented Aug 22, 2018 at 15:01

The Impaler · Accepted Answer · 2018-08-22 17:39:53Z

5

Interesting question.

When PostgreSQL's JDBC driver reads a column of type timestamp without time zone, it can usually read it to a java.sql.Timestamp or java.util.Date.

If you choose to read it to a java.util.Date (your case) PostgreSQL's JDBC driver automatically adds the local JVM time zone to it. That makes sense since the whole point of a column of type timestamp without time zone is to be treated as a "local" timestamp.

Conversely, when you send a java.util.Date to the database it strips down the time zone right away. Therefore the query condition will take the form:

2018-03-01 18:00:00 < 2018-08-22 16:14:03

Collaterally, this means it's recommended you set the JVM time zone explicitly, to make sure the JDBC driver is reassembling the database timestamp without time zone into a java.util.Date the correct way.

Otherwise, different servers in different time zones reading the same row in the same database will interpret that timestamp as a different moment in time. Nevertheless, this should probably not be a valid use case for an application that uses "local" timestamps.

edited Aug 22, 2018 at 17:39

answered Aug 22, 2018 at 17:33

The Impaler

49.3k10 gold badges50 silver badges90 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

mvb13 Over a year ago

When you are talking about "automatically adds the local JVM time zone to it" or "strips down the time zone" do you mean just adding timezone information, but without any additional timestamp conversion like it happens between different time zones(for example database UTC time on read is converted to local timezone time)?

Collectives™ on Stack Overflow

Comparsion between PostgreSQL timestamp without timezone and java.util.Date with timezone

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related