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 | 马洪宾*
