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:
For what it’s worth I posted a similar question back in August to which no one replied:
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.
· 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
Information in this e-mail and its attachments is confidential and privileged under the TEOCO confidentiality terms that can be reviewed here.