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

TIMESTAMP '<date>' returns ER_WRONG_VALUE (Incorrect DATETIME value)

    XMLWordPrintable

    Details

      Description

      On some reason, the following returns an error:

      10.2

      MariaDB [test]> select timestamp '2021-12-12';
      ERROR 1525 (HY000): Incorrect DATETIME value: '2021-12-12'
      MariaDB [test]> select timestamp '20211212';
      ERROR 1525 (HY000): Incorrect DATETIME value: '20211212'
      

      while this, for example, works:

      MariaDB [test]> select timestamp '2021-12-12 0';
      +--------------------------+
      | timestamp '2021-12-12 0' |
      +--------------------------+
      | 2021-12-12 00:00:00      |
      +--------------------------+
      1 row in set (0.000 sec)
      

      This also works

      MariaDB [test]> select cast('2021-12-12' as datetime);
      +--------------------------------+
      | cast('2021-12-12' as datetime) |
      +--------------------------------+
      | 2021-12-12 00:00:00            |
      +--------------------------------+
      1 row in set (0.000 sec)
      

      and naturally a date can be inserted into a timestamp/datetime column.

      Alexander Barkov said the error upon timestamp literal may be a bug, so I'm filing it so that it's at least tracked.

      I cannot find a definitive proof in documentation whether it should work or not. MySQL manual, while quite verbose, doesn't say explicitly whether it should work. It has, however, this note:

      Values specified as numbers should be 6, 8, 12, or 14 digits long. If a number is 8 or 14 digits long, it is assumed to be in YYYYMMDD or YYYYMMDDhhmmss format and that the year is given by the first 4 digits

      So, it appears that at least timestamp '20211212' should work (but it doesn't work in MySQL either).

      MariaDB documentation is very confusing, it doesn't help at all.

      One non-obvious effect is that in 10.7, --as-of option added to mysqldump in the scope of MDEV-16355 doesn't work with date-only values. It happens because the value is converted into a statement like

      SELECT * FROM <table> FOR SYSTEM_TIME AS OF TIMESTAMP '<value>';
      

        Attachments

          Activity

            People

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

              Dates

              Created:
              Updated:

                Git Integration

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