calcite-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Jan Van Besien <>
Subject timezone confusion
Date Fri, 25 Sep 2015 08:42:30 GMT
I might be misunderstanding something, but I think calcite is wrong
with respect to how it handles timezones in date, time and timestamp.

Allow me to use the existing CsvTest#testDateType test as an example,
in particular the test with a timestamp on line 385.

The data in the CSV file itself for the timestamp field is the string
"1996-08-03 00:01:02". According to the TIME_FORMAT_TIMESTAMP in the
CsvEnumerator, this String is interpreted as being a timestamp in GMT
timezone. So it is considered "1996-08-03 00:01:02 GMT". So far, so

Note that my local timezone is CEST (2 hours east of GMT). When
querying above timezone, I think the correct thing to do is to return
the Timezone exactly as is, hence "1996-08-03 00:01:02 GMT" or
"1996-08-03 02:01:02 CEST". Given that the string representation of a
Timestamp object returns a String in the local timezone (without
mentioning that local timezone), the resulting string would be
"1996-08-03 02:01:02". This might seem wrong (2 hours to late) but it
is actually correct because it is in the local (CEST) timezone. More
importantly, the millis inside this Timestamp object will in this case
be exactly the same as the millis inside the Timestamp object returned
by the CsvEnumerator.

However, calcite seems to do a negative correction on the millis value
with the local timezone offset, such that the result is actually 2
hours earlier, i.e. "1996-08-02 22:01:02 GMT". Thanks to the way
string formatting on the Timestamp object works, this ends up as being
the string "1996-08-03 00:01:02" which looks correct, but is actually
wrong because it is to be interpreted in the local Timezone which is
CEST, hence it really means "1996-08-02 22:01:02 GMT" which is not
what was in the database (CsvEnumerator).

If you agree with my analysis, this also means that the CsvTest on
line 385 is wrong, because it checks that the timestamp from the
resultset is java.sql.Timestamp.valueOf("1996-08-03 00:01:02") while
(in my timezone) it should really be
java.sql.Timestamp.valueOf("1996-08-03 02:01:02"). The test will have
to be rewritten to work in any timezone obviously, which will imply
creating a millis value from a Calendar with a certain fixed timezone.


View raw message