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

Reply via email to