calcite-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Julian Hyde <jh...@apache.org>
Subject Re: a question about timestampadd month
Date Thu, 16 Feb 2017 16:51:03 GMT
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 | 马洪宾*


Mime
View raw message