Mapping of Timestamp values and time zones

[Karsten Sperling]

I'm using @WhenCreated to get creation timestamps for my entities. My first choice would have been for these fields to be of type java.time.Instant, as (to my mind) a point on the timeline without any particular time zone information is the most obvious representation for creation / update times. (I can see use cases for OffsetDT / ZoneDT as well where the creation or update of the object is associated with a specific time zone at the application level, but that's not the case in my application.)

However Instant isn't currently supported by InsertTimestampFactor. Is this intentional, i.e. is Instant somehow thought to be unsuitable for this, or just an omission? I'll look into submitting a pull request in that case.

So in the interim I've just used java.sql.Timestamp instead of java.util.Instant. The next quirk I'm running into is that Ebean seems to map these to type 'TIMESTAMP' by default. Now this works fine for testing on H2, however when I run against Postgres things aren't so great, as the value end up getting stored and read in whatever happens to be the JVM default timezone of the application reading or writing, i.e. effectively treating it like a local / "human" date time, rather than an instant / timeline value. One way to work around this would be to ensure the JVM default zone is always UTC, but I'd much prefer my DB operations to have the correct semantics without relying on that.

The other point is that (at least for Postgres) 'TIMESTAMP WITH TIMEZONE' seems to be a much better match for instant / timeline type values because it explicitly tells Postgres that the date/time we're storing relates to a particular point on the timeline, and allows Postgres to convert to/from other timezones if requested. If TIMESTAMP WITH TIMEZONE was used, @WhenCreated should do what I want out of the box -- the driver would still send the time in the default JVM timezone, but PG would convert that to UTC. On reads, PG would convert to the time zone set for the connection, and the driver would parse the offset and adjust the instant correctly. Still, it would be even nicer if all of those conversions didn't have to happen in the first place.

What's best practice for solving these issues?

A few ideas came to my mind in terms of how Ebean could make this easier

• It would be nice to be able to configure set default TimeZone on the EbeanServer (or maybe even on a transaction), such that calls ebean makes to PreparedStatement.setTimestamp() and ResultSet.getTimestamp() will use the variant with an explicit Calendar to pass in that time zone. This would mean I can tell the DB connection to use UTC without having to worry about the default JVM timezone.
• The ability to configure what DB types the various time types map to globally. Maybe this could be delegated to the DatabasePlatform, as what different DBs support seems to be quite different in this area. Essentially I can think of three conceptual types that might map differently for each DB: (1) A 'human' date/time (i.e. java.time.LocalDateTime / joda LocalDateTime), (2) a timeline instant (java.time.Instant, java.util.Date), and (3) a timeline instant with a zone that is stored (java.time.ZonedDateTime, joda DateTime), or equivalently a combination of 1 and 2. timestamp with time zone' seems to have the semantics of (3) in Oracle and Sybase and (2) in Postgres for example.
  

Thoughts on these ideas? Let me know if I'm doing it all wrong or point me in the right direction if this has all been solved years ago :-)

Cheers, Karsten

[Rob Bygrave]

> Instant isn't currently supported by InsertTimestampFactory.

That ought to be a bug/enhancement.  It should be supported, I think Instant is a good option for this.

> (at least for Postgres) 'TIMESTAMP WITH TIMEZONE' seems to be a much better match 

Have a look at https://github.com/ebean-orm/avaje-ebeanorm/issues/402

When I looked at this for Postgres the only difference between "Timestamp" and "Timestamp with timezone" is client side and as we are using JDBC it actually had no effect in testing. So to clarify that further, both of these DB types store UTC timestamp value in the DB and neither store any additional timezone information (unlike the similarly named types in say Oracle).

So using Postgres "Timestamp with timezone" is rather a moot point when using JDBC (at this point).

> I've just used java.sql.Timestamp

Yes that uses the local timezone but it ought be then converted locally by the JDBC driver to UTC (using that local timezone) before it is actually stored in the DB.  So as long as it is read and written in this way it is still the correct UTC time (without needing the Calendar passing).  So as long as the application code treats a Timestamp as relative to the default timezone of the JVM when it should be all good.

However, you are suggesting there is an issue here. Do you have a code example / configuration that shows the issue with the Timestamp treatment?  I'm guessing your DB is in a different timezone to the application?  It would be good to reproduce exactly what you are seeing here (maybe need JDBC version, Postgres version, timezones used).

> will use the variant with an explicit Calendar to pass in that time zone.  

