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