phoenix-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Gabriel Reid <gabriel.r...@gmail.com>
Subject Re: Configuring phoenix.query.dateFormatTimeZone
Date Thu, 20 Aug 2015 19:44:16 GMT
I guess you could manipulate the Timestamp by adding an extra 3 hours to
it, and that would then give you the "expected" output in sqlline, but I
really wouldn't advise doing this, as it will likely lead to confusing
issues further down the line (e.g. if you query via JDBC or with another
query client).

- Gabriel


On Thu, Aug 20, 2015 at 1:44 PM Naor David <tron47@gmail.com> wrote:

> Thank you Gabriel for this excellent explanation, It's very clear now.
>
> However, is there a way overriding this behavior by storing the
> 2015-08-12T10:20:21.125+03:00 date as a TimeStamp corresponding to
> 2015-08-12 10:20:21.125 directly at Phoenix?
>
> Thanks
> בתאריך 18 באוג׳ 2015 21:48,‏ "Gabriel Reid" <gabriel.reid@gmail.com>
כתב:
>
> Ok, thanks for those snippets -- I think that's enough to explain what is
>> happening. The biggest cause for confusion here is probably the way that
>> sqlline retrieves values from a ResultSet, along with the built-in
>> formatting of Timestamps in Phoenix. Although most of the details below
>> probably aren't new, I'll lay it all out so that this can potentially be
>> useful for others.
>>
>> The Timestamp that you're creating is created around the long
>> value 1439364021125, which is the number of milliseconds between 1970-01-01
>> 00:00:00 GMT and 2015-08-12 07:20:21.125 GMT. The Timestamp itself has no
>> time zone information, it's basically just a wrapper around the long value.
>>
>> However, the toString() method on a java.sql.Timestamp object will format
>> the Timestamp in the client-local timezone. This isn't anything to do with
>> Phoenix, it's just how date/time types are implemented in the JDBC API. If
>> you print out that Timestamp in java code running in your local timezone,
>> you should see "2015-08-12 10:20:21.125".
>>
>> I believe Sqlline uses the getString() method to retrieve Timestamps
>> (along with other types) from a ResultSet. Retrieving a Timestamp as a
>> String in Phoenix will return it formatted in GMT. I believe that if you
>> were to do any of the following, you would get a local timezone
>> representation of the Timestamp (i.e. "2015-08-12 10:20:21.125") instead
>> of the GMT-based string representation:
>> * use SQuirreL SQL client (or possibly other JDBC clients) to display the
>> timestamp
>> * concatenate the timestamp with a string while selecting via sqlline,
>> like "SELECT ts || '' FROM mytable"
>> * fetch the timestamp yourself via JDBC and print it out
>>
>> I think that the main point here is that the Timestamp object itself
>> isn't (ever) linked to a specific time zone, but at the same time, it's
>> default formatting is linked to your local time zone. A case could
>> certainly be made for aligning the ResultSet.getString for Timestamps to
>> return the same thing as Timestamp.toString(), although in some ways it's
>> also handy to be able to view Timestamps as they are really stored in
>> Phoenix.
>>
>> - Gabriel
>>
>>
>>
>>
>>
>> On Tue, Aug 18, 2015 at 2:02 PM Naor David <tron47@gmail.com> wrote:
>>
>>>
>>> Hi Gabriel,
>>> Here is the code that I am executing:
>>> Timestamp ts=new Timestamp(new
>>> DateTime("2015-08-12T10:20:21.125+03:00").getMillis());
>>>
>>> And then I am upserting it to a PreparedStatement:
>>> ps.setObject(1,ts);
>>>
>>> The resulting value in sqlline is the timestamp:
>>> 2015-08-12 07:20:21.125
>>>
>>> Thanks
>>>
>>> If I'm understanding correctly, you're using DateTimeFormatter (from
>>> joda-time) to convert a String to a long, and then instantiating a
>>> java.sql.Timestamp from the long -- could you confirm that that is
>>> correct? And could you also explain how you're checking what is stored
>>> in Phoenix? Is that via sqlline or Squirrel or something similar, or
>>> your own JDBC code?
>>>
>>> Even better would be if you could write a little test class that
>>> demonstrates the issue that you're running into. My gut feeling is
>>> that things are working as intended and that it's timezone weirdness
>>> in the JDBC spec that is causing the issue, but I need to nail down
>>> your exact use case to verify this.
>>>
>>> - Gabriel
>>>
>>>
>>> On Sun, Aug 16, 2015 at 5:54 PM, Naor David <tron47@gmail.com> wrote:
>>> > I'm upserting timestamps by setting a java.sql.TimeStamp object to it's
>>> > proper index in my PreparedStatement object, so it,s something like
>>> this:
>>> > ps.setObject(1,ts);
>>> > By doing so I am not using the TO_DATE Phoenix function, nor am I
>>> parsing
>>> > the time samp as a String, so I think that setting the parameter at
>>> > hbase-site.xml wouldn't help..
>>> >
>>> > For example, if I insert the timestamp corresponding to 1-1-2015
>>> 10:00:00,
>>> > the inserted timestamp column would be 1-1-2015 07:00:00 and so on.
>>> > FYI, I use the DateTimeFormatter class for converting the date (which
>>> comes
>>> > with GMT+3 suffix) to a TimeStamp object as above for inserting the
>>> date as
>>> > a TimeStamp.
>>> >
>>> > - David
>>> >
>>> > בתאריך 14 באוג׳ 2015 16:31,‏ "Gabriel Reid" <gabriel.reid@gmail.com>
>>> כתב:
>>> >
>>> >> Hi David,
>>> >>
>>> >> How are you upserting timestamps? The phoenix.query.dateFormatTimeZone
>>> >> config property only affects string parsing or the TO_DATE function
>>> (docs on
>>> >> this are at [1]). If you're using the TO_DATE function, it's also
>>> possible
>>> >> to supply a custom time zone in the function call (docs on this are
>>> at [2]).
>>> >>
>>> >> Regardless, if you want to use this setting, you need to update the
>>> >> hbase-site.xml on the client machine where you're connecting to
>>> >> HBase/Phoenix. This configuration file will typically be in
>>> /etc/hbase/conf,
>>> >> although if you're using Cloudera Manager (or probably some other
>>> cluster
>>> >> management software) the hbase-site is automatically overwritten by
>>> CM, so
>>> >> you'll need to configure this within Cloudera Manager itself (via
>>> >> configuration settings called "Gateway safety-valve", or something
>>> along
>>> >> those lines).
>>> >>
>>> >> In any case, there are often issues due to the odd way in which JDBC
>>> >> itself handles (or doesn't handle) timezones, so the best way to
>>> resolve
>>> >> this issue is probably for you to post some examples of the statements
>>> >> you're running, what output you're getting, and what you would expect
>>> >> instead of what you're getting.
>>> >>
>>> >> - Gabriel
>>> >>
>>> >> 1. http://phoenix.apache.org/tuning.html
>>> >> 2. https://phoenix.apache.org/language/functions.html#to_date
>>> >>
>>> >> On Fri, Aug 14, 2015 at 1:59 PM Naor David <tron47@gmail.com>
wrote:
>>> >>>
>>> >>> Hello,
>>> >>> I recently installed Apache Pheonix 4.3 at a Cloudera cluster via
>>> parcel
>>> >>> installation.
>>> >>> My problem is that while inserting a java.sql.TimeStamp object via
>>> jdbc,
>>> >>> the corresponding inserted timestamp column is converted to GMT+0
>>> timezone.
>>> >>> (While my local time is GMT+3).
>>> >>> I understood that one can configure the Phoenix timezone by setting
>>> >>> phoenix.query.dateFormatTimeZone to the desired timezone.
>>> >>> My problem is that I don't know which hbase-site.xml should I edit
>>> (and
>>> >>> where can I find it).
>>> >>>
>>> >>> Any help would be appreciated.
>>> >>>
>>> >>> Regards,
>>> >>> David.
>>>
>>

Mime
View raw message