SET sql_mode='time_truncate_fractional,ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION'; # # Testing time_truncate_fractional from VARCHAR and TIME(6) to TIME(N) # CREATE TABLE t1 (a VARCHAR(32), t6 TIME(6), t5 TIME(5), t4 TIME(4), t3 TIME(3), t2 TIME(2), t1 TIME(1), t0 TIME); INSERT INTO t1 (a) VALUES ('10:10:10.9999999'); INSERT INTO t1 (a) VALUES ('10:10:10.9999994'); SELECT * FROM t1;; a 10:10:10.9999999 t6 NULL t5 NULL t4 NULL t3 NULL t2 NULL t1 NULL t0 NULL a 10:10:10.9999994 t6 NULL t5 NULL t4 NULL t3 NULL t2 NULL t1 NULL t0 NULL ALTER TABLE t1 MODIFY a TIME(6); UPDATE t1 SET t0=a, t1=a, t2=a, t3=a, t4=a, t5=a, t6=a; SELECT * FROM t1;; a 10:10:10.999999 t6 10:10:10.999999 t5 10:10:10.99999 t4 10:10:10.9999 t3 10:10:10.999 t2 10:10:10.99 t1 10:10:10.9 t0 10:10:10 a 10:10:10.999999 t6 10:10:10.999999 t5 10:10:10.99999 t4 10:10:10.9999 t3 10:10:10.999 t2 10:10:10.99 t1 10:10:10.9 t0 10:10:10 DROP TABLE t1; # # Testing time_truncate_fractional when altering TIME(N) to a smaller size # CREATE TABLE t1 (a TIME(6)); INSERT INTO t1 VALUES ('10:10:10.999999'); ALTER TABLE t1 MODIFY a TIME(5); SELECT * FROM t1; a 10:10:10.99999 DROP TABLE t1; CREATE TABLE t1 (a TIME(6)); INSERT INTO t1 VALUES ('10:10:10.999999'); ALTER TABLE t1 MODIFY a TIME; SELECT * FROM t1; a 10:10:10 DROP TABLE t1; # # Testing time_truncate_fractional from DATETIME(6) to TIME(N) # CREATE TABLE t1 (a DATETIME(6), t6 TIME(6), t5 TIME(5), t4 TIME(4), t3 TIME(3), t2 TIME(2), t1 TIME(1), t0 TIME); INSERT INTO t1 (a) VALUES ('2001-01-01 10:10:10.999999'); INSERT INTO t1 (a) VALUES ('2001-01-01 23:59:59.9999994'); UPDATE t1 SET t0=a, t1=a, t2=a, t3=a, t4=a, t5=a, t6=a; SELECT * FROM t1;; a 2001-01-01 10:10:10.999999 t6 10:10:10.999999 t5 10:10:10.99999 t4 10:10:10.9999 t3 10:10:10.999 t2 10:10:10.99 t1 10:10:10.9 t0 10:10:10 a 2001-01-01 23:59:59.999999 t6 23:59:59.999999 t5 23:59:59.99999 t4 23:59:59.9999 t3 23:59:59.999 t2 23:59:59.99 t1 23:59:59.9 t0 23:59:59 DROP TABLE t1; # # Testing time_truncate_fractional from DECIMAL and DOUBLE to TIME(N) # CREATE TABLE t1 (a DECIMAL(30,7), t6 TIME(6), t5 TIME(5), t4 TIME(4), t3 TIME(3), t2 TIME(2), t1 TIME(1), t0 TIME); INSERT INTO t1 (a) VALUES (101010.9999999); SELECT * FROM t1;; a 101010.9999999 t6 NULL t5 NULL t4 NULL t3 NULL t2 NULL t1 NULL t0 NULL ALTER TABLE t1 MODIFY a DOUBLE; UPDATE t1 SET t0=a, t1=a, t2=a, t3=a, t4=a, t5=a, t6=a; SELECT * FROM t1;; a 101010.9999999 t6 10:10:10.999999 t5 10:10:10.99999 t4 10:10:10.9999 t3 10:10:10.999 t2 10:10:10.99 t1 10:10:10.9 t0 10:10:10 DROP TABLE t1; # # Testing INSERT with near-maximum and near-minimum supported values. # CREATE TABLE t1 (a TIME(6)); INSERT INTO t1 VALUES ('838:59:59.9999999'); ERROR 22007: Incorrect time value: '838:59:59.9999999' for column 'a' at row 1 INSERT INTO t1 VALUES ('-838:59:59.9999999'); ERROR 22007: Incorrect time value: '-838:59:59.9999999' for column 'a' at row 1 SELECT * FROM t1; a DROP TABLE t1; CREATE TABLE t1 (a TIME); INSERT INTO t1 VALUES ('838:59:59.9999999'); ERROR 22007: Incorrect time value: '838:59:59.9999999' for column 'a' at row 1 INSERT INTO t1 VALUES ('-838:59:59.9999999'); ERROR 22007: Incorrect time value: '-838:59:59.9999999' for column 'a' at row 1 SELECT * FROM t1; a DROP TABLE t1; # # Testing JOIN with comparison between TIME(6) and DECIMAL(20,6) # CREATE TABLE t1 (a TIME(6)); INSERT INTO t1 VALUES ('11:22:33'); INSERT INTO t1 VALUES ('11:22:33.123'); INSERT INTO t1 VALUES ('-11:22:33'); INSERT INTO t1 VALUES ('-11:22:33.1234567'); CREATE TABLE t2 (b DECIMAL(20,6)); INSERT INTO t2 VALUES (112233.123); INSERT INTO t2 VALUES (-112233.1234567); Warnings: Note 1265 Data truncated for column 'b' at row 1 SELECT * FROM t1, t2 WHERE a=b; a b 11:22:33.123000 112233.123000 DROP TABLE t1, t2; # # Testing time_truncate_fractional from TIME(N) to BIGINT # CREATE TABLE t1 (a BIGINT, b TIME(6)); INSERT INTO t1 (b) VALUES ('10:10:59.500000'); INSERT INTO t1 (b) VALUES ('10:10:10.500000'); INSERT INTO t1 (b) VALUES ('10:10:10.499999'); UPDATE t1 SET a=b; SELECT * FROM t1; a b 101059 10:10:59.500000 101010 10:10:10.500000 101010 10:10:10.499999 DROP TABLE t1; # # Testing JOIN with comparison between TIME(6) and VARCHAR # CREATE TABLE t1 (a TIME(6)); INSERT INTO t1 VALUES ('11:22:33'); INSERT INTO t1 VALUES ('11:22:33.123'); INSERT INTO t1 VALUES ('-11:22:33'); INSERT INTO t1 VALUES ('-11:22:33.1234567'); CREATE TABLE t2 (b VARCHAR(20)); INSERT INTO t2 VALUES ('11:22:33.123'); INSERT INTO t2 VALUES ('-11:22:33.123456'); SELECT * FROM t1, t2 WHERE a=b; a b -11:22:33.123456 -11:22:33.123456 DROP TABLE t1, t2; # # Testing SEC_TO_TIME # CREATE TABLE t1 AS SELECT SEC_TO_TIME(3661), CAST(SEC_TO_TIME(3661) AS CHAR); SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( `SEC_TO_TIME(3661)` time DEFAULT NULL, `CAST(SEC_TO_TIME(3661) AS CHAR)` varchar(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL ) ENGINE=XENGINE DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci SELECT * FROM t1; SEC_TO_TIME(3661) CAST(SEC_TO_TIME(3661) AS CHAR) 01:01:01 01:01:01 DROP TABLE t1; CREATE TABLE t1 AS SELECT SEC_TO_TIME(3661.9) AS c1, SEC_TO_TIME(3661.99) AS c2, SEC_TO_TIME(3661.999) AS c3, SEC_TO_TIME(3661.9999) AS c4, SEC_TO_TIME(3661.99999) AS c5, SEC_TO_TIME(3661.999999) AS c6, SEC_TO_TIME(3661.9999999) AS c7; SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( `c1` time(1) DEFAULT NULL, `c2` time(2) DEFAULT NULL, `c3` time(3) DEFAULT NULL, `c4` time(4) DEFAULT NULL, `c5` time(5) DEFAULT NULL, `c6` time(6) DEFAULT NULL, `c7` time(6) DEFAULT NULL ) ENGINE=XENGINE DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci SELECT * FROM t1; c1 c2 c3 c4 c5 c6 c7 01:01:01.9 01:01:01.99 01:01:01.999 01:01:01.9999 01:01:01.99999 01:01:01.999999 01:01:01.999999 DROP TABLE t1; # # Testing time_truncate_fractional with MAKETIME() # CREATE TABLE t1 (hour INT, minute INT, second DECIMAL(23,7)); INSERT INTO t1 VALUES (836, 59, 59.999999), (836, 59, 59.9999990), (836, 59, 59.9999991), (836, 59, 59.9999992), (836, 59, 59.9999993), (836, 59, 59.9999995), (836, 59, 59.9999996), (836, 59, 59.9999997), (836, 59, 59.9999998), (836, 59, 59.9999999); SELECT hour, minute, second, MAKETIME(hour, minute, second) AS MAKETIME FROM t1; hour minute second MAKETIME 836 59 59.9999990 836:59:59.999999 836 59 59.9999990 836:59:59.999999 836 59 59.9999991 836:59:59.999999 836 59 59.9999992 836:59:59.999999 836 59 59.9999993 836:59:59.999999 836 59 59.9999995 836:59:59.999999 836 59 59.9999996 836:59:59.999999 836 59 59.9999997 836:59:59.999999 836 59 59.9999998 836:59:59.999999 836 59 59.9999999 836:59:59.999999 SELECT hour + 1, minute, second, MAKETIME(hour + 1, minute, second) AS MAKETIME FROM t1; hour + 1 minute second MAKETIME 837 59 59.9999990 837:59:59.999999 837 59 59.9999990 837:59:59.999999 837 59 59.9999991 837:59:59.999999 837 59 59.9999992 837:59:59.999999 837 59 59.9999993 837:59:59.999999 837 59 59.9999995 837:59:59.999999 837 59 59.9999996 837:59:59.999999 837 59 59.9999997 837:59:59.999999 837 59 59.9999998 837:59:59.999999 837 59 59.9999999 837:59:59.999999 SELECT -hour, minute, second, MAKETIME(-hour, minute, second) AS MAKETIME FROM t1; -hour minute second MAKETIME -836 59 59.9999990 -836:59:59.999999 -836 59 59.9999990 -836:59:59.999999 -836 59 59.9999991 -836:59:59.999999 -836 59 59.9999992 -836:59:59.999999 -836 59 59.9999993 -836:59:59.999999 -836 59 59.9999995 -836:59:59.999999 -836 59 59.9999996 -836:59:59.999999 -836 59 59.9999997 -836:59:59.999999 -836 59 59.9999998 -836:59:59.999999 -836 59 59.9999999 -836:59:59.999999 SELECT -hour - 1, minute, second, MAKETIME(-hour - 1, minute, second) AS MAKETIME FROM t1; -hour - 1 minute second MAKETIME -837 59 59.9999990 -837:59:59.999999 -837 59 59.9999990 -837:59:59.999999 -837 59 59.9999991 -837:59:59.999999 -837 59 59.9999992 -837:59:59.999999 -837 59 59.9999993 -837:59:59.999999 -837 59 59.9999995 -837:59:59.999999 -837 59 59.9999996 -837:59:59.999999 -837 59 59.9999997 -837:59:59.999999 -837 59 59.9999998 -837:59:59.999999 -837 59 59.9999999 -837:59:59.999999 DROP TABLE t1; SELECT MAKETIME(838, 59, 59.0000005) AS MAKETIME; MAKETIME 838:59:59.000000 SELECT MAKETIME(838, 59, 59.00000056) AS MAKETIME; MAKETIME 838:59:59.000000 SELECT MAKETIME(838, 59, 59.000000567) AS MAKETIME; MAKETIME 838:59:59.000000 SELECT MAKETIME(838, 59, 59.0000005678) AS MAKETIME; MAKETIME 838:59:59.000000 SELECT MAKETIME(838, 59, 59.00000056789) AS MAKETIME; MAKETIME 838:59:59.000000 # # Testing time_truncate_fractional for DATETIME(6) # CREATE TABLE t1 (a DATETIME(6)); INSERT INTO t1 VALUES (20010101100000.1234567); INSERT INTO t1 VALUES (20010228235959.9999997); INSERT INTO t1 VALUES ('2001-01-01 10:00:00.1234567'); INSERT INTO t1 VALUES ('2001-02-28 23:59:59.9999997'); SELECT * FROM t1; a 2001-01-01 10:00:00.123456 2001-02-28 23:59:59.999999 2001-01-01 10:00:00.123456 2001-02-28 23:59:59.999999 DROP TABLE t1; # # Testing time_truncate_fractional when altering DATETIME(N) to a smaller size # CREATE TABLE t1 (a DATETIME(6)); INSERT INTO t1 VALUES ('2001-01-01 10:10:10.999999'); ALTER TABLE t1 MODIFY a DATETIME(5); SELECT * FROM t1; a 2001-01-01 10:10:10.99999 DROP TABLE t1; CREATE TABLE t1 (a DATETIME(6)); INSERT INTO t1 VALUES ('2001-01-01 10:10:10.999999'); ALTER TABLE t1 MODIFY a TIME; SELECT * FROM t1; a 10:10:10 DROP TABLE t1; # # Testing time_truncate_fractional from VARCHAR and DATETIME(6) to DATETIME(N) # CREATE TABLE t1 (a VARCHAR(32), t6 DATETIME(6), t5 DATETIME(5), t4 DATETIME(4), t3 DATETIME(3), t2 DATETIME(2), t1 DATETIME(1), t0 DATETIME); INSERT INTO t1 (a) VALUES ('2001-01-01 10:10:10.9999999'); SELECT * FROM t1;; a 2001-01-01 10:10:10.9999999 t6 NULL t5 NULL t4 NULL t3 NULL t2 NULL t1 NULL t0 NULL ALTER TABLE t1 MODIFY a DATETIME(6); UPDATE t1 SET t0=a, t1=a, t2=a, t3=a, t4=a, t5=a, t6=a; SELECT * FROM t1;; a 2001-01-01 10:10:10.999999 t6 2001-01-01 10:10:10.999999 t5 2001-01-01 10:10:10.99999 t4 2001-01-01 10:10:10.9999 t3 2001-01-01 10:10:10.999 t2 2001-01-01 10:10:10.99 t1 2001-01-01 10:10:10.9 t0 2001-01-01 10:10:10 DROP TABLE t1; # # Testing time_truncate_fractional from DATETIME(N) to BIGINT # CREATE TABLE t1 (a BIGINT, b DATETIME(6)); INSERT INTO t1 (b) VALUES ('2001-01-01 10:10:59.500000'); INSERT INTO t1 (b) VALUES ('2001-01-01 10:10:10.500000'); INSERT INTO t1 (b) VALUES ('2001-01-01 10:10:10.499999'); UPDATE t1 SET a=b; SELECT * FROM t1; a b 20010101101059 2001-01-01 10:10:59.500000 20010101101010 2001-01-01 10:10:10.500000 20010101101010 2001-01-01 10:10:10.499999 DROP TABLE t1; # # Testing time_truncate_fractional from DECIMAL to DATETIME(N) # CREATE TABLE t1 (a DECIMAL(30,6), t6 DATETIME(6), t5 DATETIME(5), t4 DATETIME(4), t3 DATETIME(3), t2 DATETIME(2), t1 DATETIME(1), t0 DATETIME); INSERT INTO t1 (a) VALUES (20010101101010.999999); UPDATE t1 SET t0=a, t1=a, t2=a, t3=a, t4=a, t5=a, t6=a; SELECT * FROM t1;; a 20010101101010.999999 t6 2001-01-01 10:10:10.999999 t5 2001-01-01 10:10:10.99999 t4 2001-01-01 10:10:10.9999 t3 2001-01-01 10:10:10.999 t2 2001-01-01 10:10:10.99 t1 2001-01-01 10:10:10.9 t0 2001-01-01 10:10:10 DROP TABLE t1; # # Testing function MICROSECOND # CREATE TABLE t1 (a DATETIME(6)); INSERT INTO t1 VALUES ('2001-01-01 11:12:13.0000019'); INSERT INTO t1 VALUES ('2001-01-01 11:12:13.1000019'); INSERT INTO t1 VALUES ('2001-01-01 11:12:13.9999999'); SELECT a, MICROSECOND(a) FROM t1; a MICROSECOND(a) 2001-01-01 11:12:13.000001 1 2001-01-01 11:12:13.100001 100001 2001-01-01 11:12:13.999999 999999 SELECT a, EXTRACT(MICROSECOND FROM a) AS b FROM t1; a b 2001-01-01 11:12:13.000001 1 2001-01-01 11:12:13.100001 100001 2001-01-01 11:12:13.999999 999999 DROP TABLE t1; SELECT MICROSECOND(CAST(20010101235959.456 AS DATETIME(6))) AS a; a 456000 SELECT EXTRACT(MICROSECOND FROM CAST(20010101235959.456 AS DATETIME(6))) AS a; a 456000 # # Testing time_truncate_fractional with CAST # CREATE TABLE t1 (a DATETIME(6)); INSERT INTO t1 VALUES ('2001-01-01 10:10:10.999999'); INSERT INTO t1 VALUES ('2001-01-01 10:10:10.999998'); INSERT INTO t1 VALUES ('2001-01-01 10:10:10.999997'); INSERT INTO t1 VALUES ('2001-01-01 10:10:10.999996'); INSERT INTO t1 VALUES ('2001-01-01 10:10:10.999995'); INSERT INTO t1 VALUES ('2001-01-01 10:10:10.999994'); INSERT INTO t1 VALUES ('2001-01-01 10:10:10.999993'); INSERT INTO t1 VALUES ('2001-01-01 10:10:10.999992'); INSERT INTO t1 VALUES ('2001-01-01 10:10:10.999991'); INSERT INTO t1 VALUES ('2001-01-01 10:10:10.999990'); SELECT a, CAST(a AS DATETIME(5)) FROM t1; a CAST(a AS DATETIME(5)) 2001-01-01 10:10:10.999999 2001-01-01 10:10:10.99999 2001-01-01 10:10:10.999998 2001-01-01 10:10:10.99999 2001-01-01 10:10:10.999997 2001-01-01 10:10:10.99999 2001-01-01 10:10:10.999996 2001-01-01 10:10:10.99999 2001-01-01 10:10:10.999995 2001-01-01 10:10:10.99999 2001-01-01 10:10:10.999994 2001-01-01 10:10:10.99999 2001-01-01 10:10:10.999993 2001-01-01 10:10:10.99999 2001-01-01 10:10:10.999992 2001-01-01 10:10:10.99999 2001-01-01 10:10:10.999991 2001-01-01 10:10:10.99999 2001-01-01 10:10:10.999990 2001-01-01 10:10:10.99999 DROP TABLE t1; CREATE TABLE t1 (a DATETIME(5)); INSERT INTO t1 VALUES ('2001-01-01 10:10:10.99999'); INSERT INTO t1 VALUES ('2001-01-01 10:10:10.99998'); INSERT INTO t1 VALUES ('2001-01-01 10:10:10.99997'); INSERT INTO t1 VALUES ('2001-01-01 10:10:10.99996'); INSERT INTO t1 VALUES ('2001-01-01 10:10:10.99995'); INSERT INTO t1 VALUES ('2001-01-01 10:10:10.99994'); INSERT INTO t1 VALUES ('2001-01-01 10:10:10.99993'); INSERT INTO t1 VALUES ('2001-01-01 10:10:10.99992'); INSERT INTO t1 VALUES ('2001-01-01 10:10:10.99991'); INSERT INTO t1 VALUES ('2001-01-01 10:10:10.99990'); SELECT a, CAST(a AS DATETIME(4)) FROM t1; a CAST(a AS DATETIME(4)) 2001-01-01 10:10:10.99999 2001-01-01 10:10:10.9999 2001-01-01 10:10:10.99998 2001-01-01 10:10:10.9999 2001-01-01 10:10:10.99997 2001-01-01 10:10:10.9999 2001-01-01 10:10:10.99996 2001-01-01 10:10:10.9999 2001-01-01 10:10:10.99995 2001-01-01 10:10:10.9999 2001-01-01 10:10:10.99994 2001-01-01 10:10:10.9999 2001-01-01 10:10:10.99993 2001-01-01 10:10:10.9999 2001-01-01 10:10:10.99992 2001-01-01 10:10:10.9999 2001-01-01 10:10:10.99991 2001-01-01 10:10:10.9999 2001-01-01 10:10:10.99990 2001-01-01 10:10:10.9999 DROP TABLE t1; CREATE TABLE t1 (a DATETIME(4)); INSERT INTO t1 VALUES ('2001-01-01 10:10:10.9999'); INSERT INTO t1 VALUES ('2001-01-01 10:10:10.9998'); INSERT INTO t1 VALUES ('2001-01-01 10:10:10.9997'); INSERT INTO t1 VALUES ('2001-01-01 10:10:10.9996'); INSERT INTO t1 VALUES ('2001-01-01 10:10:10.9995'); INSERT INTO t1 VALUES ('2001-01-01 10:10:10.9994'); INSERT INTO t1 VALUES ('2001-01-01 10:10:10.9993'); INSERT INTO t1 VALUES ('2001-01-01 10:10:10.9992'); INSERT INTO t1 VALUES ('2001-01-01 10:10:10.9991'); INSERT INTO t1 VALUES ('2001-01-01 10:10:10.9990'); SELECT a, CAST(a AS DATETIME(3)) FROM t1; a CAST(a AS DATETIME(3)) 2001-01-01 10:10:10.9999 2001-01-01 10:10:10.999 2001-01-01 10:10:10.9998 2001-01-01 10:10:10.999 2001-01-01 10:10:10.9997 2001-01-01 10:10:10.999 2001-01-01 10:10:10.9996 2001-01-01 10:10:10.999 2001-01-01 10:10:10.9995 2001-01-01 10:10:10.999 2001-01-01 10:10:10.9994 2001-01-01 10:10:10.999 2001-01-01 10:10:10.9993 2001-01-01 10:10:10.999 2001-01-01 10:10:10.9992 2001-01-01 10:10:10.999 2001-01-01 10:10:10.9991 2001-01-01 10:10:10.999 2001-01-01 10:10:10.9990 2001-01-01 10:10:10.999 DROP TABLE t1; CREATE TABLE t1 (a DATETIME(3)); INSERT INTO t1 VALUES ('2001-01-01 10:10:10.999'); INSERT INTO t1 VALUES ('2001-01-01 10:10:10.998'); INSERT INTO t1 VALUES ('2001-01-01 10:10:10.997'); INSERT INTO t1 VALUES ('2001-01-01 10:10:10.996'); INSERT INTO t1 VALUES ('2001-01-01 10:10:10.995'); INSERT INTO t1 VALUES ('2001-01-01 10:10:10.994'); INSERT INTO t1 VALUES ('2001-01-01 10:10:10.993'); INSERT INTO t1 VALUES ('2001-01-01 10:10:10.992'); INSERT INTO t1 VALUES ('2001-01-01 10:10:10.991'); INSERT INTO t1 VALUES ('2001-01-01 10:10:10.990'); SELECT a, CAST(a AS DATETIME(2)) FROM t1; a CAST(a AS DATETIME(2)) 2001-01-01 10:10:10.999 2001-01-01 10:10:10.99 2001-01-01 10:10:10.998 2001-01-01 10:10:10.99 2001-01-01 10:10:10.997 2001-01-01 10:10:10.99 2001-01-01 10:10:10.996 2001-01-01 10:10:10.99 2001-01-01 10:10:10.995 2001-01-01 10:10:10.99 2001-01-01 10:10:10.994 2001-01-01 10:10:10.99 2001-01-01 10:10:10.993 2001-01-01 10:10:10.99 2001-01-01 10:10:10.992 2001-01-01 10:10:10.99 2001-01-01 10:10:10.991 2001-01-01 10:10:10.99 2001-01-01 10:10:10.990 2001-01-01 10:10:10.99 DROP TABLE t1; CREATE TABLE t1 (a DATETIME(2)); INSERT INTO t1 VALUES ('2001-01-01 10:10:10.99'); INSERT INTO t1 VALUES ('2001-01-01 10:10:10.98'); INSERT INTO t1 VALUES ('2001-01-01 10:10:10.97'); INSERT INTO t1 VALUES ('2001-01-01 10:10:10.96'); INSERT INTO t1 VALUES ('2001-01-01 10:10:10.95'); INSERT INTO t1 VALUES ('2001-01-01 10:10:10.94'); INSERT INTO t1 VALUES ('2001-01-01 10:10:10.93'); INSERT INTO t1 VALUES ('2001-01-01 10:10:10.92'); INSERT INTO t1 VALUES ('2001-01-01 10:10:10.91'); INSERT INTO t1 VALUES ('2001-01-01 10:10:10.90'); SELECT a, CAST(a AS DATETIME(1)) FROM t1; a CAST(a AS DATETIME(1)) 2001-01-01 10:10:10.99 2001-01-01 10:10:10.9 2001-01-01 10:10:10.98 2001-01-01 10:10:10.9 2001-01-01 10:10:10.97 2001-01-01 10:10:10.9 2001-01-01 10:10:10.96 2001-01-01 10:10:10.9 2001-01-01 10:10:10.95 2001-01-01 10:10:10.9 2001-01-01 10:10:10.94 2001-01-01 10:10:10.9 2001-01-01 10:10:10.93 2001-01-01 10:10:10.9 2001-01-01 10:10:10.92 2001-01-01 10:10:10.9 2001-01-01 10:10:10.91 2001-01-01 10:10:10.9 2001-01-01 10:10:10.90 2001-01-01 10:10:10.9 DROP TABLE t1; CREATE TABLE t1 (a DATETIME(1)); INSERT INTO t1 VALUES ('2001-01-01 10:10:10.9'); INSERT INTO t1 VALUES ('2001-01-01 10:10:10.8'); INSERT INTO t1 VALUES ('2001-01-01 10:10:10.7'); INSERT INTO t1 VALUES ('2001-01-01 10:10:10.6'); INSERT INTO t1 VALUES ('2001-01-01 10:10:10.5'); INSERT INTO t1 VALUES ('2001-01-01 10:10:10.4'); INSERT INTO t1 VALUES ('2001-01-01 10:10:10.3'); INSERT INTO t1 VALUES ('2001-01-01 10:10:10.2'); INSERT INTO t1 VALUES ('2001-01-01 10:10:10.1'); INSERT INTO t1 VALUES ('2001-01-01 10:10:10.0'); SELECT a, CAST(a AS DATETIME) FROM t1; a CAST(a AS DATETIME) 2001-01-01 10:10:10.9 2001-01-01 10:10:10 2001-01-01 10:10:10.8 2001-01-01 10:10:10 2001-01-01 10:10:10.7 2001-01-01 10:10:10 2001-01-01 10:10:10.6 2001-01-01 10:10:10 2001-01-01 10:10:10.5 2001-01-01 10:10:10 2001-01-01 10:10:10.4 2001-01-01 10:10:10 2001-01-01 10:10:10.3 2001-01-01 10:10:10 2001-01-01 10:10:10.2 2001-01-01 10:10:10 2001-01-01 10:10:10.1 2001-01-01 10:10:10 2001-01-01 10:10:10.0 2001-01-01 10:10:10 DROP TABLE t1; # # Testing that Item::get_date() do no rounds. # SELECT CAST('2001-01-01 10:10:10.9999994' AS DATETIME) AS c1, CAST('2001-01-01 10:10:10.9999995' AS DATETIME) AS c2, CAST('2001-01-01 10:10:10.9999994' AS DATETIME(6)) AS c3, CAST('2001-01-01 10:10:10.9999995' AS DATETIME(6)) AS c4, CAST(20010101101010.9999994 AS DATETIME) AS c5, CAST(20010101101010.9999995 AS DATETIME) AS c6, CAST(20010101101010.9999994 AS DATETIME(6)) AS c7, CAST(20010101101010.9999995 AS DATETIME(6)) AS c8; c1 2001-01-01 10:10:10 c2 2001-01-01 10:10:10 c3 2001-01-01 10:10:10.999999 c4 2001-01-01 10:10:10.999999 c5 2001-01-01 10:10:10 c6 2001-01-01 10:10:10 c7 2001-01-01 10:10:10.999999 c8 2001-01-01 10:10:10.999999 # # Testing that comparison do no rounds # CREATE TABLE t1 (t0 DATETIME, t6 DATETIME(6)); INSERT INTO t1 VALUES ('2001-01-01 00:00:00', '2001-01-01 00:00:00.999999'); INSERT INTO t1 VALUES ('2001-01-01 00:00:01', '2001-01-01 00:00:01.000000'); SELECT t0 FROM t1 WHERE t6='2001-01-01 00:00:00.9999998'; t0 2001-01-01 00:00:00 SELECT t6 FROM t1 WHERE t6='2001-01-01 00:00:00.9999998'; t6 2001-01-01 00:00:00.999999 DROP TABLE t1; # # Testing that EXTRACT do no rounds # SELECT EXTRACT(MICROSECOND FROM '2001-01-01 00:00:00.9999994') AS c1, EXTRACT(MICROSECOND FROM '2001-01-01 00:00:00.9999995') AS c2, EXTRACT(MICROSECOND FROM 20010101000000.9999994) AS c3, EXTRACT(MICROSECOND FROM 20010101000000.9999995) AS c4; c1 999999 c2 999999 c3 999999 c4 999999 # # Testing time_truncate_fractional when altering TIMESTAMP(N) to a smaller size # CREATE TABLE t1 (a TIMESTAMP(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6)); INSERT INTO t1 VALUES ('2001-01-01 10:10:10.999999'); ALTER TABLE t1 MODIFY a TIMESTAMP(5); SELECT * FROM t1; a 2001-01-01 10:10:10.99999 DROP TABLE t1; CREATE TABLE t1 (a TIMESTAMP(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6)); INSERT INTO t1 VALUES ('2001-01-01 10:10:10.999999'); ALTER TABLE t1 MODIFY a TIME; SELECT * FROM t1; a 10:10:10 DROP TABLE t1; # # Testing time_truncate_fractional from VARCHAR to TIMESTAMP(N) # CREATE TABLE t1 (a VARCHAR(32), t6 TIMESTAMP(6) NULL DEFAULT NULL, t5 TIMESTAMP(5) NULL DEFAULT NULL, t4 TIMESTAMP(4) NULL DEFAULT NULL, t3 TIMESTAMP(3) NULL DEFAULT NULL, t2 TIMESTAMP(2) NULL DEFAULT NULL, t1 TIMESTAMP(1) NULL DEFAULT NULL, t0 TIMESTAMP NULL DEFAULT NULL); INSERT INTO t1 (a) VALUES ('2001-01-01 10:10:10.9999999'); SELECT * FROM t1;; a 2001-01-01 10:10:10.9999999 t6 NULL t5 NULL t4 NULL t3 NULL t2 NULL t1 NULL t0 NULL UPDATE t1 SET t0=a, t1=a, t2=a, t3=a, t4=a, t5=a, t6=a; SELECT * FROM t1;; a 2001-01-01 10:10:10.9999999 t6 2001-01-01 10:10:10.999999 t5 2001-01-01 10:10:10.99999 t4 2001-01-01 10:10:10.9999 t3 2001-01-01 10:10:10.999 t2 2001-01-01 10:10:10.99 t1 2001-01-01 10:10:10.9 t0 2001-01-01 10:10:10 DROP TABLE t1; # # Testing time_truncate_fractional from TIMESTAMP(N) to BIGINT # CREATE TABLE t1 (a BIGINT, b TIMESTAMP(6) NULL DEFAULT NULL); INSERT INTO t1 (b) VALUES ('2001-01-01 10:10:59.500000'); INSERT INTO t1 (b) VALUES ('2001-01-01 10:10:10.500000'); INSERT INTO t1 (b) VALUES ('2001-01-01 10:10:10.499999'); UPDATE t1 SET a=b; SELECT * FROM t1; a b 20010101101059 2001-01-01 10:10:59.500000 20010101101010 2001-01-01 10:10:10.500000 20010101101010 2001-01-01 10:10:10.499999 DROP TABLE t1; # # Testing time_truncate_fractional from TIME(6) to TIMESTAMP(N) # SET timestamp=UNIX_TIMESTAMP('2001-01-01 00:00:00'); CREATE TABLE t1 (a TIME(6), t6 TIMESTAMP(6)); INSERT INTO t1 (a) VALUES ('10:10:10.999999'); SELECT * FROM t1; a t6 10:10:10.999999 NULL UPDATE t1 SET t6=a; SELECT * FROM t1;; a 10:10:10.999999 t6 2001-01-01 10:10:10.999999 DROP TABLE t1; SET timestamp=default; # # Testing time_truncate_fractional from DECIMAL to TIMESTAMP(N) # CREATE TABLE t1 (a DECIMAL(30,6), t6 TIMESTAMP(6)); INSERT INTO t1 (a) VALUES (20010101101010.999999); UPDATE t1 SET t6=a; SELECT * FROM t1;; a 20010101101010.999999 t6 2001-01-01 10:10:10.999999 DROP TABLE t1; # # Testing JOIN with comparison between TIMESTAMP(6) and DECIMAL(20,6) # CREATE TABLE t1 (a TIMESTAMP(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6)); INSERT INTO t1 VALUES ('2001-01-01 11:22:33'); INSERT INTO t1 VALUES ('2001-01-01 11:22:33.123'); INSERT INTO t1 VALUES ('2001-01-01 11:22:33.1234567'); CREATE TABLE t2 (b DECIMAL(20,6)); INSERT INTO t2 VALUES (20010101112233); INSERT INTO t2 VALUES (20010101112233.123); INSERT INTO t2 VALUES (20010101112233.1234567); Warnings: Note 1265 Data truncated for column 'b' at row 1 SELECT * FROM t1, t2 WHERE a=b; a b 2001-01-01 11:22:33.000000 20010101112233.000000 2001-01-01 11:22:33.123000 20010101112233.123000 2001-01-01 11:22:33.123456 20010101112233.123457 DROP TABLE t1, t2; # # Testing INSERT with near-maximum and near-minimum supported values for # TIMESTAMP. # SET time_zone='+00:00'; CREATE TABLE t1 (a TIMESTAMP(6)); INSERT INTO t1 VALUES ('1970-01-01 00:00:01.000000'); INSERT INTO t1 VALUES ('2038-01-19 03:14:07.9999999'); SELECT * FROM t1; a 1970-01-01 00:00:01.000000 2038-01-19 03:14:07.999999 DROP TABLE t1; CREATE TABLE t1 (a TIMESTAMP); INSERT INTO t1 VALUES ('1970-01-01 00:00:01.000000'); INSERT INTO t1 VALUES ('2038-01-19 03:14:07.999999'); SELECT * FROM t1; a 1970-01-01 00:00:01 2038-01-19 03:14:07 DROP TABLE t1; # # Testing INSERT with near-maximum and near-minimum supported values for # DATETIME. # CREATE TABLE t1 (a DATETIME(6)); INSERT INTO t1 VALUES ('9999-12-31 23:59:59.9999999'); INSERT INTO t1 VALUES ('1000-01-01 00:00:00.000000'); SELECT * FROM t1; a 9999-12-31 23:59:59.999999 1000-01-01 00:00:00.000000 DROP TABLE t1; CREATE TABLE t1 (a DATETIME); INSERT INTO t1 VALUES ('9999-12-31 23:59:59.999999'); INSERT INTO t1 VALUES ('1000-01-01 00:00:00.000000'); SELECT * FROM t1; a 9999-12-31 23:59:59 1000-01-01 00:00:00 DROP TABLE t1; SET sql_mode=DEFAULT;