drill-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Jinfeng Ni <...@apache.org>
Subject Re: Drill date & time types encoding
Date Fri, 17 Mar 2017 01:28:28 GMT
yes, you are right there is difference between "should" and "may". I
"should" use "may" in the first place.

However, I do not think the interpretation of a timestamp is entirely
up to the user (Do you mean the end user of a database product?).
What if the implementation of database takes a different
interpretation? For instance, when the system has to do conversion
between a timestamp w/ tz and a timestamp w/o tz, "SQL assumes the
value without time zone to be local, subtracts the current default
time zone displacement of the SQL-session from it to give UTC, and
associates that time zone displacement with the result."

I think what we are talking about is the semantics of those different
data types in a database system; it has nothing to do whether the
system is using JVM, or running on linux or other operation system.





On Thu, Mar 16, 2017 at 5:39 PM, Julian Hyde <jhyde@apache.org> wrote:
> The difference between “should” and “may”. The interpretation of a timestamp
is entirely up to the user.
>
> 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.
>
> 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.
>
> 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.
>
> 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.
>
> Julian
>
>
>
>> On Mar 16, 2017, at 4:54 PM, Jinfeng Ni <jni@apache.org> wrote:
>>
>> On Thu, Mar 16, 2017 at 4:41 PM, Julian Hyde <jhyde@apache.org <mailto:jhyde@apache.org>>
wrote:
>>>
>>>> On Mar 16, 2017, at 4:25 PM, Jinfeng Ni <jni@apache.org> wrote:
>>>>
>>>> Time/Timestamp without t/z should be interpreted as local time.
>>>
>>>
>>> No.
>>>
>>> If I am in pacific time and I have a TIMESTAMP value “1970-01-01 12:00:00”
and I send it to you in central european time you receive a TIMESTAMP value “1970-01-01
12:00:00”.
>>>
>>> Its time zone is not my local time zone, or your local time zone, or UTC. It
has no time zone.
>>>
>>>
>>
>> Maybe I did not read the SQL 2011 ISO/IEC 9075-2:2011(E) correctly.
>> How do we interpret the following ?
>>
>> "A datetime value, of data type TIME WITHOUT TIME ZONE or TIMESTAMP
>> WITHOUT TIME ZONE, may represent a local time"
>

Mime
View raw message