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" <[email protected]> כתב: > 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 <[email protected]> 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 <[email protected]> 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" <[email protected]> >> כתב: >> > >> >> 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 <[email protected]> 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. >> >
