If you have a SQL TIMESTAMP value and you want to pass it to a Java UDF, use this method.
For example, if you are in pacific TimeZone, if you have a value TIMESTAMP ‘1970-01-01 00:00:00’ (represented as long 0, and zone less) and you pass it to a UDF it will become a java.sql.Timestamp whose internal time field is 28,800,000, because that is the offset in milliseconds of 1970-01-01 00:00:00 Pacific from the Unix UTC epoch. If time zones change across machines, yes you will get the wrong result. In retrospect, converting zoneless to local time was probably not the right thing to do. But thankfully it only affects people who write UDFs. > On Jul 19, 2019, at 10:26 PM, Shuyi Chen <[email protected]> wrote: > > Thanks a lot for the quick response, Julian! So if I understand your answer > correctly, the input parameter *v* of internalToTimestamp() is NOT > milliseconds since epoch, and is defined as milliseconds since epoch + > LOCAL_TZ.getOffset(). And if we have a timestamp long value that are > milliseconds since epoch, which is timezone independent, it should not be > used with internalToTimestamp(), because it will get wrong result, and also > the wrong result will change as the local JVM timezone changes across > different machines. > > Shuyi > >> On Fri, Jul 19, 2019 at 8:38 PM Julian Hyde <[email protected]> wrote: >> >> Note that that function is for a specialized purpose: converting internal >> timestamps to java.sql.Timestamp objects to be passed as arguments to >> user-defined functions implemented in Java. >> >> It assumes that the SQL timestamp values are in the JVM’s default time >> zone, and since are converting to java.sql.Timestamp values, which are >> always relative to UTC epoch, we need to subtract the local timezone offset. >> >> (In other cases (e.g. sending values into or out of JDBC) we know that the >> values are milliseconds since epoch, but we let the caller tell us what >> timezone they think the timestamp values are in. Then we convert to a >> java.sql.Timestamp which is always relative to the UTC epoch.) >> >> Julian >> >> >>> On Jul 19, 2019, at 1:33 PM, Shuyi Chen <[email protected]> wrote: >>> >>> Hi all, >>> >>> I have a question regarding the usage & implementation of SqlFunctions. >>> internalToTimestamp(long v) (I copied the implementation below). I want >> to >>> clarify the definition of input parameter v. Is it milliseconds since >>> epoch, or something else? >>> >>> If it is milliseconds since epoch, why do we need to minus it with >> LOCAL_TZ >>> .getOffset(v)before passing it to the sql.Timestamp constructor. As >>> documented in the sql.Timestamp constructor, the constructor also takes >>> milliseconds since epoch. So it seems to me that the current >> implementation >>> is wrong unless I misunderstood the definition of input parameter v or I >>> don't have enough context. I am also happy to fix it or improve it if >> it's >>> a bug. Thanks a lot. >>> >>> public static java.sql.Timestamp internalToTimestamp(long v) { >>> return new java.sql.Timestamp(v - LOCAL_TZ.getOffset(v)); >>> } >>> >>> >>> Shuyi >> >>
