phoenix-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Thomas Grayson <tgray...@bluemetal.com>
Subject RE: timezone offset
Date Wed, 19 Nov 2014 16:52:32 GMT
Noam,

The Phoenix JDBC driver converts date and timestamp columns to the local time zone of the
client's JVM.  However, the TO_DATE and TO_CHAR functions explicitly force the time zone to
be UTC.  In your example, the timestamp was inserted as UTC via TO_DATE and retrieved in UTC
by TO_CHAR.  In contrast, the TS column was converted to the local time zone (UTC+05:00 in
your case).  Hence, you have two choices for retrieving the date in UTC in SQuirreL:

-  Always use TO_DATE when querying these columns from the table.  This is inconvenient for
SELECT * queries, as you've discovered.
-  Have SQuirreL run in the UTC time zone by setting the "-Duser.timezone=UTC" JVM parameter.
 This is the route I have chosen.  I updated the "start" command in the squirrel.bat file
to use this parameter, as shown below:

start "SQuirreL SQL Client" /B "%LOCAL_JAVA%" -Xmx1024m -Duser.timezone=UTC -Dsun.java2d.noddraw=true
-cp %SQUIRREL_CP% -splash:"%SQUIRREL_SQL_HOME%/icons/splash.jpg" net.sourceforge.squirrel_sql.client.Main
%TMP_PARMS%

SQuirrel conveniently displays the time with the time zone in the lower right corner of its
window, so it is easy to confirm what time zone SQuirrel's JVM is using.

I'll add that in Phoenix the java.sql.ResultSet.getString method returns UTC, not local time,
at least for UNSIGNED_TIMESTAMP columns.

Some JDBC drivers, such as MySQL's, have configuration properties that allow one to work around
these time zone issues by configuring the connection.  To my knowledge, Phoenix has no such
options.  According to this StackOverflow post, the handling of time zones is up to the JDBC
implementation:

http://stackoverflow.com/a/9305468

For what it's worth I posted a similar question back in August to which no one replied:

http://mail-archives.apache.org/mod_mbox/phoenix-user/201408.mbox/%3C04914161c47d4d9b9d6103c59de80b2f%40BL2PR05MB193.namprd05.prod.outlook.com%3E

Tom Grayson

From: Bulvik, Noam [mailto:Noam.Bulvik@teoco.com]
Sent: Thursday, November 13, 2014 8:30 AM
To: user@phoenix.apache.org
Subject: timezone offset

I created a table with TIMESTAMP column and inserted a value from string to it. When I query
the table I get the result with timezone offset - any way to avid it.

My steps

*         I created a table DATE_TEST with TS column

*         For insert I use: upsert into DATE_TEST  values (to_Date('2014-09-01 11:00:00','yyyy-MM-dd
HH:mm:ss')

*         When calling select TO_CHAR (TS,'yyyy-MM-dd HH:mm:ss' ) from DATE_TEST  I am getting
the value that I inserted

*         When claling select * from DATE_TEST I am getting the data with offset

See screen shout from I SQuirrel


[cid:image001.jpg@01D003E4.437285B0]



Regards,
Noam
Information in this e-mail and its attachments is confidential and privileged under the TEOCO
confidentiality terms that can be reviewed here<http://www.teoco.com/email-disclaimer>.

Mime
View raw message