phoenix-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Naor David <tro...@gmail.com>
Subject Re: Configuring phoenix.query.dateFormatTimeZone
Date Thu, 20 Aug 2015 11:44:00 GMT
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