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), (2147483647.9999999); 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 2147483647.9999999000 NULL 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;