calcite-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Julian Hyde <jh...@apache.org>
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. 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 <janvb@ngdata.com> wrote:
> 
> On Fri, Sep 25, 2015 at 11:06 AM, Julian Hyde <jhyde@apache.org> 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


Mime
View raw message