792 lines
24 KiB
Plaintext
792 lines
24 KiB
Plaintext
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;
|