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

              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: