calcite-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Julian Hyde <>
Subject Re: timezone confusion
Date Fri, 25 Sep 2015 15:58:34 GMT
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.
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.


> On Sep 25, 2015, at 5:55 AM, Jan Van Besien <> wrote:
> On Fri, Sep 25, 2015 at 11:06 AM, Julian Hyde <> 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

View raw message