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