Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-20397

Support TIMESTAMP, DATETIME, TIME in ROUND() and TRUNCATE()

Details

    Description

      As of versions 10.3.x and 10.4.7, functions ROUND() and TRUNCATE() convert temporal input to double and then return a rounded/truncated double value.

      This behaviour is quite useless, because can return '60' at the end:

      • ROUND() in double format can return '60' at the end by definition
      • TRUNCATE() in double format can return '60' at the end because of lack of precision:

        TRUNCATE(TIMESTAMP'2001-12-31 23:59:59.999999', 1);
        

        +----------------------------------------------------+
        | TRUNCATE(TIMESTAMP'2001-12-31 23:59:59.999999', 1) |
        +----------------------------------------------------+
        |                                   20011231235960.0 |
        +----------------------------------------------------+
        

      Therefore a rounded or truncated double value cannot be converted back to TIMESTAMP, DATETIME or TIME easily.

      In order to fix MDEV-18153 easier, let's implement native TIMESTAMP, DATETIME, TIME support in ROUND() and TRUNCATE().

      Let's change these functions to return:

      • TIME in case of a TIME input
      • DATETIME in case of a DATETIME input
      • DATETIME in case of a TIMESTAMP input

      Note, ROUND(timestamp) will return DATETIME for now. It should be fine in 99% cases, but won't work well near DST changes. Let's implement real native TIMESTAMP support in ROUND() in a separate change later.

      Note, the DATE data type is out of scope of this task. ROUND() and TRUNCATE() will still return a double result for a DATE input. Native DATE support for ROUND() and TRUNCATE() will be implemented separately, when needed.

      Attachments

        Issue Links

          Activity

            There are no comments yet on this issue.

            People

              bar Alexander Barkov
              bar Alexander Barkov
              Votes:
              0 Vote for this issue
              Watchers:
              2 Start watching this issue

              Dates

                Created:
                Updated:
                Resolved:

                Git Integration

                  Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.