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

    • Bug
    • Status: Closed (View Workflow)
    • Minor
    • Resolution: Not a Bug
    • 5.5.48, 10.0.26, 10.1.18
    • N/A
    • Temporal Types, Time zones
    • None
    • 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

            bar Alexander Barkov
            hholzgra Hartmut Holzgraefe
            Votes:
            1 Vote for this issue
            Watchers:
            3 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.