calcite-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Julian Hyde <jh...@apache.org>
Subject Re: a question about timestampadd month
Date Thu, 16 Feb 2017 06:29:38 GMT
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 <mahongbin@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 | 马洪宾*


Mime
View raw message