You are viewing a plain text version of this content. The canonical link for it is here.
Posted to dev@calcite.apache.org by Rui Wang <ru...@google.com.INVALID> on 2018/11/16 18:15:24 UTC
Syntax of TIMESTAMP [WITHOUT TIME ZONE], TIMESTAMP WITH LOCAL TIME
ZONE and TIMESTAMP WITH TIME ZONE in Calcite 1.17
Hi Calcite community:
I am using Calcite 1.17.0 and testing the syntax of constructing TIMESTAMP
[WITHOUT TIME ZONE], TIMESTAMP WITH LOCAL TIME ZONE and TIMESTAMP WITH TIME
ZONE in SELECTION. For example, I am doing tests like "SELECT TIMESTAMP
'2017-12-31 23:59:59'".
I can construct TIMESTAMP [WITHOUT TIME ZONE] without any problem. However,
I am facing troubles to construct others.
Per Calcite's website, this syntax is available:
TIMESTAMP '2017-12-31 23:59:59 America/Los Angeles'
However, my test failed with Illegal TIMESTAMP literal '2017-12-31
23:59:59 America/Los Angeles': not in format 'yyyy-MM-dd HH:mm:ss'.
I further tested
TIMESTAMP '2017-12-31 23:59:59Z'
TIMESTAMP_WITH_TIME_ZONE '2017-12-31 23:59:59 America/Los Angeles'
TIMESTAMP WITH TIME ZONE '2017-12-31 23:59:59 America/Los Angeles'
TIMESTAMP WITH LOCAL TIME ZONE '2017-12-31 23:59:59 America/Los Angeles'
TIMESTAMP_WITH_LOCAL_TIME_ZONE '2017-12-31 23:59:59 America/Los Angeles'
Neither one succeeded.
Is there anyone who happen to know what's the right way to construct
timestamp with time zones? Any help would be appreciated.
-Rui
Re: Syntax of TIMESTAMP [WITHOUT TIME ZONE], TIMESTAMP WITH LOCAL
TIME ZONE and TIMESTAMP WITH TIME ZONE in Calcite 1.17
Posted by Julian Hyde <jh...@apache.org>.
Maybe you should review the fix for CALCITE-1947, which added this support.
Also review SqlParserTest, which has a test for TIMESTAMP '2004-12-01 12:01:01.010234567890’ but not anything like "TIMESTAMP '2004-12-01 12:01:01 US/Pacific’.
> On Nov 16, 2018, at 12:39 PM, Rui Wang <ru...@google.com.INVALID> wrote:
>
> Thanks Julian.
>
> Seems like Oracle's syntax supports:
>
> TIMESTAMP '1997-01-31 09:26:56.66 +02:00'
>
> TIMESTAMP '1999-01-15 8:00:00 US/Pacific'
>
> TIMESTAMP '1997-01-31 09:26:50.12'
>
> However, all these syntax failed with parser exception: not in format
> 'yyyy-MM-dd HH:mm:ss'
>
> Also, based on Oracle examples, Calcite doc[1] is not really out of date.
>
> -Rui
>
> [1] https://calcite.apache.org/docs/reference.html#scalar-types
>
> On Fri, Nov 16, 2018 at 10:52 AM Julian Hyde <jh...@apache.org> wrote:
>
>> We aim to be consistent with Oracle[1]. Did you try Oracle's syntax?
>>
>> Based on what you say, I guess our doc[2] is out of date.
>>
>> Julian
>>
>> [1] https://docs.oracle.com/cd/B19306_01/server.102/b14225/ch4datetime.htm
>>
>> [2] https://calcite.apache.org/docs/reference.html#scalar-types
>> On Fri, Nov 16, 2018 at 10:15 AM Rui Wang <ru...@google.com.invalid>
>> wrote:
>>>
>>> Hi Calcite community:
>>>
>>> I am using Calcite 1.17.0 and testing the syntax of constructing
>> TIMESTAMP
>>> [WITHOUT TIME ZONE], TIMESTAMP WITH LOCAL TIME ZONE and TIMESTAMP WITH
>> TIME
>>> ZONE in SELECTION. For example, I am doing tests like "SELECT TIMESTAMP
>>> '2017-12-31 23:59:59'".
>>>
>>> I can construct TIMESTAMP [WITHOUT TIME ZONE] without any problem.
>> However,
>>> I am facing troubles to construct others.
>>>
>>> Per Calcite's website, this syntax is available:
>>>
>>> TIMESTAMP '2017-12-31 23:59:59 America/Los Angeles'
>>>
>>> However, my test failed with Illegal TIMESTAMP literal '2017-12-31
>>> 23:59:59 America/Los Angeles': not in format 'yyyy-MM-dd HH:mm:ss'.
>>>
>>>
>>> I further tested
>>>
>>> TIMESTAMP '2017-12-31 23:59:59Z'
>>>
>>> TIMESTAMP_WITH_TIME_ZONE '2017-12-31 23:59:59 America/Los Angeles'
>>>
>>> TIMESTAMP WITH TIME ZONE '2017-12-31 23:59:59 America/Los Angeles'
>>>
>>> TIMESTAMP WITH LOCAL TIME ZONE '2017-12-31 23:59:59 America/Los Angeles'
>>>
>>> TIMESTAMP_WITH_LOCAL_TIME_ZONE '2017-12-31 23:59:59 America/Los Angeles'
>>>
>>> Neither one succeeded.
>>>
>>>
>>> Is there anyone who happen to know what's the right way to construct
>>> timestamp with time zones? Any help would be appreciated.
>>>
>>> -Rui
>>
Re: Syntax of TIMESTAMP [WITHOUT TIME ZONE], TIMESTAMP WITH LOCAL
TIME ZONE and TIMESTAMP WITH TIME ZONE in Calcite 1.17
Posted by Rui Wang <ru...@google.com.INVALID>.
Thanks Julian.
Seems like Oracle's syntax supports:
TIMESTAMP '1997-01-31 09:26:56.66 +02:00'
TIMESTAMP '1999-01-15 8:00:00 US/Pacific'
TIMESTAMP '1997-01-31 09:26:50.12'
However, all these syntax failed with parser exception: not in format
'yyyy-MM-dd HH:mm:ss'
Also, based on Oracle examples, Calcite doc[1] is not really out of date.
-Rui
[1] https://calcite.apache.org/docs/reference.html#scalar-types
On Fri, Nov 16, 2018 at 10:52 AM Julian Hyde <jh...@apache.org> wrote:
> We aim to be consistent with Oracle[1]. Did you try Oracle's syntax?
>
> Based on what you say, I guess our doc[2] is out of date.
>
> Julian
>
> [1] https://docs.oracle.com/cd/B19306_01/server.102/b14225/ch4datetime.htm
>
> [2] https://calcite.apache.org/docs/reference.html#scalar-types
> On Fri, Nov 16, 2018 at 10:15 AM Rui Wang <ru...@google.com.invalid>
> wrote:
> >
> > Hi Calcite community:
> >
> > I am using Calcite 1.17.0 and testing the syntax of constructing
> TIMESTAMP
> > [WITHOUT TIME ZONE], TIMESTAMP WITH LOCAL TIME ZONE and TIMESTAMP WITH
> TIME
> > ZONE in SELECTION. For example, I am doing tests like "SELECT TIMESTAMP
> > '2017-12-31 23:59:59'".
> >
> > I can construct TIMESTAMP [WITHOUT TIME ZONE] without any problem.
> However,
> > I am facing troubles to construct others.
> >
> > Per Calcite's website, this syntax is available:
> >
> > TIMESTAMP '2017-12-31 23:59:59 America/Los Angeles'
> >
> > However, my test failed with Illegal TIMESTAMP literal '2017-12-31
> > 23:59:59 America/Los Angeles': not in format 'yyyy-MM-dd HH:mm:ss'.
> >
> >
> > I further tested
> >
> > TIMESTAMP '2017-12-31 23:59:59Z'
> >
> > TIMESTAMP_WITH_TIME_ZONE '2017-12-31 23:59:59 America/Los Angeles'
> >
> > TIMESTAMP WITH TIME ZONE '2017-12-31 23:59:59 America/Los Angeles'
> >
> > TIMESTAMP WITH LOCAL TIME ZONE '2017-12-31 23:59:59 America/Los Angeles'
> >
> > TIMESTAMP_WITH_LOCAL_TIME_ZONE '2017-12-31 23:59:59 America/Los Angeles'
> >
> > Neither one succeeded.
> >
> >
> > Is there anyone who happen to know what's the right way to construct
> > timestamp with time zones? Any help would be appreciated.
> >
> > -Rui
>
Re: Syntax of TIMESTAMP [WITHOUT TIME ZONE], TIMESTAMP WITH LOCAL
TIME ZONE and TIMESTAMP WITH TIME ZONE in Calcite 1.17
Posted by Julian Hyde <jh...@apache.org>.
We aim to be consistent with Oracle[1]. Did you try Oracle's syntax?
Based on what you say, I guess our doc[2] is out of date.
Julian
[1] https://docs.oracle.com/cd/B19306_01/server.102/b14225/ch4datetime.htm
[2] https://calcite.apache.org/docs/reference.html#scalar-types
On Fri, Nov 16, 2018 at 10:15 AM Rui Wang <ru...@google.com.invalid> wrote:
>
> Hi Calcite community:
>
> I am using Calcite 1.17.0 and testing the syntax of constructing TIMESTAMP
> [WITHOUT TIME ZONE], TIMESTAMP WITH LOCAL TIME ZONE and TIMESTAMP WITH TIME
> ZONE in SELECTION. For example, I am doing tests like "SELECT TIMESTAMP
> '2017-12-31 23:59:59'".
>
> I can construct TIMESTAMP [WITHOUT TIME ZONE] without any problem. However,
> I am facing troubles to construct others.
>
> Per Calcite's website, this syntax is available:
>
> TIMESTAMP '2017-12-31 23:59:59 America/Los Angeles'
>
> However, my test failed with Illegal TIMESTAMP literal '2017-12-31
> 23:59:59 America/Los Angeles': not in format 'yyyy-MM-dd HH:mm:ss'.
>
>
> I further tested
>
> TIMESTAMP '2017-12-31 23:59:59Z'
>
> TIMESTAMP_WITH_TIME_ZONE '2017-12-31 23:59:59 America/Los Angeles'
>
> TIMESTAMP WITH TIME ZONE '2017-12-31 23:59:59 America/Los Angeles'
>
> TIMESTAMP WITH LOCAL TIME ZONE '2017-12-31 23:59:59 America/Los Angeles'
>
> TIMESTAMP_WITH_LOCAL_TIME_ZONE '2017-12-31 23:59:59 America/Los Angeles'
>
> Neither one succeeded.
>
>
> Is there anyone who happen to know what's the right way to construct
> timestamp with time zones? Any help would be appreciated.
>
> -Rui