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 Tue, 18 Aug 2015 18:48:25 GMT
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