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

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

    XMLWordPrintable

    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

              People

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

                Dates

                Created:
                Updated:
                Resolved: