[MDEV-11193] CONVERT_TZ adds subsecond digits when converting from CHAR/VARCHAR column value Created: 2016-10-31  Updated: 2017-02-20  Resolved: 2017-02-20

Status: Closed
Project: MariaDB Server
Component/s: Temporal Types, Time zones
Affects Version/s: 5.5.48, 10.0.26, 10.1.18
Fix Version/s: N/A

Type: Bug Priority: Minor
Reporter: Hartmut Holzgraefe Assignee: Alexander Barkov
Resolution: Not a Bug Votes: 1
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



 Comments   
Comment by Alexander Barkov [ 2016-12-15 ]

The version of the same script that does not need timezone tables to be populated:

DROP TABLE IF EXISTS t1;
 
CREATE TABLE t1 (
 
 id int(11) NOT NULL AUTO_INCREMENT,
 
 d datetime DEFAULT NULL,
 
 v varchar(100) DEFAULT NULL,
 
 PRIMARY KEY (`id`)
 
);
 
INSERT INTO t1 VALUES(NULL, "2016-10-31 14:04:57", "2016-10-31 14:04:57");
 
SELECT
 
  CONVERT_TZ(d, '+00:00', '+01:00') AS a,
 
  CONVERT_TZ(v, '+00:00', '+01:00') AS b,
 
  CONVERT_TZ("2016-10-31 14:04:57", '+00:00', '+01:00') AS c
 
FROM t1\G


a: 2016-10-31 15:04:57
 
b: 2016-10-31 15:04:57.000000
 
c: 2016-10-31 15:04:57
 
1 row in set (0.00 sec)

Note, it works this way by design.
In case of the DATETIME column and the string literal, the number of fractional digits is known.
In case of the VARCHAR column the number of fractional digits is not known, so it reserves 6 digits.
To get a desired number of digits from a VARCHAR column one needs to cast it to DATETIME(N), e.g.:

SELECT
 
  CONVERT_TZ(CAST(v AS DATETIME), '+00:00', '+01:00') AS a,
 
  CONVERT_TZ(CAST(v AS DATETIME(3)), '+00:00', '+01:00') AS b,
 
  CONVERT_TZ(CAST(v AS DATETIME(6)), '+00:00', '+01:00') AS c FROM t1\G


a: 2016-10-31 15:04:57
 
b: 2016-10-31 15:04:57.000
 
c: 2016-10-31 15:04:57.000000

Generated at Thu Feb 08 07:48:01 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.