calcite-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From hongbin ma <mahong...@apache.org>
Subject Re: a question about timestampadd month
Date Fri, 17 Feb 2017 07:30:13 GMT
at sql spec side, I tried to find sth related in
http://jtc1sc32.org/doc/N2301-2350/32N2311T-text_for_ballot-CD_9075-2.pdf,
especially in section 4.6, but nothing is found. If you found sth pls let
me know

On Fri, Feb 17, 2017 at 3:24 PM, hongbin ma <mahongbin@apache.org> wrote:

> hi julian
>
> on postgres I tried timestamp + interval, because it seems timetampadd is
> not supported: https://www.postgresql.org/docs/9.1/
> static/functions-datetime.html
>
> JIRA title is updated
>
> On Fri, Feb 17, 2017 at 12:51 AM, Julian Hyde <jhyde@apache.org> wrote:
>
>> On Postgres did you try TIMESTAMPADD or did you try timestamp + interval?
>> I ask because if we fix this, we are going to have to change the behavior
>> of timestamp + interval.
>>
>> Also please change the JIRA case subject to something more descriptive.
>>
>> For the SQL standard, search for "ISO/IEC 9075-2” and you can find some
>> committee drafts. Not the final standard, but good enough for our purposes.
>>
>> Julian
>>
>> > On Feb 16, 2017, at 12:37 AM, hongbin ma <mahongbin@apache.org> wrote:
>> >
>> > Hi Julian
>> >
>> > Thanks for you reply.
>> >
>> > I have confirmed on postgresql, it behaves exactly as mysql.
>> >
>> > For ansi SQL 92 I could find http://www.contrib.andrew.cmu.
>> edu/~shadow/sql/
>> > sql1992.txt, however for SQL 2011 I can't find a counterpart source. Can
>> > you please kindly show me a link? just to make sure we're at same page.
>> >
>> >
>> >
>> > On Thu, Feb 16, 2017 at 2:29 PM, Julian Hyde <jhyde@apache.org> wrote:
>> >
>> >> Can you please log a JIRA case for this?
>> >>
>> >> It seems that we implement TIMESTAMPADD(unit, n, t) as “t + interval n
>> >> unit”. E.g. timestampadd(MONTH, 1, DATE ‘2016-05-31’) translates to
>> DATE
>> >> ‘2016-05-31’ + INTERVAL ‘1’ MONTH. Similarly TIMESTAMPDIFF.
>> >>
>> >> So, can you please investigate whether we got interval addition and
>> >> subtraction wrong too. I think you should check the SQL standard
>> >> (preferably SQL-2011 or SQL-2014 draft) and also test on PostgreSQL.
>> >>
>> >> Julian
>> >>
>> >>> On Feb 15, 2017, at 10:16 PM, hongbin ma <mahongbin@apache.org>
>> wrote:
>> >>>
>> >>> hi experts
>> >>>
>> >>> in calcite,
>> >>>
>> >>> select timestampadd(MONTH,1,cast('2016-05-31' as timestamp))  will
>> >>> return 2016-07-01 00:00:00, and select
>> >>> timestampadd(MONTH,-1,cast('2016-03-31' as timestamp))  will
>> >>> return 2016-03-01 00:00:00
>> >>>
>> >>> however in mysql, the last day of the next/previous month is always
>> >>> returned:
>> >>>
>> >>> *mysql> select timestampadd(MONTH,1,'2016-05-31') ;*
>> >>> *+------------------------------------+*
>> >>> *| timestampadd(MONTH,1,'2016-05-31') |*
>> >>> *+------------------------------------+*
>> >>> *| 2016-06-30                         |*
>> >>> *+------------------------------------+*
>> >>> *1 row in set (0.00 sec)*
>> >>>
>> >>> *mysql> select timestampadd(MONTH,-1,'2016-03-31') ;*
>> >>> *+-------------------------------------+*
>> >>> *| timestampadd(MONTH,-1,'2016-03-31') |*
>> >>> *+-------------------------------------+*
>> >>> *| 2016-02-29                          |*
>> >>> *+-------------------------------------+*
>> >>> *1 row in set (0.00 sec)*
>> >>>
>> >>> *mysql> *
>> >>>
>> >>> I checked ANSI SQL 92, seems there's no definition on this.
>> >>> Is this an issue we should concern? Looks like mysql's approach is
>> more
>> >>> straigthtforward.
>> >>>
>> >>>
>> >>> --
>> >>> Regards,
>> >>>
>> >>> *Bin Mahone | 马洪宾*
>> >>
>> >>
>> >
>> >
>> > --
>> > Regards,
>> >
>> > *Bin Mahone | 马洪宾*
>>
>>
>
>
> --
> Regards,
>
> *Bin Mahone | 马洪宾*
>



-- 
Regards,

*Bin Mahone | 马洪宾*

Mime
  • Unnamed multipart/alternative (inline, None, 0 bytes)
View raw message