There is one trick to display the date back to GMT time zone is to use to_char function if you don't change the default time zone setting as following. The above code is running against the tip of 4.0 branch but to_char(t) should work in old releases as well)
0: jdbc:phoenix:localhost:58344> CREATE TABLE TABLE1 (ID INTEGER NOT NULL PRIMARY KEY, T DATE); No rows affected (0.258 seconds) 0: jdbc:phoenix:localhost:58344> upsert into TABLE1 values(1, '2015-02-09T02:54:51Z'); 1 row affected (0.063 seconds) 0: jdbc:phoenix:localhost:58344> select to_char(t) from table1; +------------------------------------------+ | TO_CHAR(T) | +------------------------------------------+ | 2015-02-09 02:54:51 | +------------------------------------------+ 1 row selected (0.017 seconds) From: Gabriel Reid <[email protected]<mailto:[email protected]>> Reply-To: "[email protected]<mailto:[email protected]>" <[email protected]<mailto:[email protected]>> Date: Tuesday, February 10, 2015 at 3:13 AM To: "[email protected]<mailto:[email protected]>" <[email protected]<mailto:[email protected]>> Subject: Re: Time change when bulk load csv to phoenix Hi Thanaphol, Could you elaborate on how you're debugging this issue? The reason I ask is that the JDBC Timestamp class does some of its own formatting when you query it as a string (it formats the string to a timestamp in the local timezone). The general rules are as follows: * the bulk loader interprets date/time/timestamp strings as being in GMT * Phoenix stores only the long value of the date/time/timestamp internally * JDBC date/time/timestamp values are instantiated based on the long value when you query them In other words, Phoenix treats everything internally as being in GMT. However, if you run a query on a timestamp column in a non-GMT locale and display the timestamps as strings, the string will be the local timezone representation of the GMT timestamp -- the internal long value will be the same, but the string representation will be different than the file that you imported. - Gabriel On Tue, Feb 10, 2015 at 11:14 AM, Thanaphol Prasitphaithoon <[email protected]<mailto:[email protected]>> wrote: Hi All I use phoenix 4.2.2 and I bulk load csv file to phoenix. When I check records on timestamp column, records in table are different from csv. It like phoenix convert GMT time zone to ICT Timezone(ICT is my timezone). GMT is default time zone on phoenix. Does Phoenix has parameter to specific default timezone or other solution to suggest me to do. -- Best Regards, Thanaphol Prasitphaithoon ========================= Information Security Engineer Service Department Mindterra Company Limited Tel: (+66) 2530 2062-4 Fax: (+66) 2530 2177<tel:%28%2B66%29%C2%A02530%202177> Mobile: (+66) 84-022-0683<tel:%28%2B66%29%2084-022-0683> E-mail: [email protected]<mailto:[email protected]> Website: http://www.mindterra.com<http://www.mindterra.com/> Interact Mindterra via Facebook<https://www.facebook.com/mindterra> and Twitter<https://twitter.com/mindterra> [http://www.mindterra.com/images/signature/mindterra-logo.png]