Yes, I'd like to reproduce and see the issue first.

The thing Ebean isn't supporting is Oracle's Timestamp with timezone type where both are stored (and yes that would more naturally map to ZonedDateTime and yes we'd need to be passing in Calendar for that case).  In this case the ZonedDateTime would come back in the stored timezone rather than always come back in the local jvm default timezone.

... there is the chance I've missed something so yeah if I can reproduce the issue your seeing that would be great.

Cheers, Rob.

--

---
You received this message because you are subscribed to the Google Groups "Ebean ORM" group.
To unsubscribe from this group and stop receiving emails from it, send an email to ebean+un...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[Rob Bygrave]

> Instant isn't currently supported by InsertTimestampFactory

FYI:  I've logged that as: https://github.com/ebean-orm/avaje-ebeanorm/issues/659

Also note there is now an "example-minimal" application that could be good for cloning and reproducing issues:

https://github.com/ebean-orm-examples/example-minimal

Cheers, Rob. 

[Karsten Sperling]

Hi Rob,

thanks for the quick reply! here is some plain(-ish -- with Spring) JDBC code that shows the different behaviour of Timestamp with/without zone:

long now = 1460000000000L;//System.currentTimeMillis();
log.info("Driver version='{}', DB version='{}'", org.postgresql.Driver.getVersion(), jdbc.queryForObject("select version()", String.class));

jdbc.execute("create table dummy (ts timestamp not null, tswz timestamp with time zone not null)");
log.info("jvm-tz={}, pg-tz={}", TimeZone.getDefault().getID(), jdbc.queryForObject("show time zone", String.class));
log.info("src= {}", ts2s(new Timestamp(now)));
jdbc.update("insert into dummy (ts, tswz) values (?, ?)", new Timestamp(now), new Timestamp(now));

Map<String, Object> result1 = jdbc.queryForMap("select * from dummy limit 1");
log.info("ts=  {}\ntswz={}", ts2s(result1.get("ts")), ts2s(result1.get("tswz")));

TimeZone.setDefault(TimeZone.getTimeZone("America/Los_Angeles")); // this could be a different application accessing the DB
jdbc.execute("set time zone 'Europe/Berlin'");
log.info("jvm-tz={}, pg-tz={}", TimeZone.getDefault().getID(), jdbc.queryForObject("show time zone", String.class));
Map<String, Object> result2 = jdbc.queryForMap("select * from dummy limit 1");
log.info("ts=  {}\ntswz={}", ts2s(result2.get("ts")), ts2s(result2.get("tswz")));

jdbc.execute("set time zone 'Europe/Dublin'");
log.info("jvm-tz={}, pg-tz={}", TimeZone.getDefault().getID(), jdbc.queryForObject("show time zone", String.class));
Map<String, Object> result3 = jdbc.queryForMap("select * from dummy limit 1");
log.info("ts=  {}\ntswz={}", ts2s(result3.get("ts")), ts2s(result3.get("tswz")));

This is the output

// Driver version='PostgreSQL 9.4.1208'
// DB version='PostgreSQL 9.5.2 on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 4.8.2-19ubuntu1) 4.8.2, 64-bit'
//
// jvm-tz=Pacific/Auckland, pg-tz=Pacific/Auckland
// src= [millis=1460000000000 local=2016-04-07 15:33:20.0 utc=2016-04-07T03:33:20Z]
// ts=  [millis=1460000000000 local=2016-04-07 15:33:20.0 utc=2016-04-07T03:33:20Z]
// tswz=[millis=1460000000000 local=2016-04-07 15:33:20.0 utc=2016-04-07T03:33:20Z]
//
// jvm-tz=America/Los_Angeles, pg-tz=Europe/Berlin
// ts=  [millis=1460068400000 local=2016-04-07 15:33:20.0 utc=2016-04-07T22:33:20Z]
// tswz=[millis=1460000000000 local=2016-04-06 20:33:20.0 utc=2016-04-07T03:33:20Z]
//
// jvm-tz=America/Los_Angeles, pg-tz=Europe/Dublin
// ts=  [millis=1460068400000 local=2016-04-07 15:33:20.0 utc=2016-04-07T22:33:20Z]
// tswz=[millis=1460000000000 local=2016-04-06 20:33:20.0 utc=2016-04-07T03:33:20Z]

Notice that for 'TS' all the 'local' values (i.e. the human readable y/m/d/... tuple) match, whereas for 'TSWZ' the millis and utc values (i.e. the actual represented instant in time match).

And this is what psql shows directly for comparison:

postgres=# set time zone 'utc'; select * from dummy;
SET
         ts          |          tswz          
---------------------+------------------------
 2016-04-07 15:33:20 | 2016-04-07 03:33:20+00
(1 row)
postgres=# set time zone 'Pacific/Auckland'; select * from dummy;
SET
         ts          |          tswz          
---------------------+------------------------
 2016-04-07 15:33:20 | 2016-04-07 15:33:20+12
(1 row)

• Looking at the psql output first, the 'timestamp' value is stored as a local time exactly how the application that inserted it supplied the value, it's is not shown with a timestamp, and the session timezone settings doesn't affect what's displayed.
  
• The 'with zone' value is stored in UTC and treated as an actual instant on the time line, and is display in whatever zone is set in the session.
  
• With JDBC, everything behaves essentially the same way: The 'timestamp' value is read back correctly if the default TZ of the reading application instance matches the default TZ of the application that wrote it (first set of output from the Java code).
• 2nd set of output, if the default TZ of the reading application is different from the one in effect when the value is written, you end up with the same 'local' time string, but the actual millis instant this represents is wrong. Everything works in the 'with timezone' field, because PG returns a zone offset along with the value so the JDBC driver recovers the correct millis instant.
• 3rd set of output, changing the session timezone setting (as opposed to has now effect on the 'timestamp' case because it's always returned by PG as is, and interpreted by the JDBC driver in the JVM's zone. It has no visible effect on the 'with timezone' field, because even though PG returns the value in that zone, it returns the zone offset along with it, which the JDBC driver then takes into account, so you again end up with the correct instant millis value.

So to summarise:

TIMESTAMP acts like a human date/time value that's always returned as the same year/month/day/hour/minute etc tuple. JDBC (and EBean) assume it to be in the JVM default time zone, so as soon as at any point in time any application instance that accesses the DB has the wrong time zone set (in practice anything other than UTC), you're in trouble. It's also inconvenient for accessing PG directly, because you can't use "set time zone" to tell PG to render the values in a zone that's convenient for looking at by hand.

TIMESTAMP WITH TIME ZONE acts like an instant on the time line, stored in UTC (but really it doesn't matter what zone it's actually stored in, because if PG returns it in a non-UTC zone, the zone offset is returned along with it, and the driver compensates).

The behaviour I'm getting with Ebean matches the vanilla JDBC behaviour.

Cheers, Karsten

[Karsten Sperling]

Just re-ran the test with the 9.3-1103-jdbc41 driver.

The behaviour seems to be essentially the same, however my test code doesn't manage to show the problem with the 'TIMESTAMP' type because the driver caches the default time zone object, so the test changing it halfway through doesn't affect the driver's parsing. When I split the test code into two parts and run the insert with one jvm TZ and the read in the other, the result is the same as with the 9.4 driver.

[Rob Bygrave]

Right. Looking at this now ...

[Rob Bygrave]

I'm not sure it's a good test to change both the JVM time zone and execute "set time zone xxx" for the same test.

I'd like to see your ts2s() method if you can share that.

For me:

I've adjusted this test a little bit to add 2 sets of columns and inserting a second set using Calendar so it becomes:

create table tztest (ts timestamp, tstz timestamp with time zone, ts1 timestamp, tstz1 timestamp with time zone);

The insert becomes:

private void insert() throws SQLException {

String insert = "insert into tztest (ts, tstz, ts1, tstz1) values (?,?,?,?)";

Calendar calendar = Calendar.getInstance();
calendar.setTimeZone(TimeZone.getTimeZone("UTC"));

Transaction transaction = Ebean.beginTransaction();
Connection connection = transaction.getConnection();
PreparedStatement statement = connection.prepareStatement(insert);
statement.setTimestamp(1, nowTs);
statement.setTimestamp(2, nowTs);
statement.setTimestamp(3, nowTs, (Calendar) calendar.clone());
statement.setTimestamp(4, nowTs, (Calendar) calendar.clone());
statement.executeUpdate();

transaction.commit();
}

The fetch testing becomes...

  @Test

  public void rawJdbc() throws SQLException {

    //insert();

    System.out.println("Local--");

    fetch();

    System.out.println("UTC--");

    TimeZone.setDefault(TimeZone.getTimeZone("UTC"));

    fetch();

    System.out.println("LA--");

    TimeZone.setDefault(TimeZone.getTimeZone("America/Los_Angeles"));

    fetch();

  }

  private void fetch() {

    SqlQuery sqlQuery = Ebean.createSqlQuery("select * from tztest");

    SqlRow row = sqlQuery.findUnique();

    System.out.println("   ts:"+tsof(row.getTimestamp("ts")));

    System.out.println(" tstz:"+tsof(row.getTimestamp("tstz")));

    System.out.println("  ts1:"+tsof(row.getTimestamp("ts1")));

    System.out.println("tstz1:"+tsof(row.getTimestamp("tstz1")));

  }

  private String tsof(Timestamp timestamp) {

    return ""+timestamp.getTime()+","+timestamp.toString();

  }

And the output of that is ...

Local--

   ts:1460000000000,2016-04-07 15:33:20.0

 tstz:1460000000000,2016-04-07 15:33:20.0

  ts1:1459956800000,2016-04-07 03:33:20.0

tstz1:1460000000000,2016-04-07 15:33:20.0

UTC--

   ts:1460000000000,2016-04-07 03:33:20.0

 tstz:1460000000000,2016-04-07 03:33:20.0

  ts1:1459956800000,2016-04-06 15:33:20.0

tstz1:1460000000000,2016-04-07 03:33:20.0

LA--

   ts:1460000000000,2016-04-06 20:33:20.0

 tstz:1460000000000,2016-04-06 20:33:20.0

  ts1:1459956800000,2016-04-06 08:33:20.0

tstz1:1460000000000,2016-04-06 20:33:20.0

So I'll look at why you are executing "set time zone xxx" in your example/test.  That is not clear to me though.  

[Karsten Sperling]

Hi Rob,

sorry, here's the ts2s() method:

  protected String ts2s(Object ts) {
    return String.format("[millis=%d local=%s utc=%s]", ((Timestamp) ts).getTime(), ts, ((Timestamp) ts).toInstant());
  }

I guess the 'set time zone xxx' isn't really necessary, I mainly put that in there to demonstrate that it doesn't affect the visible behaviour in this case. By default the JDBC driver (at least the 9.4) seems to set the JVM default time zone as the DB session time zone as well, but even when it's changed 'timestamp' fields don't get affected at all.

In terms of TimeZone.setDefault() during the test you need to be careful depending on which version of the driver you're using. 9.3 will cache the Calendar with the TZ internally, so the logic that transforms the string received from the DB into the millis value it puts into the java.sql.Timestamp doesn't see the changed zone. Which version are you using? Can you try leaving the values in the DB, and the running the 'fetch' in a separate JVM with a different default TZ?

I think for your 'with calendar' examples ts1/tswz1 you should also supply the same calendar to getTimestamp() in fetch()

[Rob Bygrave]

See the code in: https://github.com/ebean-orm/avaje-ebeanorm/issues/662

https://github.com/ebean-orm/avaje-ebeanorm/blob/master/src/test/java/com/avaje/tests/timezone/TimezoneTests.java

I'm using jdbc driver 9.4-1202-jdbc41

> Can you try leaving the values in the DB, and the running the 'fetch' in a separate JVM with a different default TZ?

Yes'ish.  I'm leaving the values in the DB.  I'm setting the default Timezone 3 times in the same/single JVM execution.  I'll try 3 different fetch execution runs.

> same calendar to getTimestamp() in fetch()

Yes, I converted the code all over to plain JDBC and that is what I'm doing now.

[Rob Bygrave]

>  running the 'fetch' in a separate JVM with a different default TZ?

That reproduces the problem.  e.g. Running in LA timezone by itself reproduces the problem (a different timezone from the original).

[Rob Bygrave]

Right, that ticket is a bit verbose but I think we have got this issue sorted there and fix pushed into master. 

Also note the #663 issue to change the default timezone.  

Thanks Karsten, great work there - ta !!!

Folks using MySql need to be careful with their jdbc driver configuration properties.

Cheers, Rob.

[Karsten Sperling]

Thanks Rob, this looks great! I'll give it a test tomorrow.

Cheers, Karsten

You received this message because you are subscribed to a topic in the Google Groups "Ebean ORM" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/ebean/OlHWFWImiXE/unsubscribe.
To unsubscribe from this group and all its topics, send an email to ebean+un...@googlegroups.com.