phoenix-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Bulvik, Noam" <>
Subject RE: timezone offset
Date Wed, 19 Nov 2014 18:15:40 GMT
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 []
Received: רביעי, 19 נוב 2014, 18:53
To: []
Subject: RE: timezone offset


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

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:

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

Tom Grayson

From: Bulvik, Noam []
Sent: Thursday, November 13, 2014 8:30 AM
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

·         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


Information in this e-mail and its attachments is confidential and privileged under the TEOCO
confidentiality terms that can be reviewed here<>.

View raw message