You are viewing a plain text version of this content. The canonical link for it is here.
Posted to dev@calcite.apache.org by Shuyi Chen <su...@gmail.com> on 2019/07/19 20:33:27 UTC
Question on SqlFunctions.internalToTimestamp()
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
Re: Question on SqlFunctions.internalToTimestamp()
Posted by Julian Hyde <jh...@gmail.com>.
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 <su...@gmail.com> 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 <jh...@apache.org> 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 <su...@gmail.com> 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
>>
>>
Re: Question on SqlFunctions.internalToTimestamp()
Posted by Shuyi Chen <su...@gmail.com>.
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 <jh...@apache.org> 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 <su...@gmail.com> 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
>
>
Re: Question on SqlFunctions.internalToTimestamp()
Posted by Julian Hyde <jh...@apache.org>.
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 <su...@gmail.com> 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