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 <mahong...@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 <jh...@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 <mahong...@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 <jh...@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 <mahong...@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 | 马洪宾*
