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

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

VotersWatch issueWatchersLinkCloneUpdate Comment AuthorReplace String in CommentUpdate Comment VisibilityDelete Comments
    XMLWordPrintableJSON

Details

    • Bug
    • Status: Closed
    • Major
    • Resolution: Fixed
    • None
    • 1.13.0
    • None
    • 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

          This comment will be Viewable by All Users Viewable by All Users
          Cancel

          People

            julianhyde Julian Hyde
            mahongbin Hongbin Ma
            Votes:
            0 Vote for this issue
            Watchers:
            3 Start watching this issue

            Dates

              Created:
              Updated:
              Resolved:

              Slack

                Issue deployment