phoenix-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Jeffrey Zhong <jzh...@hortonworks.com>
Subject Re: Time change when bulk load csv to phoenix
Date Tue, 10 Feb 2015 21:34:41 GMT

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 <gabriel.reid@gmail.com<mailto:gabriel.reid@gmail.com>>
Reply-To: "user@phoenix.apache.org<mailto:user@phoenix.apache.org>" <user@phoenix.apache.org<mailto:user@phoenix.apache.org>>
Date: Tuesday, February 10, 2015 at 3:13 AM
To: "user@phoenix.apache.org<mailto:user@phoenix.apache.org>" <user@phoenix.apache.org<mailto:user@phoenix.apache.org>>
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 <thanaphol.p@mindterra.com<mailto:thanaphol.p@mindterra.com>>
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: thanaphol.p@mindterra.com<mailto:sarawit@mindterra.com>
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]

Mime
View raw message