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

CONVERT_TZ adds subsecond digits when converting from CHAR/VARCHAR column value

    XMLWordPrintable

    Details

    • Type: Bug
    • Status: Closed (View Workflow)
    • Priority: Minor
    • Resolution: Not a Bug
    • Affects Version/s: 5.5.48, 10.0.26, 10.1.18
    • Fix Version/s: N/A
    • Component/s: Temporal Types, Time zones
    • Labels:
      None
    • Environment:
      Linux

      Description

      When using CONVERT_TZ() to convert a date string stored in a CHAR or VARCHAR column the result is correct, but extra sub second digits are added. This does not happen when the argument is from a DATETIME column or is a string constant.

      Make sure that you have timezone tables populated, e.g. with

      mysql_tzinfo_to_sql /usr/share/zoneinfo | mysql -u root mysql
      

      Then execute the following to reproduce:

      CREATE TABLE `d` (
      `id` int(11) NOT NULL AUTO_INCREMENT,
      `d` datetime DEFAULT NULL,
      `v` varchar(100) DEFAULT NULL,
      PRIMARY KEY (`id`)
      );
      INSERT INTO d VALUES(NULL, "2016-10-31 14:04:57", "2016-10-31 14:04:57");
       
      SELECT CONVERT_TZ(d, 'UTC', 'Europe/Amsterdam') AS a,
      CONVERT_TZ(v, 'UTC', 'Europe/Amsterdam') AS b,
      CONVERT_TZ("2016-10-31 14:04:57", 'UTC', 'Europe/Amsterdam') AS c
      FROM d \G
      

      Result:

      *************************** 1. row ***************************
      a: 2016-10-31 15:04:57
      b: 2016-10-31 15:04:57.000000
      c: 2016-10-31 15:04:57
      

      Expected result: same for VARCHAR and string constant

      *************************** 1. row ***************************
      a: 2016-10-31 15:04:57
      b: 2016-10-31 15:04:57
      c: 2016-10-31 15:04:57
      

        Attachments

          Activity

            People

            Assignee:
            bar Alexander Barkov
            Reporter:
            hholzgra Hartmut Holzgraefe
            Votes:
            1 Vote for this issue
            Watchers:
            3 Start watching this issue

              Dates

              Created:
              Updated:
              Resolved:

                Git Integration