JPQL CURRENT_TIMESTAMP offset by 2 hours

Question

When i select CURRENT_TIMESTAMP in a JPQL query the resulting java.sql.Tiemstamp is off by 2 hours. Behavior happened (presumably) after the winter to summer time switch. Before that the time was correct.

The linux system time using date in the shell is correct.
The CURRENT_TIMESTAMP inside oracle when running a native query from the linux machine itself is corret.
The time inside java when creating a new Date() object is correct.
But the CURRENT_TIMESTAMP i get from a JPQL query (java.sql.Tiemstamp) is wrong / off by 2 hours.

JPQL (wrong)

When i run the following query (ignore the OutboundMessage part, i only highjacked this to query the value of CURRENT_TIMESTAMP).

Query q = getManager().createQuery("select o.id, CURRENT_TIMESTAMP from OutboundMessage o where o.id=11370");
Timestamp current_timestamp = (Timestamp)((Object[])q.getResultList().get(0))[1];

In the debugger timestamp is this (i've pruned some properties so only the relevant ones remain):

result = {Timestamp@13972} "2016-03-31 14:48:12.432215"
 cdate = {Gregorian$Date@13975} "2016-03-31T14:48:12.000+0200"
  zoneinfo = {ZoneInfo@12683} "sun.util.calendar.ZoneInfo[id="Europe/Vienna",offset=3600000,dstSavings=3600000,useDaylight=true,transitions=139,lastRule=java.util.SimpleTimeZone[id=Europe/Vienna,offset=3600000,dstSavings=3600000,useDaylight=true,...]"
   rawOffset = 3600000
   rawOffsetDiff = 0
   dstSavings = 3600000
   ID = "Europe/Vienna"
  zoneOffset = 7200000
  daylightSaving = 3600000
  forceStandardTime = false
  locale = null

jvm (right)

While a simple new Date() gives (7 min later):

result = {Date@13983} "Thu Mar 31 16:55:07 CEST 2016"
 cdate = {Gregorian$Date@13986} "2016-03-31T16:55:07.871+0200"
  zoneinfo = {ZoneInfo@12683} "sun.util.calendar.ZoneInfo[id="Europe/Vienna",offset=3600000,dstSavings=3600    000,useDaylight=true,..."
  zoneOffset = 7200000
  daylightSaving = 3600000
  forceStandardTime = false
  locale = null

native sql (right)

The corresponding native query that i get enabling hibernate logging also yields the correct time

select outboundme0_.OM_ID as col_0_0_, CURRENT_TIMESTAMP as col_1_0_ from MAILABO_OTS_TEST.TB_OUTBOUND_MESSAGE outboundme0_ where outboundme0_.OM_ID=11370

yields col_1_0_ = 2016-03-31 16:59:16.910065 Europe/Berlin

So what is it that makes only the jpql CURRENT_TIMESTAMP wrong?

**Editing this section to be a bit clearer
What i am really trying to do is to compare the current timestamp with a threshold timestamp on the entity itself like this: select o from OutboundMessage o where o.id=11370 and o.someDate < CURRENT_TIMESTAMP

Executing this query give me no results at a point in time where the condition o.someDate < CURRENT_TIMESTAMP should be true.
However when i execute the corresponding native-sql query to this jpql query from hibernate sql logging i (correctly) get 1 result.

I only selected and cast CURRENT_TIMESTAMP to a java.sql.Timestamp while debugging to figure out the cause of my entity not being selected and then realized that the CURRENT_TIMESTAMP value was off by 2 hours.

I would expect the behavior to be: jqpl -> translated to native query -> native query executed on db -> result translated back to java classes. Assuming this behavior the entire java-side of my application should never even be aware of the actual value the CURRENT_TIMESTAMP holds -- it should only be concerned with translating jpql to native sql and translating the resultset back to java objects.

Workaround
What i ended up doing for now is to replace CURRENT_TIMESTAMP with a named parameter like

queryString="...where o.someDate < :referenceTime"
Query query = entityManager.createQuery(queryString);
query.setParameter("referenceTime", new Date());

This works at least. However i would still like to understand what the problem could be.

K.Nicholas K.Nicholas · Accepted Answer · 2016-04-01T19:48:04

Your question is a little confused. Look at, for a start, the Javadoc on java.sql.timestamp:

Note: This type is a composite of a java.util.Date and a separate nanoseconds value. Only integral seconds are stored in the java.util.Date component. The fractional seconds - the nanos - are separate. The Timestamp.equals(Object) method never returns true when passed an object that isn't an instance of java.sql.Timestamp, because the nanos component of a date is unknown. As a result, the Timestamp.equals(Object) method is not symmetric with respect to the java.util.Date.equals(Object) method. Also, the hashCode method uses the underlying java.util.Date implementation and therefore does not include nanos in its computation.

Due to the differences between the Timestamp class and the java.util.Date class mentioned above, it is recommended that code not view Timestamp values generically as an instance of java.util.Date. The inheritance relationship between Timestamp and java.util.Date really denotes implementation inheritance, and not type inheritance.

So, it seems to recommend against what you are doing in JPA, which is (down?)casting the result.

If I run this, I get an expected result:

Tuple t = em.createQuery("select u.id, CURRENT_TIMESTAMP from User u where u.id = 1", Tuple.class).getSingleResult();
for ( TupleElement<?> e: t.getElements()) {
    System.out.println("Element = "+e.getAlias()+":"+t.get(e));
}

And I don't get the problem you are describing. This gives the same time as above.

System.out.println( (Timestamp)t.get(1) );

I'm not sure what you mean by However i get 0 hits running this in java.

JPQL CURRENT_TIMESTAMP offset by 2 hours

1 Answers