SET sql_mode=time_truncate_fractional;
#
# 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');
Warnings:
Warning	1264	Out of range value for column 'a' at row 1
INSERT INTO t1 VALUES ('-838:59:59.9999999');
Warnings:
Warning	1264	Out of range value for column 'a' at row 1
SELECT * FROM t1;
a
838:59:59.000000
-838:59:59.000000
DROP TABLE t1;
CREATE TABLE t1 (a TIME);
INSERT INTO t1 VALUES ('838:59:59.9999999');
Warnings:
Warning	1264	Out of range value for column 'a' at row 1
INSERT INTO t1 VALUES ('-838:59:59.9999999');
Warnings:
Warning	1264	Out of range value for column 'a' at row 1
SELECT * FROM t1;
a
838:59:59
-838:59:59
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 b=a;
a	b
-11:22:33.123456	-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) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_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=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_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), t5 TIMESTAMP(5), t4 TIMESTAMP(4),
t3 TIMESTAMP(3), t2 TIMESTAMP(2), t1 TIMESTAMP(1),
t0 TIMESTAMP);
INSERT INTO t1 (a) VALUES ('10:10:10.999999');
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	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;
SET timestamp=default;
#
# Testing time_truncate_fractional from DECIMAL to TIMESTAMP(N)
#
CREATE TABLE t1 (a DECIMAL(30,6),
t6 TIMESTAMP(6), t5 TIMESTAMP(5), t4 TIMESTAMP(4),
t3 TIMESTAMP(3), t2 TIMESTAMP(2), t1 TIMESTAMP(1),
t0 TIMESTAMP);
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 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;