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 <[email protected]> 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 <[email protected]> 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 | 马洪宾*
