[
https://issues.apache.org/jira/browse/PHOENIX-1485?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=14226427#comment-14226427
]
Gabriel Reid commented on PHOENIX-1485:
---------------------------------------
First some background on this: the underlying issue is the fact that there are
some inconsistencies with how Phoenix handles DATE/TIME/TIMESTAMP values vs the
most common interpretation of the JDBC spec. A large part of this is described
in PHOENIX-868.
In general, most JDBC drivers interpret and display String representations of
DATE/TIME/TIMESTAMP values in the local timezone of the client. How the values
are actually stored, and in which timezone, is outside of the scope of a JDBC
driver. Related to this is the fact that java.sql.Date/Time/Timestamp all have
symmetrical valueOf(String) and toString methods which interpret and write
Strings as being in the local timezone, while using an underlying long value
which is an offset from the java epoch (which is GMT-based).
Also note: Date and Time values are only really meant to provide date (without
time component) and time (without date component) information. Phoenix
currently breaks this contract a bit, which also causes some potential issues.
Phoenix currently stores DATE/TIME/TIMESTAMP values as a long offset since the
java epoch. However, it interprets strings as being in GMT in the TO_DATE
function, and writes strings in GMT with the TO_CHAR function. This is not in
line with the local-timezone handling provided by the {{valueOf}} to
{{toString}} methods in java.sql.Time/Timestamp/Date
If this were all code that was not out in the wild, my recommendations would be:
* TO_DATE (or a replacement) should interpret strings in the local timezone,
or allow providing a timezone
* TO_DATE should only be used for creating a Date, and TO_TIME and
TO_TIMESTAMP should exist for the other two types
* TO_CHAR should also use the local timezone
* Going from Timestamp to Date should drop the time component, going in the
other direction should use midnight as the time component. Similar logic could
be used for dealing going between these types and TIME as well.
Following these recommendations would also allow creating a Date/Time/Timestamp
from a string and retrieve the same thing back, as well as ensuring that
TO_DATE does the same thing as java.sql.Date.valueOf(), and TO_CHAR does the
same as java.sql.Date.toString()
However, doing all of the above would totally break backwards compatibility.
Given that, the best thing I can think of before PHOENIX-868 is thoroughly
handled is to add a timezone parameter to TO_DATE, and/or add a TO_LOCAL_DATE
function which interprets the date in the local timezone (instead of GMT). The
bulk loader would also need to have some kind of parameter to specify which
timezone should be used when interpreting date values.
Any thoughts on that approach [~jamestaylor] or [~noamb]?
> Date columns should be displayed without timezone offset
> --------------------------------------------------------
>
> Key: PHOENIX-1485
> URL: https://issues.apache.org/jira/browse/PHOENIX-1485
> Project: Phoenix
> Issue Type: Bug
> Affects Versions: 4.1
> Reporter: noam bulvik
>
> when date or timestamp column queried the value is the date with timezone
> offset. this is not how it is handled by other jdbc drivers like oracle and
> impala. and it seems strange that when I have text file with specific date
> and I use bulk loader to store it , when I will use select * .. I will get
> different dates then the one in the file (because of the time zone offset)
> by default the date should be displayed without any offset.
> it will be nice to have an option to add the timezone offset the option in
> the driver
--
This message was sent by Atlassian JIRA
(v6.3.4#6332)
