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> 
      

        Attachments

          Activity

            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: