Details
-
Bug
-
Status: Closed (View Workflow)
-
Minor
-
Resolution: Not a Bug
-
5.5.48, 10.0.26, 10.1.18
-
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
|