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 <[email protected]> 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" <[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. >>> >>
