[
https://issues.apache.org/jira/browse/DRILL-5360?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=15929351#comment-15929351
]
Paul Rogers commented on DRILL-5360:
------------------------------------
>From Julian Hyde in the dev mailing list:
bq. If I am reading a timestamp value from a database, and I know it to be in
local timezone, I use the ResultSet.getTimestamp(int) method, which (per the
JDBC spec) interprets the value as being in my JVM’s time zone, and converts it
into an instant (a java.sql.Timestamp) accordingly. Thus ‘1970-01-01 00:00:00’
will become ‘1969-12-31 16:00:00 UTC’ since I am in pacific time.
bq. If I am reading a timestamp value from a database, and I know it to be in
some other timezone, I use the ResultSet.getTimestamp(int, Calendar) method,
which applies the time zone inside the calendar.
bq. I HAVE to provide a timezone, implicitly or explicitly, when reading a
TIMESTAMP value from a database via JDBC into a java.sql.Timestamp. Why?
Because I am converting a zoneless value into an instant.
bq. Note that the database can do quite a few operations on a timestamp without
knowing its time zone. For instance "CAST(ts AS VARCHAR)" and "EXTRACT(HOUR
FROM ts)” and “ts + INTERVAL ‘1’ DAY” all make sense.
Given this, perhaps provide a system table that can be queried to get the
server's timezone so that the client can pass the server's timezone into the
{{getTimestamp}} method in order to convert server timezone to UTC. This will
avoid the error of using the client's timezone.
Of course, it would be even nicer if Drill did this automatically...
> Timestamp type documented as UTC, implemented as local time
> -----------------------------------------------------------
>
> Key: DRILL-5360
> URL: https://issues.apache.org/jira/browse/DRILL-5360
> Project: Apache Drill
> Issue Type: Bug
> Affects Versions: 1.10.0
> Reporter: Paul Rogers
>
> The Drill documentation implies that the {{Timestamp}} type is in UTC:
> bq. JDBC timestamp in year, month, date hour, minute, second, and optional
> milliseconds format: yyyy-MM-dd HH:mm:ss.SSS. ... TIMESTAMP literals: Drill
> stores values in Coordinated Universal Time (UTC). Drill supports time
> functions in the range 1971 to 2037. ... Drill does not support TIMESTAMP
> with time zone.
> The above is ambiguous. The first part talks about JDBC timestamps. From the
> JDK Javadoc:
> bq. Timestamp: A thin wrapper around java.util.Date. ... Date class is
> intended to reflect coordinated universal time (UTC)...
> So, a JDBC timestamp is intended to represent time in UTC. (The "indented to
> reflect" statement leaves open the possibility of misusing {{Date}} to
> represent times in other time zones. This was common practice in early Java
> development and was the reason for the eventual development of the Joda, then
> Java 8 date/time classes.)
> The Drill documentation implies that timestamp *literals* are in UTC, but a
> careful read of the documentation does allow an interpretation that the
> internal representation can be other than UTC. If this is true, then we would
> also rely on a liberal reading of the Java `Timestamp` class to also not be
> UTC. (Or, we rely on the Drill JDBC driver to convert from the (unknown)
> server time zone to a UTC value returned by the Drill JDBC client.)
> Still, a superficial reading (and common practice) would suggest that a Drill
> Timestamp should be in UTC.
> However, a test on a Mac, with an embedded Drillbit (run in the Pacific time
> zone, with Daylight Savings Time in effect) shows that the Timestamp binary
> value is actual local time:
> {code}
> long before = System.currentTimeMillis();
> long value = getDateValue(client, "SELECT NOW() FROM (VALUES(1))" );
> double hrsDiff = (value - before) / (1000.00 * 60 * 60);
> System.out.println("Hours: " + hrsDiff);
> {code}
> The above gets the actual UTC time from Java. Then, it runs a query that gets
> Drill's idea of the current time using the {{NOW()}} function. (The
> {{getDateValue}} function uses the new test framework to access the actual
> {{long}} value from the returned value vector.) Finally, we compute the
> difference between the two times, converted to hours. Output:
> {code}
> Hours: -6.9999975
> {code}
> As it turns out, this is the difference between UTC and PDT. So, the time is
> in local time, not UTC.
> Since the documentation and implementation are both ambiguous, it is hard to
> know the intent of the Drill Timestamp. Clearly, common practice is to use
> UTC. But, there is wiggle-room.
> If the Timestamp value is supposed to be local time, then Drill should
> provide a function to return the server's time zone offset (in ms) from UTC
> so that the client can to the needed local-to-UTC conversion to get a true
> timestamp.
> On the other hand, if the Timestamp is supposed to be UTC (per common
> practice), then {{NOW()}} should not report local time, it should return UTC.
> Further, if {{NOW()}} returns local time, but Timestamp literals are UTC,
> then it is hard to see how any query can be rationally written if one
> timestamp value is local, but a literal is UTC.
> So, job #1 is to define the Timestamp semantics. Then, use that to figure out
> where the bug lies to make implementation consistent with documentation (or
> visa-versa.)
--
This message was sent by Atlassian JIRA
(v6.3.15#6346)
