Thanks for the detailed info,
I think that Poenix should support disabling timezone conversion in the 
connection string, or disable it on their implementation.
I use Oracle and impala and both of them does not do that.

-----Original Message-----
From: Thomas Grayson [[email protected]]
Received: רביעי, 19 נוב 2014, 18:53
To: [email protected] [[email protected]]
Subject: RE: timezone offset

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:[email protected]]
Sent: Thursday, November 13, 2014 8:30 AM
To: [email protected]
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:[email protected]]



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>.

Reply via email to