SET sql_mode=IF(@@version LIKE '%MariaDB%', 'TIME_ROUND_FRACTIONAL', '');
SET @default_sql_mode=@@sql_mode;
CREATE TABLE t1_datetime_in_varchar (id SERIAL, a VARCHAR(64));
INSERT INTO t1_datetime_in_varchar (a) VALUES
('2000-12-31 23:59:59'),
('2000-12-31 23:59:59.9'),
('2000-12-31 23:59:59.99'),
('2000-12-31 23:59:59.999'),
('2000-12-31 23:59:59.9999'),
('2000-12-31 23:59:59.99999'),
('2000-12-31 23:59:59.999999'),
('2000-12-31 23:59:59.9999999');
CREATE TABLE t1_datetime_in_decimal (id SERIAL, a DECIMAL(38,10));
INSERT INTO t1_datetime_in_decimal (a) VALUES
(20001231235959),
(20001231235959.9),
(20001231235959.99),
(20001231235959.999),
(20001231235959.9999),
(20001231235959.99999),
(20001231235959.999999),
(20001231235959.9999999);
CREATE TABLE t1_time_in_varchar (id SERIAL, a VARCHAR(64));
INSERT INTO t1_time_in_varchar (a) VALUES
('00:00:00'),
('00:00:00.9'),
('00:00:00.99'),
('00:00:00.999'),
('00:00:00.9999'),
('00:00:00.99999'),
('00:00:00.999999'),
('00:00:00.9999999');
INSERT INTO t1_time_in_varchar (a) VALUES
('837:59:59.9999999'),
('838:59:59'),
('838:59:59.9'),
('838:59:59.99'),
('838:59:59.999'),
('838:59:59.9999'),
('838:59:59.99999'),
('838:59:59.999999'),
('838:59:59.9999999'),
('839:59:59.9999999'),
('87649414:59:59.999999'),
('87649414:59:59.9999999'),
('87649415:59:59.999999'),
('87649415:59:59.9999999');
CREATE TABLE t1_time_in_decimal (id SERIAL, a DECIMAL(38,10));
INSERT INTO t1_time_in_decimal (a) VALUES
(0),
(0.9),
(0.99),
(0.999),
(0.9999),
(0.99999),
(0.999999),
(0.9999999);
INSERT INTO t1_time_in_decimal (a) VALUES
(8375959.9999999),
(8385959),
(8385959.9),
(8385959.99),
(8385959.999),
(8385959.9999),
(8385959.99999),
(8385959.999999),
(8385959.9999999),
(8395959.9999999),
(876494145959.999999),
(876494145959.9999999),
(876494155959.999999),
(876494155959.9999999);
#
# TIME: LEAST/GREATEST
#
SELECT GREATEST(TIME'00:00:00', a) FROM t1_time_in_varchar;
GREATEST(TIME'00:00:00', a)
00:00:00.000000
00:00:00.900000
00:00:00.990000
00:00:00.999000
00:00:00.999900
00:00:00.999990
00:00:00.999999
00:00:01.000000
838:00:00.000000
838:59:59.000000
838:59:59.900000
838:59:59.990000
838:59:59.999000
838:59:59.999900
838:59:59.999990
838:59:59.999999
838:59:59.999999
838:59:59.999999
838:59:59.999999
838:59:59.999999
838:59:59.999999
838:59:59.999999
Warnings:
Note	1292	Truncated incorrect time value: '00:00:00.9999999'
Note	1292	Truncated incorrect time value: '837:59:59.9999999'
Warning	1292	Truncated incorrect time value: '838:59:59.9999999'
Warning	1292	Truncated incorrect time value: '839:59:59.9999999'
Warning	1292	Truncated incorrect time value: '87649414:59:59.999999'
Warning	1292	Truncated incorrect time value: '87649414:59:59.9999999'
Warning	1292	Truncated incorrect time value: '87649415:59:59.999999'
Warning	1292	Truncated incorrect time value: '87649415:59:59.9999999'
SELECT GREATEST(TIME'00:00:00', a) FROM t1_time_in_decimal;
GREATEST(TIME'00:00:00', a)
00:00:00.000000
00:00:00.900000
00:00:00.990000
00:00:00.999000
00:00:00.999900
00:00:00.999990
00:00:00.999999
00:00:01.000000
838:00:00.000000
838:59:59.000000
838:59:59.900000
838:59:59.990000
838:59:59.999000
838:59:59.999900
838:59:59.999990
838:59:59.999999
838:59:59.999999
838:59:59.999999
NULL
NULL
NULL
NULL
Warnings:
Warning	1292	Incorrect time value: '8385959.9999999000' for column `test`.`t1_time_in_decimal`.`a` at row 17
Warning	1292	Incorrect time value: '8395959.9999999000' for column `test`.`t1_time_in_decimal`.`a` at row 18
Warning	1292	Incorrect time value: '876494145959.9999990000' for column `test`.`t1_time_in_decimal`.`a` at row 19
Warning	1292	Incorrect time value: '876494145959.9999999000' for column `test`.`t1_time_in_decimal`.`a` at row 20
Warning	1292	Incorrect time value: '876494155959.9999990000' for column `test`.`t1_time_in_decimal`.`a` at row 21
Warning	1292	Incorrect time value: '876494155959.9999999000' for column `test`.`t1_time_in_decimal`.`a` at row 22
SELECT GREATEST(TIME'00:00:00', '00:00:00.0000004');
GREATEST(TIME'00:00:00', '00:00:00.0000004')
00:00:00.000000
Warnings:
Note	1292	Truncated incorrect time value: '00:00:00.0000004'
SELECT GREATEST(TIME'00:00:00', 0.0000004);
GREATEST(TIME'00:00:00', 0.0000004)
00:00:00.000000
SELECT GREATEST(TIME'00:00:00', '00:00:00.0000005');
GREATEST(TIME'00:00:00', '00:00:00.0000005')
00:00:00.000001
Warnings:
Note	1292	Truncated incorrect time value: '00:00:00.0000005'
SELECT GREATEST(TIME'00:00:00', 0.0000005);
GREATEST(TIME'00:00:00', 0.0000005)
00:00:00.000001
#
# Functions with a single TIME input, conversion from DATETIME-in-VARCHAR
#
SELECT SECOND(a), CAST(a AS TIME(6)), a FROM t1_datetime_in_varchar ORDER BY id;
SECOND(a)	CAST(a AS TIME(6))	a
59	23:59:59.000000	2000-12-31 23:59:59
59	23:59:59.900000	2000-12-31 23:59:59.9
59	23:59:59.990000	2000-12-31 23:59:59.99
59	23:59:59.999000	2000-12-31 23:59:59.999
59	23:59:59.999900	2000-12-31 23:59:59.9999
59	23:59:59.999990	2000-12-31 23:59:59.99999
59	23:59:59.999999	2000-12-31 23:59:59.999999
0	00:00:00.000000	2000-12-31 23:59:59.9999999
Warnings:
Note	1292	Truncated incorrect datetime value: '2000-12-31 23:59:59.9999999'
Note	1292	Truncated incorrect datetime value: '2000-12-31 23:59:59.9999999'
SELECT MINUTE(a), CAST(a AS TIME(6)), a FROM t1_datetime_in_varchar ORDER BY id;
MINUTE(a)	CAST(a AS TIME(6))	a
59	23:59:59.000000	2000-12-31 23:59:59
59	23:59:59.900000	2000-12-31 23:59:59.9
59	23:59:59.990000	2000-12-31 23:59:59.99
59	23:59:59.999000	2000-12-31 23:59:59.999
59	23:59:59.999900	2000-12-31 23:59:59.9999
59	23:59:59.999990	2000-12-31 23:59:59.99999
59	23:59:59.999999	2000-12-31 23:59:59.999999
0	00:00:00.000000	2000-12-31 23:59:59.9999999
Warnings:
Note	1292	Truncated incorrect datetime value: '2000-12-31 23:59:59.9999999'
Note	1292	Truncated incorrect datetime value: '2000-12-31 23:59:59.9999999'
SELECT HOUR(a), CAST(a AS TIME(6)), a FROM t1_datetime_in_varchar ORDER BY id;
HOUR(a)	CAST(a AS TIME(6))	a
23	23:59:59.000000	2000-12-31 23:59:59
23	23:59:59.900000	2000-12-31 23:59:59.9
23	23:59:59.990000	2000-12-31 23:59:59.99
23	23:59:59.999000	2000-12-31 23:59:59.999
23	23:59:59.999900	2000-12-31 23:59:59.9999
23	23:59:59.999990	2000-12-31 23:59:59.99999
23	23:59:59.999999	2000-12-31 23:59:59.999999
0	00:00:00.000000	2000-12-31 23:59:59.9999999
Warnings:
Note	1292	Truncated incorrect datetime value: '2000-12-31 23:59:59.9999999'
Note	1292	Truncated incorrect datetime value: '2000-12-31 23:59:59.9999999'
SELECT EXTRACT(SECOND FROM a), CAST(a AS TIME(6)), a FROM t1_datetime_in_varchar ORDER BY id;
EXTRACT(SECOND FROM a)	CAST(a AS TIME(6))	a
59	23:59:59.000000	2000-12-31 23:59:59
59	23:59:59.900000	2000-12-31 23:59:59.9
59	23:59:59.990000	2000-12-31 23:59:59.99
59	23:59:59.999000	2000-12-31 23:59:59.999
59	23:59:59.999900	2000-12-31 23:59:59.9999
59	23:59:59.999990	2000-12-31 23:59:59.99999
59	23:59:59.999999	2000-12-31 23:59:59.999999
0	00:00:00.000000	2000-12-31 23:59:59.9999999
Warnings:
Note	1292	Truncated incorrect datetime value: '2000-12-31 23:59:59.9999999'
Note	1292	Truncated incorrect datetime value: '2000-12-31 23:59:59.9999999'
SELECT EXTRACT(MINUTE FROM a), CAST(a AS TIME(6)), a FROM t1_datetime_in_varchar ORDER BY id;
EXTRACT(MINUTE FROM a)	CAST(a AS TIME(6))	a
59	23:59:59.000000	2000-12-31 23:59:59
59	23:59:59.900000	2000-12-31 23:59:59.9
59	23:59:59.990000	2000-12-31 23:59:59.99
59	23:59:59.999000	2000-12-31 23:59:59.999
59	23:59:59.999900	2000-12-31 23:59:59.9999
59	23:59:59.999990	2000-12-31 23:59:59.99999
59	23:59:59.999999	2000-12-31 23:59:59.999999
0	00:00:00.000000	2000-12-31 23:59:59.9999999
Warnings:
Note	1292	Truncated incorrect datetime value: '2000-12-31 23:59:59.9999999'
Note	1292	Truncated incorrect datetime value: '2000-12-31 23:59:59.9999999'
SELECT EXTRACT(HOUR FROM a), CAST(a AS TIME(6)), a FROM t1_datetime_in_varchar ORDER BY id;
EXTRACT(HOUR FROM a)	CAST(a AS TIME(6))	a
23	23:59:59.000000	2000-12-31 23:59:59
23	23:59:59.900000	2000-12-31 23:59:59.9
23	23:59:59.990000	2000-12-31 23:59:59.99
23	23:59:59.999000	2000-12-31 23:59:59.999
23	23:59:59.999900	2000-12-31 23:59:59.9999
23	23:59:59.999990	2000-12-31 23:59:59.99999
23	23:59:59.999999	2000-12-31 23:59:59.999999
0	00:00:00.000000	2000-12-31 23:59:59.9999999
Warnings:
Note	1292	Truncated incorrect datetime value: '2000-12-31 23:59:59.9999999'
Note	1292	Truncated incorrect datetime value: '2000-12-31 23:59:59.9999999'
SELECT TIME_TO_SEC(a), CAST(a AS TIME(6)), a FROM t1_datetime_in_varchar ORDER BY id;
TIME_TO_SEC(a)	CAST(a AS TIME(6))	a
86399.000000	23:59:59.000000	2000-12-31 23:59:59
86399.900000	23:59:59.900000	2000-12-31 23:59:59.9
86399.990000	23:59:59.990000	2000-12-31 23:59:59.99
86399.999000	23:59:59.999000	2000-12-31 23:59:59.999
86399.999900	23:59:59.999900	2000-12-31 23:59:59.9999
86399.999990	23:59:59.999990	2000-12-31 23:59:59.99999
86399.999999	23:59:59.999999	2000-12-31 23:59:59.999999
0.000000	00:00:00.000000	2000-12-31 23:59:59.9999999
Warnings:
Note	1292	Truncated incorrect datetime value: '2000-12-31 23:59:59.9999999'
Note	1292	Truncated incorrect datetime value: '2000-12-31 23:59:59.9999999'
#
# Functions with a single TIME input, conversion from DATETIME-in-DECIMAL
#
SELECT SECOND(a), CAST(a AS TIME(6)), a FROM t1_datetime_in_decimal ORDER BY id;
SECOND(a)	CAST(a AS TIME(6))	a
59	23:59:59.000000	20001231235959.0000000000
59	23:59:59.900000	20001231235959.9000000000
59	23:59:59.990000	20001231235959.9900000000
59	23:59:59.999000	20001231235959.9990000000
59	23:59:59.999900	20001231235959.9999000000
59	23:59:59.999990	20001231235959.9999900000
59	23:59:59.999999	20001231235959.9999990000
0	00:00:00.000000	20001231235959.9999999000
SELECT MINUTE(a), CAST(a AS TIME(6)), a FROM t1_datetime_in_decimal ORDER BY id;
MINUTE(a)	CAST(a AS TIME(6))	a
59	23:59:59.000000	20001231235959.0000000000
59	23:59:59.900000	20001231235959.9000000000
59	23:59:59.990000	20001231235959.9900000000
59	23:59:59.999000	20001231235959.9990000000
59	23:59:59.999900	20001231235959.9999000000
59	23:59:59.999990	20001231235959.9999900000
59	23:59:59.999999	20001231235959.9999990000
0	00:00:00.000000	20001231235959.9999999000
SELECT HOUR(a), CAST(a AS TIME(6)), a FROM t1_datetime_in_decimal ORDER BY id;
HOUR(a)	CAST(a AS TIME(6))	a
23	23:59:59.000000	20001231235959.0000000000
23	23:59:59.900000	20001231235959.9000000000
23	23:59:59.990000	20001231235959.9900000000
23	23:59:59.999000	20001231235959.9990000000
23	23:59:59.999900	20001231235959.9999000000
23	23:59:59.999990	20001231235959.9999900000
23	23:59:59.999999	20001231235959.9999990000
0	00:00:00.000000	20001231235959.9999999000
SELECT EXTRACT(SECOND FROM a), CAST(a AS TIME(6)), a FROM t1_datetime_in_decimal ORDER BY id;
EXTRACT(SECOND FROM a)	CAST(a AS TIME(6))	a
59	23:59:59.000000	20001231235959.0000000000
59	23:59:59.900000	20001231235959.9000000000
59	23:59:59.990000	20001231235959.9900000000
59	23:59:59.999000	20001231235959.9990000000
59	23:59:59.999900	20001231235959.9999000000
59	23:59:59.999990	20001231235959.9999900000
59	23:59:59.999999	20001231235959.9999990000
0	00:00:00.000000	20001231235959.9999999000
SELECT EXTRACT(MINUTE FROM a), CAST(a AS TIME(6)), a FROM t1_datetime_in_decimal ORDER BY id;
EXTRACT(MINUTE FROM a)	CAST(a AS TIME(6))	a
59	23:59:59.000000	20001231235959.0000000000
59	23:59:59.900000	20001231235959.9000000000
59	23:59:59.990000	20001231235959.9900000000
59	23:59:59.999000	20001231235959.9990000000
59	23:59:59.999900	20001231235959.9999000000
59	23:59:59.999990	20001231235959.9999900000
59	23:59:59.999999	20001231235959.9999990000
0	00:00:00.000000	20001231235959.9999999000
SELECT EXTRACT(HOUR FROM a), CAST(a AS TIME(6)), a FROM t1_datetime_in_decimal ORDER BY id;
EXTRACT(HOUR FROM a)	CAST(a AS TIME(6))	a
23	23:59:59.000000	20001231235959.0000000000
23	23:59:59.900000	20001231235959.9000000000
23	23:59:59.990000	20001231235959.9900000000
23	23:59:59.999000	20001231235959.9990000000
23	23:59:59.999900	20001231235959.9999000000
23	23:59:59.999990	20001231235959.9999900000
23	23:59:59.999999	20001231235959.9999990000
0	00:00:00.000000	20001231235959.9999999000
SELECT TIME_TO_SEC(a), CAST(a AS TIME(6)), a FROM t1_datetime_in_decimal ORDER BY id;
TIME_TO_SEC(a)	CAST(a AS TIME(6))	a
86399.000000	23:59:59.000000	20001231235959.0000000000
86399.900000	23:59:59.900000	20001231235959.9000000000
86399.990000	23:59:59.990000	20001231235959.9900000000
86399.999000	23:59:59.999000	20001231235959.9990000000
86399.999900	23:59:59.999900	20001231235959.9999000000
86399.999990	23:59:59.999990	20001231235959.9999900000
86399.999999	23:59:59.999999	20001231235959.9999990000
0.000000	00:00:00.000000	20001231235959.9999999000
#
# Functions with a single TIME interval input, conversion from TIME-interval-in-VARCHAR
#
SELECT
EXTRACT(DAY FROM a),
EXTRACT(HOUR FROM a),
EXTRACT(MINUTE FROM a),
EXTRACT(SECOND FROM a),
EXTRACT(MICROSECOND FROM a),
CAST(a AS INTERVAL DAY_SECOND(6)),
a
FROM t1_time_in_varchar ORDER BY id;
EXTRACT(DAY FROM a)	EXTRACT(HOUR FROM a)	EXTRACT(MINUTE FROM a)	EXTRACT(SECOND FROM a)	EXTRACT(MICROSECOND FROM a)	CAST(a AS INTERVAL DAY_SECOND(6))	a
0	0	0	0	0	00:00:00.000000	00:00:00
0	0	0	0	900000	00:00:00.900000	00:00:00.9
0	0	0	0	990000	00:00:00.990000	00:00:00.99
0	0	0	0	999000	00:00:00.999000	00:00:00.999
0	0	0	0	999900	00:00:00.999900	00:00:00.9999
0	0	0	0	999990	00:00:00.999990	00:00:00.99999
0	0	0	0	999999	00:00:00.999999	00:00:00.999999
0	0	0	1	0	00:00:01.000000	00:00:00.9999999
34	22	0	0	0	34 22:00:00.000000	837:59:59.9999999
34	22	59	59	0	34 22:59:59.000000	838:59:59
34	22	59	59	900000	34 22:59:59.900000	838:59:59.9
34	22	59	59	990000	34 22:59:59.990000	838:59:59.99
34	22	59	59	999000	34 22:59:59.999000	838:59:59.999
34	22	59	59	999900	34 22:59:59.999900	838:59:59.9999
34	22	59	59	999990	34 22:59:59.999990	838:59:59.99999
34	22	59	59	999999	34 22:59:59.999999	838:59:59.999999
34	23	0	0	0	34 23:00:00.000000	838:59:59.9999999
35	0	0	0	0	35 00:00:00.000000	839:59:59.9999999
3652058	22	59	59	999999	3652058 22:59:59.999999	87649414:59:59.999999
3652058	23	0	0	0	3652058 23:00:00.000000	87649414:59:59.9999999
3652058	23	59	59	999999	3652058 23:59:59.999999	87649415:59:59.999999
3652058	23	59	59	999999	3652058 23:59:59.999999	87649415:59:59.9999999
Warnings:
Note	1292	Truncated incorrect time value: '00:00:00.9999999'
Note	1292	Truncated incorrect time value: '00:00:00.9999999'
Note	1292	Truncated incorrect time value: '00:00:00.9999999'
Note	1292	Truncated incorrect time value: '00:00:00.9999999'
Note	1292	Truncated incorrect time value: '00:00:00.9999999'
Note	1292	Truncated incorrect INTERVAL DAY TO SECOND value: '00:00:00.9999999'
Note	1292	Truncated incorrect time value: '837:59:59.9999999'
Note	1292	Truncated incorrect time value: '837:59:59.9999999'
Note	1292	Truncated incorrect time value: '837:59:59.9999999'
Note	1292	Truncated incorrect time value: '837:59:59.9999999'
Note	1292	Truncated incorrect time value: '837:59:59.9999999'
Note	1292	Truncated incorrect INTERVAL DAY TO SECOND value: '837:59:59.9999999'
Note	1292	Truncated incorrect time value: '838:59:59.9999999'
Note	1292	Truncated incorrect time value: '838:59:59.9999999'
Note	1292	Truncated incorrect time value: '838:59:59.9999999'
Note	1292	Truncated incorrect time value: '838:59:59.9999999'
Note	1292	Truncated incorrect time value: '838:59:59.9999999'
Note	1292	Truncated incorrect INTERVAL DAY TO SECOND value: '838:59:59.9999999'
Note	1292	Truncated incorrect time value: '839:59:59.9999999'
Note	1292	Truncated incorrect time value: '839:59:59.9999999'
Note	1292	Truncated incorrect time value: '839:59:59.9999999'
Note	1292	Truncated incorrect time value: '839:59:59.9999999'
Note	1292	Truncated incorrect time value: '839:59:59.9999999'
Note	1292	Truncated incorrect INTERVAL DAY TO SECOND value: '839:59:59.9999999'
Note	1292	Truncated incorrect time value: '87649414:59:59.9999999'
Note	1292	Truncated incorrect time value: '87649414:59:59.9999999'
Note	1292	Truncated incorrect time value: '87649414:59:59.9999999'
Note	1292	Truncated incorrect time value: '87649414:59:59.9999999'
Note	1292	Truncated incorrect time value: '87649414:59:59.9999999'
Note	1292	Truncated incorrect INTERVAL DAY TO SECOND value: '87649414:59:59.9999999'
Warning	1292	Truncated incorrect time value: '87649415:59:59.9999999'
Warning	1292	Truncated incorrect time value: '87649415:59:59.9999999'
Warning	1292	Truncated incorrect time value: '87649415:59:59.9999999'
Warning	1292	Truncated incorrect time value: '87649415:59:59.9999999'
Warning	1292	Truncated incorrect time value: '87649415:59:59.9999999'
Warning	1292	Incorrect INTERVAL DAY TO SECOND value: '87649415:59:59.9999999'
SELECT
TIME_TO_SEC(a),
CAST(a AS TIME(6)),
a
FROM t1_time_in_varchar ORDER BY id;
TIME_TO_SEC(a)	CAST(a AS TIME(6))	a
0.000000	00:00:00.000000	00:00:00
0.900000	00:00:00.900000	00:00:00.9
0.990000	00:00:00.990000	00:00:00.99
0.999000	00:00:00.999000	00:00:00.999
0.999900	00:00:00.999900	00:00:00.9999
0.999990	00:00:00.999990	00:00:00.99999
0.999999	00:00:00.999999	00:00:00.999999
1.000000	00:00:01.000000	00:00:00.9999999
3016800.000000	838:00:00.000000	837:59:59.9999999
3020399.000000	838:59:59.000000	838:59:59
3020399.900000	838:59:59.900000	838:59:59.9
3020399.990000	838:59:59.990000	838:59:59.99
3020399.999000	838:59:59.999000	838:59:59.999
3020399.999900	838:59:59.999900	838:59:59.9999
3020399.999990	838:59:59.999990	838:59:59.99999
3020399.999999	838:59:59.999999	838:59:59.999999
3020399.999999	838:59:59.999999	838:59:59.9999999
3020399.999999	838:59:59.999999	839:59:59.9999999
3020399.999999	838:59:59.999999	87649414:59:59.999999
3020399.999999	838:59:59.999999	87649414:59:59.9999999
3020399.999999	838:59:59.999999	87649415:59:59.999999
3020399.999999	838:59:59.999999	87649415:59:59.9999999
Warnings:
Note	1292	Truncated incorrect time value: '00:00:00.9999999'
Note	1292	Truncated incorrect time value: '00:00:00.9999999'
Note	1292	Truncated incorrect time value: '837:59:59.9999999'
Note	1292	Truncated incorrect time value: '837:59:59.9999999'
Warning	1292	Truncated incorrect time value: '838:59:59.9999999'
Warning	1292	Truncated incorrect time value: '838:59:59.9999999'
Warning	1292	Truncated incorrect time value: '839:59:59.9999999'
Warning	1292	Truncated incorrect time value: '839:59:59.9999999'
Warning	1292	Truncated incorrect time value: '87649414:59:59.999999'
Warning	1292	Truncated incorrect time value: '87649414:59:59.999999'
Warning	1292	Truncated incorrect time value: '87649414:59:59.9999999'
Warning	1292	Truncated incorrect time value: '87649414:59:59.9999999'
Warning	1292	Truncated incorrect time value: '87649415:59:59.999999'
Warning	1292	Truncated incorrect time value: '87649415:59:59.999999'
Warning	1292	Truncated incorrect time value: '87649415:59:59.9999999'
Warning	1292	Truncated incorrect time value: '87649415:59:59.9999999'
#
# Functions with a single TIME interval input, conversion from TIME-interval-in-DECIMAL
#
SELECT
EXTRACT(DAY FROM a),
EXTRACT(HOUR FROM a),
EXTRACT(MINUTE FROM a),
EXTRACT(SECOND FROM a),
EXTRACT(MICROSECOND FROM a),
CAST(a AS INTERVAL DAY_SECOND(6)),
a
FROM t1_time_in_decimal ORDER BY id;
EXTRACT(DAY FROM a)	EXTRACT(HOUR FROM a)	EXTRACT(MINUTE FROM a)	EXTRACT(SECOND FROM a)	EXTRACT(MICROSECOND FROM a)	CAST(a AS INTERVAL DAY_SECOND(6))	a
0	0	0	0	0	00:00:00.000000	0.0000000000
0	0	0	0	900000	00:00:00.900000	0.9000000000
0	0	0	0	990000	00:00:00.990000	0.9900000000
0	0	0	0	999000	00:00:00.999000	0.9990000000
0	0	0	0	999900	00:00:00.999900	0.9999000000
0	0	0	0	999990	00:00:00.999990	0.9999900000
0	0	0	0	999999	00:00:00.999999	0.9999990000
0	0	0	1	0	00:00:01.000000	0.9999999000
34	22	0	0	0	34 22:00:00.000000	8375959.9999999000
34	22	59	59	0	34 22:59:59.000000	8385959.0000000000
34	22	59	59	900000	34 22:59:59.900000	8385959.9000000000
34	22	59	59	990000	34 22:59:59.990000	8385959.9900000000
34	22	59	59	999000	34 22:59:59.999000	8385959.9990000000
34	22	59	59	999900	34 22:59:59.999900	8385959.9999000000
34	22	59	59	999990	34 22:59:59.999990	8385959.9999900000
34	22	59	59	999999	34 22:59:59.999999	8385959.9999990000
34	23	0	0	0	34 23:00:00.000000	8385959.9999999000
35	0	0	0	0	35 00:00:00.000000	8395959.9999999000
3652058	22	59	59	999999	3652058 22:59:59.999999	876494145959.9999990000
3652058	23	0	0	0	3652058 23:00:00.000000	876494145959.9999999000
3652058	23	59	59	999999	3652058 23:59:59.999999	876494155959.9999990000
3652058	23	59	59	999999	3652058 23:59:59.999999	876494155959.9999999000
Warnings:
Note	1292	Truncated incorrect INTERVAL DAY TO SECOND value: '0.0000000000'
Note	1292	Truncated incorrect INTERVAL DAY TO SECOND value: '0.9000000000'
Note	1292	Truncated incorrect INTERVAL DAY TO SECOND value: '0.9900000000'
Note	1292	Truncated incorrect INTERVAL DAY TO SECOND value: '0.9990000000'
Note	1292	Truncated incorrect INTERVAL DAY TO SECOND value: '0.9999000000'
Note	1292	Truncated incorrect INTERVAL DAY TO SECOND value: '0.9999900000'
Note	1292	Truncated incorrect INTERVAL DAY TO SECOND value: '0.9999990000'
Note	1292	Truncated incorrect INTERVAL DAY TO SECOND value: '0.9999999000'
Note	1292	Truncated incorrect INTERVAL DAY TO SECOND value: '8375959.9999999000'
Note	1292	Truncated incorrect INTERVAL DAY TO SECOND value: '8385959.0000000000'
Note	1292	Truncated incorrect INTERVAL DAY TO SECOND value: '8385959.9000000000'
Note	1292	Truncated incorrect INTERVAL DAY TO SECOND value: '8385959.9900000000'
Note	1292	Truncated incorrect INTERVAL DAY TO SECOND value: '8385959.9990000000'
Note	1292	Truncated incorrect INTERVAL DAY TO SECOND value: '8385959.9999000000'
Note	1292	Truncated incorrect INTERVAL DAY TO SECOND value: '8385959.9999900000'
Note	1292	Truncated incorrect INTERVAL DAY TO SECOND value: '8385959.9999990000'
Note	1292	Truncated incorrect INTERVAL DAY TO SECOND value: '8385959.9999999000'
Note	1292	Truncated incorrect INTERVAL DAY TO SECOND value: '8395959.9999999000'
Note	1292	Truncated incorrect INTERVAL DAY TO SECOND value: '876494145959.9999990000'
Note	1292	Truncated incorrect INTERVAL DAY TO SECOND value: '876494145959.9999999000'
Note	1292	Truncated incorrect INTERVAL DAY TO SECOND value: '876494155959.9999990000'
Warning	1292	Incorrect time value: '876494155959.9999999000' for column `test`.`t1_time_in_decimal`.`a` at row 22
Warning	1292	Incorrect time value: '876494155959.9999999000' for column `test`.`t1_time_in_decimal`.`a` at row 22
Warning	1292	Incorrect time value: '876494155959.9999999000' for column `test`.`t1_time_in_decimal`.`a` at row 22
Warning	1292	Incorrect time value: '876494155959.9999999000' for column `test`.`t1_time_in_decimal`.`a` at row 22
Warning	1292	Incorrect time value: '876494155959.9999999000' for column `test`.`t1_time_in_decimal`.`a` at row 22
Warning	1292	Incorrect INTERVAL DAY TO SECOND value: '876494155959.9999999000'
SELECT
TIME_TO_SEC(a),
CAST(a AS TIME(6)),
a
FROM t1_time_in_decimal ORDER BY id;
TIME_TO_SEC(a)	CAST(a AS TIME(6))	a
0.000000	00:00:00.000000	0.0000000000
0.900000	00:00:00.900000	0.9000000000
0.990000	00:00:00.990000	0.9900000000
0.999000	00:00:00.999000	0.9990000000
0.999900	00:00:00.999900	0.9999000000
0.999990	00:00:00.999990	0.9999900000
0.999999	00:00:00.999999	0.9999990000
1.000000	00:00:01.000000	0.9999999000
3016800.000000	838:00:00.000000	8375959.9999999000
3020399.000000	838:59:59.000000	8385959.0000000000
3020399.900000	838:59:59.900000	8385959.9000000000
3020399.990000	838:59:59.990000	8385959.9900000000
3020399.999000	838:59:59.999000	8385959.9990000000
3020399.999900	838:59:59.999900	8385959.9999000000
3020399.999990	838:59:59.999990	8385959.9999900000
3020399.999999	838:59:59.999999	8385959.9999990000
3020399.999999	838:59:59.999999	8385959.9999999000
3020399.999999	838:59:59.999999	8395959.9999999000
NULL	NULL	876494145959.9999990000
NULL	NULL	876494145959.9999999000
NULL	NULL	876494155959.9999990000
NULL	NULL	876494155959.9999999000
Warnings:
Warning	1292	Incorrect time value: '8385959.9999999000' for column `test`.`t1_time_in_decimal`.`a` at row 17
Warning	1292	Incorrect time value: '8385959.9999999000' for column `test`.`t1_time_in_decimal`.`a` at row 17
Warning	1292	Incorrect time value: '8395959.9999999000' for column `test`.`t1_time_in_decimal`.`a` at row 18
Warning	1292	Incorrect time value: '8395959.9999999000' for column `test`.`t1_time_in_decimal`.`a` at row 18
Warning	1292	Incorrect time value: '876494145959.9999990000' for column `test`.`t1_time_in_decimal`.`a` at row 19
Warning	1292	Incorrect time value: '876494145959.9999990000' for column `test`.`t1_time_in_decimal`.`a` at row 19
Warning	1292	Incorrect time value: '876494145959.9999999000' for column `test`.`t1_time_in_decimal`.`a` at row 20
Warning	1292	Incorrect time value: '876494145959.9999999000' for column `test`.`t1_time_in_decimal`.`a` at row 20
Warning	1292	Incorrect time value: '876494155959.9999990000' for column `test`.`t1_time_in_decimal`.`a` at row 21
Warning	1292	Incorrect time value: '876494155959.9999990000' for column `test`.`t1_time_in_decimal`.`a` at row 21
Warning	1292	Incorrect time value: '876494155959.9999999000' for column `test`.`t1_time_in_decimal`.`a` at row 22
Warning	1292	Incorrect time value: '876494155959.9999999000' for column `test`.`t1_time_in_decimal`.`a` at row 22
#
# Functions with a single DATE input, conversion from DATETIME-in-VARCHAR
#
SELECT QUARTER(a), CAST(a AS DATE),  a FROM t1_datetime_in_varchar ORDER BY id;
QUARTER(a)	CAST(a AS DATE)	a
4	2000-12-31	2000-12-31 23:59:59
4	2000-12-31	2000-12-31 23:59:59.9
4	2000-12-31	2000-12-31 23:59:59.99
4	2000-12-31	2000-12-31 23:59:59.999
4	2000-12-31	2000-12-31 23:59:59.9999
4	2000-12-31	2000-12-31 23:59:59.99999
4	2000-12-31	2000-12-31 23:59:59.999999
1	2000-12-31	2000-12-31 23:59:59.9999999
Warnings:
Note	1292	Truncated incorrect datetime value: '2000-12-31 23:59:59.9999999'
Note	1292	Truncated incorrect datetime value: '2000-12-31 23:59:59.9999999'
SELECT DAY(a), CAST(a AS DATE), a FROM t1_datetime_in_varchar ORDER BY id;
DAY(a)	CAST(a AS DATE)	a
31	2000-12-31	2000-12-31 23:59:59
31	2000-12-31	2000-12-31 23:59:59.9
31	2000-12-31	2000-12-31 23:59:59.99
31	2000-12-31	2000-12-31 23:59:59.999
31	2000-12-31	2000-12-31 23:59:59.9999
31	2000-12-31	2000-12-31 23:59:59.99999
31	2000-12-31	2000-12-31 23:59:59.999999
1	2000-12-31	2000-12-31 23:59:59.9999999
Warnings:
Note	1292	Truncated incorrect datetime value: '2000-12-31 23:59:59.9999999'
Note	1292	Truncated incorrect datetime value: '2000-12-31 23:59:59.9999999'
SELECT MONTH(a), CAST(a AS DATE), a FROM t1_datetime_in_varchar ORDER BY id;
MONTH(a)	CAST(a AS DATE)	a
12	2000-12-31	2000-12-31 23:59:59
12	2000-12-31	2000-12-31 23:59:59.9
12	2000-12-31	2000-12-31 23:59:59.99
12	2000-12-31	2000-12-31 23:59:59.999
12	2000-12-31	2000-12-31 23:59:59.9999
12	2000-12-31	2000-12-31 23:59:59.99999
12	2000-12-31	2000-12-31 23:59:59.999999
1	2000-12-31	2000-12-31 23:59:59.9999999
Warnings:
Note	1292	Truncated incorrect datetime value: '2000-12-31 23:59:59.9999999'
Note	1292	Truncated incorrect datetime value: '2000-12-31 23:59:59.9999999'
SELECT YEAR(a), CAST(a AS DATE), a FROM t1_datetime_in_varchar ORDER BY id;
YEAR(a)	CAST(a AS DATE)	a
2000	2000-12-31	2000-12-31 23:59:59
2000	2000-12-31	2000-12-31 23:59:59.9
2000	2000-12-31	2000-12-31 23:59:59.99
2000	2000-12-31	2000-12-31 23:59:59.999
2000	2000-12-31	2000-12-31 23:59:59.9999
2000	2000-12-31	2000-12-31 23:59:59.99999
2000	2000-12-31	2000-12-31 23:59:59.999999
2001	2000-12-31	2000-12-31 23:59:59.9999999
Warnings:
Note	1292	Truncated incorrect datetime value: '2000-12-31 23:59:59.9999999'
Note	1292	Truncated incorrect datetime value: '2000-12-31 23:59:59.9999999'
SELECT DAYNAME(a), CAST(a AS DATE), a FROM t1_datetime_in_varchar ORDER BY id;
DAYNAME(a)	CAST(a AS DATE)	a
Sunday	2000-12-31	2000-12-31 23:59:59
Sunday	2000-12-31	2000-12-31 23:59:59.9
Sunday	2000-12-31	2000-12-31 23:59:59.99
Sunday	2000-12-31	2000-12-31 23:59:59.999
Sunday	2000-12-31	2000-12-31 23:59:59.9999
Sunday	2000-12-31	2000-12-31 23:59:59.99999
Sunday	2000-12-31	2000-12-31 23:59:59.999999
Monday	2000-12-31	2000-12-31 23:59:59.9999999
Warnings:
Note	1292	Truncated incorrect datetime value: '2000-12-31 23:59:59.9999999'
Note	1292	Truncated incorrect datetime value: '2000-12-31 23:59:59.9999999'
SELECT MONTHNAME(a), CAST(a AS DATE), a FROM t1_datetime_in_varchar ORDER BY id;
MONTHNAME(a)	CAST(a AS DATE)	a
December	2000-12-31	2000-12-31 23:59:59
December	2000-12-31	2000-12-31 23:59:59.9
December	2000-12-31	2000-12-31 23:59:59.99
December	2000-12-31	2000-12-31 23:59:59.999
December	2000-12-31	2000-12-31 23:59:59.9999
December	2000-12-31	2000-12-31 23:59:59.99999
December	2000-12-31	2000-12-31 23:59:59.999999
January	2000-12-31	2000-12-31 23:59:59.9999999
Warnings:
Note	1292	Truncated incorrect datetime value: '2000-12-31 23:59:59.9999999'
Note	1292	Truncated incorrect datetime value: '2000-12-31 23:59:59.9999999'
SELECT LAST_DAY(a), CAST(a AS DATE), a FROM t1_datetime_in_varchar ORDER BY id;
LAST_DAY(a)	CAST(a AS DATE)	a
2000-12-31	2000-12-31	2000-12-31 23:59:59
2000-12-31	2000-12-31	2000-12-31 23:59:59.9
2000-12-31	2000-12-31	2000-12-31 23:59:59.99
2000-12-31	2000-12-31	2000-12-31 23:59:59.999
2000-12-31	2000-12-31	2000-12-31 23:59:59.9999
2000-12-31	2000-12-31	2000-12-31 23:59:59.99999
2000-12-31	2000-12-31	2000-12-31 23:59:59.999999
2000-12-31	2000-12-31	2000-12-31 23:59:59.9999999
Warnings:
Note	1292	Truncated incorrect datetime value: '2000-12-31 23:59:59.9999999'
Note	1292	Truncated incorrect datetime value: '2000-12-31 23:59:59.9999999'
SELECT TO_DAYS(a), CAST(a AS DATE), a FROM t1_datetime_in_varchar ORDER BY id;
TO_DAYS(a)	CAST(a AS DATE)	a
730850	2000-12-31	2000-12-31 23:59:59
730850	2000-12-31	2000-12-31 23:59:59.9
730850	2000-12-31	2000-12-31 23:59:59.99
730850	2000-12-31	2000-12-31 23:59:59.999
730850	2000-12-31	2000-12-31 23:59:59.9999
730850	2000-12-31	2000-12-31 23:59:59.99999
730850	2000-12-31	2000-12-31 23:59:59.999999
730851	2000-12-31	2000-12-31 23:59:59.9999999
Warnings:
Note	1292	Truncated incorrect datetime value: '2000-12-31 23:59:59.9999999'
Note	1292	Truncated incorrect datetime value: '2000-12-31 23:59:59.9999999'
SELECT DAYOFYEAR(a), CAST(a AS DATE), a FROM t1_datetime_in_varchar ORDER BY id;
DAYOFYEAR(a)	CAST(a AS DATE)	a
366	2000-12-31	2000-12-31 23:59:59
366	2000-12-31	2000-12-31 23:59:59.9
366	2000-12-31	2000-12-31 23:59:59.99
366	2000-12-31	2000-12-31 23:59:59.999
366	2000-12-31	2000-12-31 23:59:59.9999
366	2000-12-31	2000-12-31 23:59:59.99999
366	2000-12-31	2000-12-31 23:59:59.999999
1	2000-12-31	2000-12-31 23:59:59.9999999
Warnings:
Note	1292	Truncated incorrect datetime value: '2000-12-31 23:59:59.9999999'
Note	1292	Truncated incorrect datetime value: '2000-12-31 23:59:59.9999999'
CREATE TABLE t1 (a VARCHAR(32));
INSERT INTO t1 VALUES
('2002-01-05 23:59:59'),
('2002-01-05 23:59:59.999999'),
('2002-01-05 23:59:59.9999999');
SELECT YEARWEEK(a), a FROM t1;
YEARWEEK(a)	a
200152	2002-01-05 23:59:59
200152	2002-01-05 23:59:59.999999
200201	2002-01-05 23:59:59.9999999
Warnings:
Note	1292	Truncated incorrect datetime value: '2002-01-05 23:59:59.9999999'
SELECT WEEK(a), a FROM t1;
WEEK(a)	a
0	2002-01-05 23:59:59
0	2002-01-05 23:59:59.999999
1	2002-01-05 23:59:59.9999999
Warnings:
Note	1292	Truncated incorrect datetime value: '2002-01-05 23:59:59.9999999'
SELECT WEEKDAY(a), a FROM t1;
WEEKDAY(a)	a
5	2002-01-05 23:59:59
5	2002-01-05 23:59:59.999999
6	2002-01-05 23:59:59.9999999
Warnings:
Note	1292	Truncated incorrect datetime value: '2002-01-05 23:59:59.9999999'
DROP TABLE t1;
CREATE TABLE t1 (a DECIMAL(32,9));
INSERT INTO t1 VALUES
(20020105235959),
(20020105235959.999999),
(20020105235959.9999999);
SELECT YEARWEEK(a), a FROM t1;
YEARWEEK(a)	a
200152	20020105235959.000000000
200152	20020105235959.999999000
200201	20020105235959.999999900
SELECT WEEK(a), a FROM t1;
WEEK(a)	a
0	20020105235959.000000000
0	20020105235959.999999000
1	20020105235959.999999900
SELECT WEEKDAY(a), a FROM t1;
WEEKDAY(a)	a
5	20020105235959.000000000
5	20020105235959.999999000
6	20020105235959.999999900
DROP TABLE t1;
#
# Functions with a single DATE input, conversion from DATETIME-in-DECIMAL
#
SELECT QUARTER(a), CAST(a AS DATE),  a FROM t1_datetime_in_decimal ORDER BY id;
QUARTER(a)	CAST(a AS DATE)	a
4	2000-12-31	20001231235959.0000000000
4	2000-12-31	20001231235959.9000000000
4	2000-12-31	20001231235959.9900000000
4	2000-12-31	20001231235959.9990000000
4	2000-12-31	20001231235959.9999000000
4	2000-12-31	20001231235959.9999900000
4	2000-12-31	20001231235959.9999990000
1	2000-12-31	20001231235959.9999999000
SELECT DAY(a), CAST(a AS DATE), a FROM t1_datetime_in_decimal ORDER BY id;
DAY(a)	CAST(a AS DATE)	a
31	2000-12-31	20001231235959.0000000000
31	2000-12-31	20001231235959.9000000000
31	2000-12-31	20001231235959.9900000000
31	2000-12-31	20001231235959.9990000000
31	2000-12-31	20001231235959.9999000000
31	2000-12-31	20001231235959.9999900000
31	2000-12-31	20001231235959.9999990000
1	2000-12-31	20001231235959.9999999000
SELECT MONTH(a), CAST(a AS DATE), a FROM t1_datetime_in_decimal ORDER BY id;
MONTH(a)	CAST(a AS DATE)	a
12	2000-12-31	20001231235959.0000000000
12	2000-12-31	20001231235959.9000000000
12	2000-12-31	20001231235959.9900000000
12	2000-12-31	20001231235959.9990000000
12	2000-12-31	20001231235959.9999000000
12	2000-12-31	20001231235959.9999900000
12	2000-12-31	20001231235959.9999990000
1	2000-12-31	20001231235959.9999999000
SELECT YEAR(a), CAST(a AS DATE), a FROM t1_datetime_in_decimal ORDER BY id;
YEAR(a)	CAST(a AS DATE)	a
2000	2000-12-31	20001231235959.0000000000
2000	2000-12-31	20001231235959.9000000000
2000	2000-12-31	20001231235959.9900000000
2000	2000-12-31	20001231235959.9990000000
2000	2000-12-31	20001231235959.9999000000
2000	2000-12-31	20001231235959.9999900000
2000	2000-12-31	20001231235959.9999990000
2001	2000-12-31	20001231235959.9999999000
SELECT DAYNAME(a), CAST(a AS DATE), a FROM t1_datetime_in_decimal ORDER BY id;
DAYNAME(a)	CAST(a AS DATE)	a
Sunday	2000-12-31	20001231235959.0000000000
Sunday	2000-12-31	20001231235959.9000000000
Sunday	2000-12-31	20001231235959.9900000000
Sunday	2000-12-31	20001231235959.9990000000
Sunday	2000-12-31	20001231235959.9999000000
Sunday	2000-12-31	20001231235959.9999900000
Sunday	2000-12-31	20001231235959.9999990000
Monday	2000-12-31	20001231235959.9999999000
SELECT MONTHNAME(a), CAST(a AS DATE), a FROM t1_datetime_in_decimal ORDER BY id;
MONTHNAME(a)	CAST(a AS DATE)	a
December	2000-12-31	20001231235959.0000000000
December	2000-12-31	20001231235959.9000000000
December	2000-12-31	20001231235959.9900000000
December	2000-12-31	20001231235959.9990000000
December	2000-12-31	20001231235959.9999000000
December	2000-12-31	20001231235959.9999900000
December	2000-12-31	20001231235959.9999990000
January	2000-12-31	20001231235959.9999999000
SELECT YEARWEEK(a), CAST(a AS DATE), a FROM t1_datetime_in_decimal ORDER BY id;
YEARWEEK(a)	CAST(a AS DATE)	a
200053	2000-12-31	20001231235959.0000000000
200053	2000-12-31	20001231235959.9000000000
200053	2000-12-31	20001231235959.9900000000
200053	2000-12-31	20001231235959.9990000000
200053	2000-12-31	20001231235959.9999000000
200053	2000-12-31	20001231235959.9999900000
200053	2000-12-31	20001231235959.9999990000
200053	2000-12-31	20001231235959.9999999000
SELECT LAST_DAY(a), CAST(a AS DATE), a FROM t1_datetime_in_decimal ORDER BY id;
LAST_DAY(a)	CAST(a AS DATE)	a
2000-12-31	2000-12-31	20001231235959.0000000000
2000-12-31	2000-12-31	20001231235959.9000000000
2000-12-31	2000-12-31	20001231235959.9900000000
2000-12-31	2000-12-31	20001231235959.9990000000
2000-12-31	2000-12-31	20001231235959.9999000000
2000-12-31	2000-12-31	20001231235959.9999900000
2000-12-31	2000-12-31	20001231235959.9999990000
2000-12-31	2000-12-31	20001231235959.9999999000
SELECT TO_DAYS(a), CAST(a AS DATE), a FROM t1_datetime_in_decimal ORDER BY id;
TO_DAYS(a)	CAST(a AS DATE)	a
730850	2000-12-31	20001231235959.0000000000
730850	2000-12-31	20001231235959.9000000000
730850	2000-12-31	20001231235959.9900000000
730850	2000-12-31	20001231235959.9990000000
730850	2000-12-31	20001231235959.9999000000
730850	2000-12-31	20001231235959.9999900000
730850	2000-12-31	20001231235959.9999990000
730851	2000-12-31	20001231235959.9999999000
SELECT DAYOFYEAR(a), CAST(a AS DATE), a FROM t1_datetime_in_decimal ORDER BY id;
DAYOFYEAR(a)	CAST(a AS DATE)	a
366	2000-12-31	20001231235959.0000000000
366	2000-12-31	20001231235959.9000000000
366	2000-12-31	20001231235959.9900000000
366	2000-12-31	20001231235959.9990000000
366	2000-12-31	20001231235959.9999000000
366	2000-12-31	20001231235959.9999900000
366	2000-12-31	20001231235959.9999990000
1	2000-12-31	20001231235959.9999999000
SELECT DAYOFMONTH(a), CAST(a AS DATE), a FROM t1_datetime_in_decimal ORDER BY id;
DAYOFMONTH(a)	CAST(a AS DATE)	a
31	2000-12-31	20001231235959.0000000000
31	2000-12-31	20001231235959.9000000000
31	2000-12-31	20001231235959.9900000000
31	2000-12-31	20001231235959.9990000000
31	2000-12-31	20001231235959.9999000000
31	2000-12-31	20001231235959.9999900000
31	2000-12-31	20001231235959.9999990000
1	2000-12-31	20001231235959.9999999000
#
# Functions with a single DATETIME input, conversion from DATETIME-in-VARCHAR
#
SELECT TO_SECONDS(a), CAST(a AS DATETIME(6)), a FROM t1_datetime_in_varchar ORDER BY id;
TO_SECONDS(a)	CAST(a AS DATETIME(6))	a
63145526399	2000-12-31 23:59:59.000000	2000-12-31 23:59:59
63145526399	2000-12-31 23:59:59.900000	2000-12-31 23:59:59.9
63145526399	2000-12-31 23:59:59.990000	2000-12-31 23:59:59.99
63145526399	2000-12-31 23:59:59.999000	2000-12-31 23:59:59.999
63145526399	2000-12-31 23:59:59.999900	2000-12-31 23:59:59.9999
63145526399	2000-12-31 23:59:59.999990	2000-12-31 23:59:59.99999
63145526399	2000-12-31 23:59:59.999999	2000-12-31 23:59:59.999999
63145526400	2001-01-01 00:00:00.000000	2000-12-31 23:59:59.9999999
Warnings:
Note	1292	Truncated incorrect datetime value: '2000-12-31 23:59:59.9999999'
Note	1292	Truncated incorrect datetime value: '2000-12-31 23:59:59.9999999'
SET time_zone='+00:00';
SELECT UNIX_TIMESTAMP(a), CAST(a AS DATETIME(6)), a FROM t1_datetime_in_varchar ORDER BY id;
UNIX_TIMESTAMP(a)	CAST(a AS DATETIME(6))	a
978307199.000000	2000-12-31 23:59:59.000000	2000-12-31 23:59:59
978307199.900000	2000-12-31 23:59:59.900000	2000-12-31 23:59:59.9
978307199.990000	2000-12-31 23:59:59.990000	2000-12-31 23:59:59.99
978307199.999000	2000-12-31 23:59:59.999000	2000-12-31 23:59:59.999
978307199.999900	2000-12-31 23:59:59.999900	2000-12-31 23:59:59.9999
978307199.999990	2000-12-31 23:59:59.999990	2000-12-31 23:59:59.99999
978307199.999999	2000-12-31 23:59:59.999999	2000-12-31 23:59:59.999999
978307200.000000	2001-01-01 00:00:00.000000	2000-12-31 23:59:59.9999999
Warnings:
Note	1292	Truncated incorrect datetime value: '2000-12-31 23:59:59.9999999'
Note	1292	Truncated incorrect datetime value: '2000-12-31 23:59:59.9999999'
SET time_zone=DEFAULT;
SELECT CONVERT_TZ(a, '+00:00','+00:00'), CAST(a AS DATETIME(6)), a FROM t1_datetime_in_varchar ORDER BY id;
CONVERT_TZ(a, '+00:00','+00:00')	CAST(a AS DATETIME(6))	a
2000-12-31 23:59:59.000000	2000-12-31 23:59:59.000000	2000-12-31 23:59:59
2000-12-31 23:59:59.900000	2000-12-31 23:59:59.900000	2000-12-31 23:59:59.9
2000-12-31 23:59:59.990000	2000-12-31 23:59:59.990000	2000-12-31 23:59:59.99
2000-12-31 23:59:59.999000	2000-12-31 23:59:59.999000	2000-12-31 23:59:59.999
2000-12-31 23:59:59.999900	2000-12-31 23:59:59.999900	2000-12-31 23:59:59.9999
2000-12-31 23:59:59.999990	2000-12-31 23:59:59.999990	2000-12-31 23:59:59.99999
2000-12-31 23:59:59.999999	2000-12-31 23:59:59.999999	2000-12-31 23:59:59.999999
2001-01-01 00:00:00.000000	2001-01-01 00:00:00.000000	2000-12-31 23:59:59.9999999
Warnings:
Note	1292	Truncated incorrect datetime value: '2000-12-31 23:59:59.9999999'
Note	1292	Truncated incorrect datetime value: '2000-12-31 23:59:59.9999999'
#
# Functions with a single DATETIME input, conversion from DATETIME-in-DECIMAL
#
SELECT TO_SECONDS(a), CAST(a AS DATETIME(6)), a FROM t1_datetime_in_decimal ORDER BY id;
TO_SECONDS(a)	CAST(a AS DATETIME(6))	a
63145526399	2000-12-31 23:59:59.000000	20001231235959.0000000000
63145526399	2000-12-31 23:59:59.900000	20001231235959.9000000000
63145526399	2000-12-31 23:59:59.990000	20001231235959.9900000000
63145526399	2000-12-31 23:59:59.999000	20001231235959.9990000000
63145526399	2000-12-31 23:59:59.999900	20001231235959.9999000000
63145526399	2000-12-31 23:59:59.999990	20001231235959.9999900000
63145526399	2000-12-31 23:59:59.999999	20001231235959.9999990000
63145526400	2001-01-01 00:00:00.000000	20001231235959.9999999000
SET time_zone='+00:00';
SELECT UNIX_TIMESTAMP(a), CAST(a AS DATETIME(6)), a FROM t1_datetime_in_decimal ORDER BY id;
UNIX_TIMESTAMP(a)	CAST(a AS DATETIME(6))	a
978307199.000000	2000-12-31 23:59:59.000000	20001231235959.0000000000
978307199.900000	2000-12-31 23:59:59.900000	20001231235959.9000000000
978307199.990000	2000-12-31 23:59:59.990000	20001231235959.9900000000
978307199.999000	2000-12-31 23:59:59.999000	20001231235959.9990000000
978307199.999900	2000-12-31 23:59:59.999900	20001231235959.9999000000
978307199.999990	2000-12-31 23:59:59.999990	20001231235959.9999900000
978307199.999999	2000-12-31 23:59:59.999999	20001231235959.9999990000
978307200.000000	2001-01-01 00:00:00.000000	20001231235959.9999999000
SET time_zone=DEFAULT;
SELECT CONVERT_TZ(a, '+00:00','+00:00'), CAST(a AS DATETIME(6)), a FROM t1_datetime_in_decimal ORDER BY id;
CONVERT_TZ(a, '+00:00','+00:00')	CAST(a AS DATETIME(6))	a
2000-12-31 23:59:59.000000	2000-12-31 23:59:59.000000	20001231235959.0000000000
2000-12-31 23:59:59.900000	2000-12-31 23:59:59.900000	20001231235959.9000000000
2000-12-31 23:59:59.990000	2000-12-31 23:59:59.990000	20001231235959.9900000000
2000-12-31 23:59:59.999000	2000-12-31 23:59:59.999000	20001231235959.9990000000
2000-12-31 23:59:59.999900	2000-12-31 23:59:59.999900	20001231235959.9999000000
2000-12-31 23:59:59.999990	2000-12-31 23:59:59.999990	20001231235959.9999900000
2000-12-31 23:59:59.999999	2000-12-31 23:59:59.999999	20001231235959.9999990000
2001-01-01 00:00:00.000000	2001-01-01 00:00:00.000000	20001231235959.9999999000
DROP TABLE t1_datetime_in_varchar;
DROP TABLE t1_datetime_in_decimal;
DROP TABLE t1_time_in_varchar;
DROP TABLE t1_time_in_decimal;
#
# Functions that construct DATETIME
#
SET time_zone='+00:00';
CREATE TABLE t1_unix_timestamp (id SERIAL, a DECIMAL(30,10));
INSERT INTO t1_unix_timestamp (a) VALUES
(980639999),
(980639999.9),
(980639999.999999),
(980639999.9999999),
(2147483647),
(2147483647.9),
(2147483647.999999);
SELECT a, FROM_UNIXTIME(a) FROM t1_unix_timestamp ORDER BY id;
a	FROM_UNIXTIME(a)
980639999.0000000000	2001-01-27 23:59:59.000000
980639999.9000000000	2001-01-27 23:59:59.900000
980639999.9999990000	2001-01-27 23:59:59.999999
980639999.9999999000	2001-01-28 00:00:00.000000
2147483647.0000000000	2038-01-19 03:14:07.000000
2147483647.9000000000	2038-01-19 03:14:07.900000
2147483647.9999990000	2038-01-19 03:14:07.999999
DROP TABLE t1_unix_timestamp;
SET time_zone=DEFAULT;
#
# Functions that construct TIME
#
CREATE TABLE t1_sec (id SERIAL, a DECIMAL(38,10));
INSERT INTO t1_sec (a) VALUES
(59),
(59.9),
(59.999999),
(59.9999999),
(3020398),
(3020398.999999),
(3020398.9999999),
(3020399),
(3020399.999999),
(3020399.9999999),
(9223372036854775807),
(9223372036854775807.9),
(9223372036854775807.999999),
(9223372036854775807.9999999),
(18446744073709551615),
(18446744073709551615.9),
(18446744073709551615.999999),
(18446744073709551615.9999999);
SELECT a, SEC_TO_TIME(a) FROM t1_sec ORDER BY id;
a	SEC_TO_TIME(a)
59.0000000000	00:00:59.000000
59.9000000000	00:00:59.900000
59.9999990000	00:00:59.999999
59.9999999000	00:01:00.000000
3020398.0000000000	838:59:58.000000
3020398.9999990000	838:59:58.999999
3020398.9999999000	838:59:59.000000
3020399.0000000000	838:59:59.000000
3020399.9999990000	838:59:59.999999
3020399.9999999000	838:59:59.999999
9223372036854775807.0000000000	838:59:59.999999
9223372036854775807.9000000000	838:59:59.999999
9223372036854775807.9999990000	838:59:59.999999
9223372036854775807.9999999000	838:59:59.999999
18446744073709551615.0000000000	838:59:59.999999
18446744073709551615.9000000000	838:59:59.999999
18446744073709551615.9999990000	838:59:59.999999
18446744073709551615.9999999000	838:59:59.999999
Warnings:
Warning	1292	Truncated incorrect seconds value: '3020400'
Warning	1292	Truncated incorrect seconds value: '9223372036854775807.0000000000'
Warning	1292	Truncated incorrect seconds value: '9223372036854775807.9000000000'
Warning	1292	Truncated incorrect seconds value: '9223372036854775807.9999990000'
Warning	1292	Truncated incorrect seconds value: '9223372036854775807.9999999000'
Warning	1292	Truncated incorrect seconds value: '18446744073709551615.0000000000'
Warning	1292	Truncated incorrect seconds value: '18446744073709551615.9000000000'
Warning	1292	Truncated incorrect seconds value: '18446744073709551615.9999990000'
Warning	1292	Truncated incorrect seconds value: '18446744073709551615.9999999000'
DROP TABLE t1_sec;
CREATE TABLE t1_sec (id SERIAL, a DECIMAL(38,10));
INSERT INTO t1_sec (a) VALUES
(0),
(0.9),
(0.999999),
(0.9999999);
SELECT a, MAKETIME(0, 0, a) FROM t1_sec ORDER BY id;
a	MAKETIME(0, 0, a)
0.0000000000	00:00:00.000000
0.9000000000	00:00:00.900000
0.9999990000	00:00:00.999999
0.9999999000	00:00:01.000000
DROP TABLE t1_sec;
CREATE TABLE t1 (a VARCHAR(64));
INSERT INTO t1 VALUES
('2017-12-31 23:59:59'),
('2017-12-31 23:59:59.9'),
('2017-12-31 23:59:59.999999'),
('2017-12-31 23:59:59.9999999');
SELECT
'----',
a,
DATE_FORMAT(a, '%Y') AS yyyy,
DATE_FORMAT(a, '%Y-%m-%d') AS d,
DATE_FORMAT(a, '%H:%i:%s') AS t0,
DATE_FORMAT(a, '%H:%i:%s.%f') AS t6,
DATE_FORMAT(a, '%Y-%m-%d %H:%i:%s') AS dt0,
DATE_FORMAT(a, '%Y-%m-%d %H:%i:%s.%f') AS dt6
FROM t1;
----	----
a	2017-12-31 23:59:59
yyyy	2017
d	2017-12-31
t0	23:59:59
t6	23:59:59.000000
dt0	2017-12-31 23:59:59
dt6	2017-12-31 23:59:59.000000
----	----
a	2017-12-31 23:59:59.9
yyyy	2017
d	2017-12-31
t0	23:59:59
t6	23:59:59.900000
dt0	2017-12-31 23:59:59
dt6	2017-12-31 23:59:59.900000
----	----
a	2017-12-31 23:59:59.999999
yyyy	2017
d	2017-12-31
t0	23:59:59
t6	23:59:59.999999
dt0	2017-12-31 23:59:59
dt6	2017-12-31 23:59:59.999999
----	----
a	2017-12-31 23:59:59.9999999
yyyy	2018
d	2018-01-01
t0	00:00:00
t6	00:00:00.000000
dt0	2018-01-01 00:00:00
dt6	2018-01-01 00:00:00.000000
Warnings:
Level	Note
Code	1292
Message	Truncated incorrect datetime value: '2017-12-31 23:59:59.9999999'
Level	Note
Code	1292
Message	Truncated incorrect datetime value: '2017-12-31 23:59:59.9999999'
Level	Note
Code	1292
Message	Truncated incorrect datetime value: '2017-12-31 23:59:59.9999999'
Level	Note
Code	1292
Message	Truncated incorrect datetime value: '2017-12-31 23:59:59.9999999'
Level	Note
Code	1292
Message	Truncated incorrect datetime value: '2017-12-31 23:59:59.9999999'
Level	Note
Code	1292
Message	Truncated incorrect datetime value: '2017-12-31 23:59:59.9999999'
DROP TABLE t1;
CREATE TABLE t1 (a DECIMAL(32,9));
INSERT INTO t1 VALUES
(20171231235959),
(20171231235959.9),
(20171231235959.999999),
(20171231235959.9999999);
SELECT
'----',
a,
DATE_FORMAT(a, '%Y') AS yyyy,
DATE_FORMAT(a, '%Y-%m-%d') AS d,
DATE_FORMAT(a, '%H:%i:%s') AS t0,
DATE_FORMAT(a, '%H:%i:%s.%f') AS t6,
DATE_FORMAT(a, '%Y-%m-%d %H:%i:%s') AS dt0,
DATE_FORMAT(a, '%Y-%m-%d %H:%i:%s.%f') AS dt6
FROM t1;
----	----
a	20171231235959.000000000
yyyy	2017
d	2017-12-31
t0	23:59:59
t6	23:59:59.000000
dt0	2017-12-31 23:59:59
dt6	2017-12-31 23:59:59.000000
----	----
a	20171231235959.900000000
yyyy	2017
d	2017-12-31
t0	23:59:59
t6	23:59:59.900000
dt0	2017-12-31 23:59:59
dt6	2017-12-31 23:59:59.900000
----	----
a	20171231235959.999999000
yyyy	2017
d	2017-12-31
t0	23:59:59
t6	23:59:59.999999
dt0	2017-12-31 23:59:59
dt6	2017-12-31 23:59:59.999999
----	----
a	20171231235959.999999900
yyyy	2018
d	2018-01-01
t0	00:00:00
t6	00:00:00.000000
dt0	2018-01-01 00:00:00
dt6	2018-01-01 00:00:00.000000
DROP TABLE t1;
#
# Functions with two temporal parameters that round nanoseconds in both parameters in MySQL
#
CREATE TABLE t1 (a VARCHAR(64));
CREATE TABLE t2 (a VARCHAR(64));
INSERT INTO t1 VALUES
('2017-12-31 23:59:59'),
('2017-12-31 23:59:59.9'),
('2017-12-31 23:59:59.999999'),
('2017-12-31 23:59:59.9999999');
INSERT INTO t2 VALUES
('00:00:00'),
('00:00:00.9'),
('00:00:00.999999'),
('00:00:00.9999999');
SELECT TIMESTAMP(t1.a, t2.a), t1.a, t2.a FROM t1,t2 ORDER BY t1.a, t2.a;
TIMESTAMP(t1.a, t2.a)	a	a
2017-12-31 23:59:59.000000	2017-12-31 23:59:59	00:00:00
2017-12-31 23:59:59.900000	2017-12-31 23:59:59	00:00:00.9
2017-12-31 23:59:59.999999	2017-12-31 23:59:59	00:00:00.999999
2018-01-01 00:00:00.000000	2017-12-31 23:59:59	00:00:00.9999999
2017-12-31 23:59:59.900000	2017-12-31 23:59:59.9	00:00:00
2018-01-01 00:00:00.800000	2017-12-31 23:59:59.9	00:00:00.9
2018-01-01 00:00:00.899999	2017-12-31 23:59:59.9	00:00:00.999999
2018-01-01 00:00:00.900000	2017-12-31 23:59:59.9	00:00:00.9999999
2017-12-31 23:59:59.999999	2017-12-31 23:59:59.999999	00:00:00
2018-01-01 00:00:00.899999	2017-12-31 23:59:59.999999	00:00:00.9
2018-01-01 00:00:00.999998	2017-12-31 23:59:59.999999	00:00:00.999999
2018-01-01 00:00:00.999999	2017-12-31 23:59:59.999999	00:00:00.9999999
2018-01-01 00:00:00.000000	2017-12-31 23:59:59.9999999	00:00:00
2018-01-01 00:00:00.900000	2017-12-31 23:59:59.9999999	00:00:00.9
2018-01-01 00:00:00.999999	2017-12-31 23:59:59.9999999	00:00:00.999999
2018-01-01 00:00:01.000000	2017-12-31 23:59:59.9999999	00:00:00.9999999
Warnings:
Note	1292	Truncated incorrect datetime value: '2017-12-31 23:59:59.9999999'
Note	1292	Truncated incorrect datetime value: '2017-12-31 23:59:59.9999999'
Note	1292	Truncated incorrect datetime value: '2017-12-31 23:59:59.9999999'
Note	1292	Truncated incorrect INTERVAL DAY TO SECOND value: '00:00:00.9999999'
Note	1292	Truncated incorrect INTERVAL DAY TO SECOND value: '00:00:00.9999999'
Note	1292	Truncated incorrect INTERVAL DAY TO SECOND value: '00:00:00.9999999'
Note	1292	Truncated incorrect datetime value: '2017-12-31 23:59:59.9999999'
Note	1292	Truncated incorrect INTERVAL DAY TO SECOND value: '00:00:00.9999999'
SELECT ADDTIME(t1.a, t2.a), t1.a, t2.a FROM t1,t2 ORDER BY t1.a, t2.a;
ADDTIME(t1.a, t2.a)	a	a
2017-12-31 23:59:59	2017-12-31 23:59:59	00:00:00
2017-12-31 23:59:59.900000	2017-12-31 23:59:59	00:00:00.9
2017-12-31 23:59:59.999999	2017-12-31 23:59:59	00:00:00.999999
2018-01-01 00:00:00	2017-12-31 23:59:59	00:00:00.9999999
2017-12-31 23:59:59.900000	2017-12-31 23:59:59.9	00:00:00
2018-01-01 00:00:00.800000	2017-12-31 23:59:59.9	00:00:00.9
2018-01-01 00:00:00.899999	2017-12-31 23:59:59.9	00:00:00.999999
2018-01-01 00:00:00.900000	2017-12-31 23:59:59.9	00:00:00.9999999
2017-12-31 23:59:59.999999	2017-12-31 23:59:59.999999	00:00:00
2018-01-01 00:00:00.899999	2017-12-31 23:59:59.999999	00:00:00.9
2018-01-01 00:00:00.999998	2017-12-31 23:59:59.999999	00:00:00.999999
2018-01-01 00:00:00.999999	2017-12-31 23:59:59.999999	00:00:00.9999999
2018-01-01 00:00:00	2017-12-31 23:59:59.9999999	00:00:00
2018-01-01 00:00:00.900000	2017-12-31 23:59:59.9999999	00:00:00.9
2018-01-01 00:00:00.999999	2017-12-31 23:59:59.9999999	00:00:00.999999
2018-01-01 00:00:01	2017-12-31 23:59:59.9999999	00:00:00.9999999
Warnings:
Note	1292	Truncated incorrect datetime value: '2017-12-31 23:59:59.9999999'
Note	1292	Truncated incorrect datetime value: '2017-12-31 23:59:59.9999999'
Note	1292	Truncated incorrect datetime value: '2017-12-31 23:59:59.9999999'
Note	1292	Truncated incorrect INTERVAL DAY TO SECOND value: '00:00:00.9999999'
Note	1292	Truncated incorrect INTERVAL DAY TO SECOND value: '00:00:00.9999999'
Note	1292	Truncated incorrect INTERVAL DAY TO SECOND value: '00:00:00.9999999'
Note	1292	Truncated incorrect datetime value: '2017-12-31 23:59:59.9999999'
Note	1292	Truncated incorrect INTERVAL DAY TO SECOND value: '00:00:00.9999999'
DROP TABLE t1, t2;
CREATE TABLE t1 (a VARCHAR(64));
CREATE TABLE t2 (a VARCHAR(64));
INSERT INTO t1 VALUES
('23:59:59'),
('23:59:59.9'),
('23:59:59.999999'),
('23:59:59.9999999');
INSERT INTO t2 VALUES
('00:00:00'),
('00:00:00.9'),
('00:00:00.999999'),
('00:00:00.9999999');
SELECT TIMEDIFF(t1.a, t2.a), t1.a, t2.a FROM t1,t2 ORDER BY t1.a, t2.a;
TIMEDIFF(t1.a, t2.a)	a	a
23:59:59.000000	23:59:59	00:00:00
23:59:58.100000	23:59:59	00:00:00.9
23:59:58.000001	23:59:59	00:00:00.999999
23:59:58.000000	23:59:59	00:00:00.9999999
23:59:59.900000	23:59:59.9	00:00:00
23:59:59.000000	23:59:59.9	00:00:00.9
23:59:58.900001	23:59:59.9	00:00:00.999999
23:59:58.900000	23:59:59.9	00:00:00.9999999
23:59:59.999999	23:59:59.999999	00:00:00
23:59:59.099999	23:59:59.999999	00:00:00.9
23:59:59.000000	23:59:59.999999	00:00:00.999999
23:59:58.999999	23:59:59.999999	00:00:00.9999999
24:00:00.000000	23:59:59.9999999	00:00:00
23:59:59.100000	23:59:59.9999999	00:00:00.9
23:59:59.000001	23:59:59.9999999	00:00:00.999999
23:59:59.000000	23:59:59.9999999	00:00:00.9999999
Warnings:
Note	1292	Truncated incorrect time value: '23:59:59.9999999'
Note	1292	Truncated incorrect time value: '23:59:59.9999999'
Note	1292	Truncated incorrect time value: '23:59:59.9999999'
Note	1292	Truncated incorrect time value: '00:00:00.9999999'
Note	1292	Truncated incorrect time value: '00:00:00.9999999'
Note	1292	Truncated incorrect time value: '00:00:00.9999999'
Note	1292	Truncated incorrect time value: '23:59:59.9999999'
Note	1292	Truncated incorrect time value: '00:00:00.9999999'
DROP TABLE t1, t2;
CREATE TABLE t1 (a VARCHAR(64));
CREATE TABLE t2 (a VARCHAR(64));
INSERT INTO t1 VALUES
('2001-12-31 23:59:59'),
('2001-12-31 23:59:59.9'),
('2001-12-31 23:59:59.999999'),
('2001-12-31 23:59:59.9999999');
INSERT INTO t2 VALUES
('2001-12-31 23:59:59'),
('2001-12-31 23:59:59.9'),
('2001-12-31 23:59:59.999999'),
('2001-12-31 23:59:59.9999999');
SELECT TIMESTAMPDIFF(MICROSECOND,t1.a, t2.a), t1.a, t2.a FROM t1,t2 ORDER BY t1.a, t2.a;
TIMESTAMPDIFF(MICROSECOND,t1.a, t2.a)	a	a
0	2001-12-31 23:59:59	2001-12-31 23:59:59
900000	2001-12-31 23:59:59	2001-12-31 23:59:59.9
999999	2001-12-31 23:59:59	2001-12-31 23:59:59.999999
1000000	2001-12-31 23:59:59	2001-12-31 23:59:59.9999999
-900000	2001-12-31 23:59:59.9	2001-12-31 23:59:59
0	2001-12-31 23:59:59.9	2001-12-31 23:59:59.9
99999	2001-12-31 23:59:59.9	2001-12-31 23:59:59.999999
100000	2001-12-31 23:59:59.9	2001-12-31 23:59:59.9999999
-999999	2001-12-31 23:59:59.999999	2001-12-31 23:59:59
-99999	2001-12-31 23:59:59.999999	2001-12-31 23:59:59.9
0	2001-12-31 23:59:59.999999	2001-12-31 23:59:59.999999
1	2001-12-31 23:59:59.999999	2001-12-31 23:59:59.9999999
-1000000	2001-12-31 23:59:59.9999999	2001-12-31 23:59:59
-100000	2001-12-31 23:59:59.9999999	2001-12-31 23:59:59.9
-1	2001-12-31 23:59:59.9999999	2001-12-31 23:59:59.999999
0	2001-12-31 23:59:59.9999999	2001-12-31 23:59:59.9999999
Warnings:
Note	1292	Truncated incorrect datetime value: '2001-12-31 23:59:59.9999999'
Note	1292	Truncated incorrect datetime value: '2001-12-31 23:59:59.9999999'
Note	1292	Truncated incorrect datetime value: '2001-12-31 23:59:59.9999999'
Note	1292	Truncated incorrect datetime value: '2001-12-31 23:59:59.9999999'
Note	1292	Truncated incorrect datetime value: '2001-12-31 23:59:59.9999999'
Note	1292	Truncated incorrect datetime value: '2001-12-31 23:59:59.9999999'
Note	1292	Truncated incorrect datetime value: '2001-12-31 23:59:59.9999999'
Note	1292	Truncated incorrect datetime value: '2001-12-31 23:59:59.9999999'
DROP TABLE t1, t2;
CREATE TABLE t1 (a VARCHAR(64));
CREATE TABLE t2 (a VARCHAR(64));
INSERT INTO t1 VALUES
('23:59:59'),
('23:59:59.9'),
('23:59:59.999999'),
('23:59:59.9999999');
INSERT INTO t2 VALUES
('00:00:00'),
('00:00:00.9'),
('00:00:00.999999'),
('00:00:00.9999999');
SELECT TIMEDIFF(t1.a, t2.a), t1.a, t2.a FROM t1,t2 ORDER BY t1.a, t2.a;
TIMEDIFF(t1.a, t2.a)	a	a
23:59:59.000000	23:59:59	00:00:00
23:59:58.100000	23:59:59	00:00:00.9
23:59:58.000001	23:59:59	00:00:00.999999
23:59:58.000000	23:59:59	00:00:00.9999999
23:59:59.900000	23:59:59.9	00:00:00
23:59:59.000000	23:59:59.9	00:00:00.9
23:59:58.900001	23:59:59.9	00:00:00.999999
23:59:58.900000	23:59:59.9	00:00:00.9999999
23:59:59.999999	23:59:59.999999	00:00:00
23:59:59.099999	23:59:59.999999	00:00:00.9
23:59:59.000000	23:59:59.999999	00:00:00.999999
23:59:58.999999	23:59:59.999999	00:00:00.9999999
24:00:00.000000	23:59:59.9999999	00:00:00
23:59:59.100000	23:59:59.9999999	00:00:00.9
23:59:59.000001	23:59:59.9999999	00:00:00.999999
23:59:59.000000	23:59:59.9999999	00:00:00.9999999
Warnings:
Note	1292	Truncated incorrect time value: '23:59:59.9999999'
Note	1292	Truncated incorrect time value: '23:59:59.9999999'
Note	1292	Truncated incorrect time value: '23:59:59.9999999'
Note	1292	Truncated incorrect time value: '00:00:00.9999999'
Note	1292	Truncated incorrect time value: '00:00:00.9999999'
Note	1292	Truncated incorrect time value: '00:00:00.9999999'
Note	1292	Truncated incorrect time value: '23:59:59.9999999'
Note	1292	Truncated incorrect time value: '00:00:00.9999999'
DROP TABLE t1, t2;
#
# STR_TO_DATE behaviour is questionable in MySQL 5.6 (MySQL Bug #92474)
#
# It truncates nanoseconds, but this may change in the future.
CREATE TABLE t1 (a VARCHAR(64));
INSERT INTO t1 VALUES
('2017-12-31 23:59:59'),
('2017-12-31 23:59:59.9'),
('2017-12-31 23:59:59.999999'),
('2017-12-31 23:59:59.9999999');
SELECT
a,
STR_TO_DATE(a, '%Y-%m-%d %H:%i:%s') AS c0,
STR_TO_DATE(a, '%Y-%m-%d %H:%i:%s.%f') AS c6
FROM t1;
a	c0	c6
2017-12-31 23:59:59	2017-12-31 23:59:59	2017-12-31 23:59:59.000000
2017-12-31 23:59:59.9	2017-12-31 23:59:59	2017-12-31 23:59:59.900000
2017-12-31 23:59:59.999999	2017-12-31 23:59:59	2017-12-31 23:59:59.999999
2017-12-31 23:59:59.9999999	2017-12-31 23:59:59	2017-12-31 23:59:59.999999
Warnings:
Warning	1292	Truncated incorrect datetime value: '2017-12-31 23:59:59.9'
Warning	1292	Truncated incorrect datetime value: '2017-12-31 23:59:59.999999'
Warning	1292	Truncated incorrect datetime value: '2017-12-31 23:59:59.9999999'
Warning	1292	Truncated incorrect datetime value: '2017-12-31 23:59:59.9999999'
DROP TABLE t1;
#
# DATE_ADD behaviour is questionable in MySQL 5.6 (MySQL Bug#92473)
# It rounds nanoseconds in the first argument, but truncates nanoseconds in the second argument.
# This may change in the future, to round both arguments.
#
CREATE TABLE t1 (a VARCHAR(64));
INSERT INTO t1 VALUES
('2017-12-31 23:59:59'),
('2017-12-31 23:59:59.9'),
('2017-12-31 23:59:59.999999'),
('2017-12-31 23:59:59.9999999');
CREATE TABLE t2 (b DECIMAL(32,9));
INSERT INTO t2 VALUES
(0),
(0.9),
(0.999999),
(0.9999999);
SELECT a, b, DATE_ADD(a, INTERVAL b SECOND) FROM t1,t2 ORDER BY a,b;
a	b	DATE_ADD(a, INTERVAL b SECOND)
2017-12-31 23:59:59	0.000000000	2017-12-31 23:59:59.000000
2017-12-31 23:59:59	0.900000000	2017-12-31 23:59:59.900000
2017-12-31 23:59:59	0.999999000	2017-12-31 23:59:59.999999
2017-12-31 23:59:59	0.999999900	2017-12-31 23:59:59.999999
2017-12-31 23:59:59.9	0.000000000	2017-12-31 23:59:59.900000
2017-12-31 23:59:59.9	0.900000000	2018-01-01 00:00:00.800000
2017-12-31 23:59:59.9	0.999999000	2018-01-01 00:00:00.899999
2017-12-31 23:59:59.9	0.999999900	2018-01-01 00:00:00.899999
2017-12-31 23:59:59.999999	0.000000000	2017-12-31 23:59:59.999999
2017-12-31 23:59:59.999999	0.900000000	2018-01-01 00:00:00.899999
2017-12-31 23:59:59.999999	0.999999000	2018-01-01 00:00:00.999998
2017-12-31 23:59:59.999999	0.999999900	2018-01-01 00:00:00.999998
2017-12-31 23:59:59.9999999	0.000000000	2018-01-01 00:00:00.000000
2017-12-31 23:59:59.9999999	0.900000000	2018-01-01 00:00:00.900000
2017-12-31 23:59:59.9999999	0.999999000	2018-01-01 00:00:00.999999
2017-12-31 23:59:59.9999999	0.999999900	2018-01-01 00:00:00.999999
Warnings:
Note	1292	Truncated incorrect datetime value: '2017-12-31 23:59:59.9999999'
Note	1292	Truncated incorrect datetime value: '2017-12-31 23:59:59.9999999'
Note	1292	Truncated incorrect datetime value: '2017-12-31 23:59:59.9999999'
Note	1292	Truncated incorrect datetime value: '2017-12-31 23:59:59.9999999'
DROP TABLE t1, t2;