Uploaded image for project: 'Calcite'
  1. Calcite
  2. CALCITE-1639

TIMESTAMPADD(MONTH, ...) should return last day of month if the day overflows

    Details

    • Type: Bug
    • Status: Closed
    • Priority: Major
    • Resolution: Fixed
    • Affects Version/s: None
    • Fix Version/s: 1.13.0
    • Component/s: None
    • Labels:
      None

      Description

      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> 
      

        Activity

        Hide
        mahongbin hongbin ma added a comment -

        refined org.apache.calcite.runtime.SqlFunctions#addMonths(int, int) so that calcite handles day part overflow with the same way as mysql/pg, please help to review

        Show
        mahongbin hongbin ma added a comment - refined org.apache.calcite.runtime.SqlFunctions#addMonths(int, int) so that calcite handles day part overflow with the same way as mysql/pg, please help to review
        Hide
        mahongbin hongbin ma added a comment -

        During working on the issue I found an interesting constant: org.apache.calcite.avatica.util.DateTimeUtils#EPOCH_JULIAN, which seems to be related with 4800 BC. What is the retionale behind the design? I don't quite understand it

        Show
        mahongbin hongbin ma added a comment - During working on the issue I found an interesting constant: org.apache.calcite.avatica.util.DateTimeUtils#EPOCH_JULIAN, which seems to be related with 4800 BC. What is the retionale behind the design? I don't quite understand it
        Hide
        julianhyde Julian Hyde added a comment -

        Can you please convert this patch to a pull request? It's easier for us to retain original author, etc.

        The Julian epoch (nothing to do with me, BTW!) is the value of the Unix epoch (Jan 1st 1970) as a Julian date value. It makes it easier to convert from Unix dates (where Jan 1st 1970 = 0) to Julian dates (where Jan 1st 1970 = 2440588 and Jan 1st 4713 BC = 0). See http://aa.usno.navy.mil/data/docs/JulianDate.php. PostgreSQL also uses Julian dates internally, BTW.

        Show
        julianhyde Julian Hyde added a comment - Can you please convert this patch to a pull request? It's easier for us to retain original author, etc. The Julian epoch (nothing to do with me, BTW!) is the value of the Unix epoch (Jan 1st 1970) as a Julian date value. It makes it easier to convert from Unix dates (where Jan 1st 1970 = 0) to Julian dates (where Jan 1st 1970 = 2440588 and Jan 1st 4713 BC = 0). See http://aa.usno.navy.mil/data/docs/JulianDate.php . PostgreSQL also uses Julian dates internally, BTW.
        Hide
        mahongbin hongbin ma added a comment -

        Thanks for the clear explanation! Sorry I should have googled first, I thought it has something to do with "Julian Hyde" at first sight...

        Show
        mahongbin hongbin ma added a comment - Thanks for the clear explanation! Sorry I should have googled first, I thought it has something to do with "Julian Hyde" at first sight...
        Show
        mahongbin hongbin ma added a comment - Julian Hyde the PR is https://github.com/apache/calcite/pull/431/
        Hide
        julianhyde Julian Hyde added a comment -
        Show
        julianhyde Julian Hyde added a comment - Fixed in http://git-wip-us.apache.org/repos/asf/calcite/commit/0d2d11f3 . Thanks for the PR, hongbin ma !
        Hide
        jcamachorodriguez Jesus Camacho Rodriguez added a comment -

        Resolved in release 1.13.0 (2017-06-26).

        Show
        jcamachorodriguez Jesus Camacho Rodriguez added a comment - Resolved in release 1.13.0 (2017-06-26).

          People

          • Assignee:
            julianhyde Julian Hyde
            Reporter:
            mahongbin hongbin ma
          • Votes:
            0 Vote for this issue
            Watchers:
            3 Start watching this issue

            Dates

            • Created:
              Updated:
              Resolved:

              Development