The fundamental cause is that SQL’s TIMESTAMP represents a local time with no stated timezone, whereas Java’s java.util.Date represents a moment in time. Any conversion between those is going to require a timezone (implicit or explicit).
I don’t know the Joda-Time API (part of the JDK since 1.8) very well, but I see that it recognizes “instant” and “local time” as separate concepts. See e.g. https://docs.oracle.com/javase/8/docs/api/java/time/LocalDateTime.html. Maybe we should support resultSet.getObject(0, java.time.LocalDateTime.class), then people can convert the LocalDateTime to an Instant if that’s what they want. Julian > On Sep 25, 2015, at 5:55 AM, Jan Van Besien <[email protected]> wrote: > > On Fri, Sep 25, 2015 at 11:06 AM, Julian Hyde <[email protected]> wrote: >> The SQL standard says that TIMESTAMP values do not have a time zone. So, >> “1970-01-01 00:00:00” means just that; it does not mean “1970-01-01 00:00:00 >> UTC” or "“1970-01-01 00:00:00 CET” or anything on your local time zone. The >> time zone is an interpretation placed on the value when they read it. > > I agree. > >> Now JDBC is another matter. When you read a TIMESTAMP value via JDBC, >> specifically the ResultSet.getTimestamp(int) method, it translates it into >> the local timezone. So, when you read it, it becomes “1970-01-01 00:00:00 >> CET”, whose value is -36000000L (one hour, in milliseconds, before the UTC >> epoch). > > What you are describing here is indeed what calcite is doing. I've > been going through some tests with hsqldb and mysql which lead me to > conclude that they seem to do this as well. I am starting to > understand why this is considered correct, although each time I think > I understand it I start to doubt again ;-) Do you know where in the > JDBC spec it is explained that it should be like this? I couldn't find > anything that suggests this in the JDBC spec nor in the javadoc. > > Anyway, given that at least hsqldb and mysql also seem to do it like > this, I am willing to accept that it is how it is supposed to be. > > Maybe it is just wrong that JDBC uses timezone-aware objects to > represent things like SQL TIMESTAMP. > > > Jan
