1SET sql_mode=IF(@@version LIKE '%MariaDB%', 'TIME_ROUND_FRACTIONAL', ''); 2SET @default_sql_mode=@@sql_mode; 3CREATE TABLE t1_datetime_in_varchar (id SERIAL, a VARCHAR(64)); 4INSERT INTO t1_datetime_in_varchar (a) VALUES 5('2000-12-31 23:59:59'), 6('2000-12-31 23:59:59.9'), 7('2000-12-31 23:59:59.99'), 8('2000-12-31 23:59:59.999'), 9('2000-12-31 23:59:59.9999'), 10('2000-12-31 23:59:59.99999'), 11('2000-12-31 23:59:59.999999'), 12('2000-12-31 23:59:59.9999999'); 13CREATE TABLE t1_datetime_in_decimal (id SERIAL, a DECIMAL(38,10)); 14INSERT INTO t1_datetime_in_decimal (a) VALUES 15(20001231235959), 16(20001231235959.9), 17(20001231235959.99), 18(20001231235959.999), 19(20001231235959.9999), 20(20001231235959.99999), 21(20001231235959.999999), 22(20001231235959.9999999); 23CREATE TABLE t1_time_in_varchar (id SERIAL, a VARCHAR(64)); 24INSERT INTO t1_time_in_varchar (a) VALUES 25('00:00:00'), 26('00:00:00.9'), 27('00:00:00.99'), 28('00:00:00.999'), 29('00:00:00.9999'), 30('00:00:00.99999'), 31('00:00:00.999999'), 32('00:00:00.9999999'); 33INSERT INTO t1_time_in_varchar (a) VALUES 34('837:59:59.9999999'), 35('838:59:59'), 36('838:59:59.9'), 37('838:59:59.99'), 38('838:59:59.999'), 39('838:59:59.9999'), 40('838:59:59.99999'), 41('838:59:59.999999'), 42('838:59:59.9999999'), 43('839:59:59.9999999'), 44('87649414:59:59.999999'), 45('87649414:59:59.9999999'), 46('87649415:59:59.999999'), 47('87649415:59:59.9999999'); 48CREATE TABLE t1_time_in_decimal (id SERIAL, a DECIMAL(38,10)); 49INSERT INTO t1_time_in_decimal (a) VALUES 50(0), 51(0.9), 52(0.99), 53(0.999), 54(0.9999), 55(0.99999), 56(0.999999), 57(0.9999999); 58INSERT INTO t1_time_in_decimal (a) VALUES 59(8375959.9999999), 60(8385959), 61(8385959.9), 62(8385959.99), 63(8385959.999), 64(8385959.9999), 65(8385959.99999), 66(8385959.999999), 67(8385959.9999999), 68(8395959.9999999), 69(876494145959.999999), 70(876494145959.9999999), 71(876494155959.999999), 72(876494155959.9999999); 73# 74# TIME: LEAST/GREATEST 75# 76SELECT GREATEST(TIME'00:00:00', a) FROM t1_time_in_varchar; 77GREATEST(TIME'00:00:00', a) 7800:00:00.000000 7900:00:00.900000 8000:00:00.990000 8100:00:00.999000 8200:00:00.999900 8300:00:00.999990 8400:00:00.999999 8500:00:01.000000 86838:00:00.000000 87838:59:59.000000 88838:59:59.900000 89838:59:59.990000 90838:59:59.999000 91838:59:59.999900 92838:59:59.999990 93838:59:59.999999 94838:59:59.999999 95838:59:59.999999 96838:59:59.999999 97838:59:59.999999 98838:59:59.999999 99838:59:59.999999 100Warnings: 101Note 1292 Truncated incorrect time value: '00:00:00.9999999' 102Note 1292 Truncated incorrect time value: '837:59:59.9999999' 103Warning 1292 Truncated incorrect time value: '838:59:59.9999999' 104Warning 1292 Truncated incorrect time value: '839:59:59.9999999' 105Warning 1292 Truncated incorrect time value: '87649414:59:59.999999' 106Warning 1292 Truncated incorrect time value: '87649414:59:59.9999999' 107Warning 1292 Truncated incorrect time value: '87649415:59:59.999999' 108Warning 1292 Truncated incorrect time value: '87649415:59:59.9999999' 109SELECT GREATEST(TIME'00:00:00', a) FROM t1_time_in_decimal; 110GREATEST(TIME'00:00:00', a) 11100:00:00.000000 11200:00:00.900000 11300:00:00.990000 11400:00:00.999000 11500:00:00.999900 11600:00:00.999990 11700:00:00.999999 11800:00:01.000000 119838:00:00.000000 120838:59:59.000000 121838:59:59.900000 122838:59:59.990000 123838:59:59.999000 124838:59:59.999900 125838:59:59.999990 126838:59:59.999999 127838:59:59.999999 128838:59:59.999999 129NULL 130NULL 131NULL 132NULL 133Warnings: 134Warning 1292 Incorrect time value: '8385959.9999999000' for column `test`.`t1_time_in_decimal`.`a` at row 17 135Warning 1292 Incorrect time value: '8395959.9999999000' for column `test`.`t1_time_in_decimal`.`a` at row 18 136Warning 1292 Incorrect time value: '876494145959.9999990000' for column `test`.`t1_time_in_decimal`.`a` at row 19 137Warning 1292 Incorrect time value: '876494145959.9999999000' for column `test`.`t1_time_in_decimal`.`a` at row 20 138Warning 1292 Incorrect time value: '876494155959.9999990000' for column `test`.`t1_time_in_decimal`.`a` at row 21 139Warning 1292 Incorrect time value: '876494155959.9999999000' for column `test`.`t1_time_in_decimal`.`a` at row 22 140SELECT GREATEST(TIME'00:00:00', '00:00:00.0000004'); 141GREATEST(TIME'00:00:00', '00:00:00.0000004') 14200:00:00.000000 143Warnings: 144Note 1292 Truncated incorrect time value: '00:00:00.0000004' 145SELECT GREATEST(TIME'00:00:00', 0.0000004); 146GREATEST(TIME'00:00:00', 0.0000004) 14700:00:00.000000 148SELECT GREATEST(TIME'00:00:00', '00:00:00.0000005'); 149GREATEST(TIME'00:00:00', '00:00:00.0000005') 15000:00:00.000001 151Warnings: 152Note 1292 Truncated incorrect time value: '00:00:00.0000005' 153SELECT GREATEST(TIME'00:00:00', 0.0000005); 154GREATEST(TIME'00:00:00', 0.0000005) 15500:00:00.000001 156# 157# Functions with a single TIME input, conversion from DATETIME-in-VARCHAR 158# 159SELECT SECOND(a), CAST(a AS TIME(6)), a FROM t1_datetime_in_varchar ORDER BY id; 160SECOND(a) CAST(a AS TIME(6)) a 16159 23:59:59.000000 2000-12-31 23:59:59 16259 23:59:59.900000 2000-12-31 23:59:59.9 16359 23:59:59.990000 2000-12-31 23:59:59.99 16459 23:59:59.999000 2000-12-31 23:59:59.999 16559 23:59:59.999900 2000-12-31 23:59:59.9999 16659 23:59:59.999990 2000-12-31 23:59:59.99999 16759 23:59:59.999999 2000-12-31 23:59:59.999999 1680 00:00:00.000000 2000-12-31 23:59:59.9999999 169Warnings: 170Note 1292 Truncated incorrect datetime value: '2000-12-31 23:59:59.9999999' 171Note 1292 Truncated incorrect datetime value: '2000-12-31 23:59:59.9999999' 172SELECT MINUTE(a), CAST(a AS TIME(6)), a FROM t1_datetime_in_varchar ORDER BY id; 173MINUTE(a) CAST(a AS TIME(6)) a 17459 23:59:59.000000 2000-12-31 23:59:59 17559 23:59:59.900000 2000-12-31 23:59:59.9 17659 23:59:59.990000 2000-12-31 23:59:59.99 17759 23:59:59.999000 2000-12-31 23:59:59.999 17859 23:59:59.999900 2000-12-31 23:59:59.9999 17959 23:59:59.999990 2000-12-31 23:59:59.99999 18059 23:59:59.999999 2000-12-31 23:59:59.999999 1810 00:00:00.000000 2000-12-31 23:59:59.9999999 182Warnings: 183Note 1292 Truncated incorrect datetime value: '2000-12-31 23:59:59.9999999' 184Note 1292 Truncated incorrect datetime value: '2000-12-31 23:59:59.9999999' 185SELECT HOUR(a), CAST(a AS TIME(6)), a FROM t1_datetime_in_varchar ORDER BY id; 186HOUR(a) CAST(a AS TIME(6)) a 18723 23:59:59.000000 2000-12-31 23:59:59 18823 23:59:59.900000 2000-12-31 23:59:59.9 18923 23:59:59.990000 2000-12-31 23:59:59.99 19023 23:59:59.999000 2000-12-31 23:59:59.999 19123 23:59:59.999900 2000-12-31 23:59:59.9999 19223 23:59:59.999990 2000-12-31 23:59:59.99999 19323 23:59:59.999999 2000-12-31 23:59:59.999999 1940 00:00:00.000000 2000-12-31 23:59:59.9999999 195Warnings: 196Note 1292 Truncated incorrect datetime value: '2000-12-31 23:59:59.9999999' 197Note 1292 Truncated incorrect datetime value: '2000-12-31 23:59:59.9999999' 198SELECT EXTRACT(SECOND FROM a), CAST(a AS TIME(6)), a FROM t1_datetime_in_varchar ORDER BY id; 199EXTRACT(SECOND FROM a) CAST(a AS TIME(6)) a 20059 23:59:59.000000 2000-12-31 23:59:59 20159 23:59:59.900000 2000-12-31 23:59:59.9 20259 23:59:59.990000 2000-12-31 23:59:59.99 20359 23:59:59.999000 2000-12-31 23:59:59.999 20459 23:59:59.999900 2000-12-31 23:59:59.9999 20559 23:59:59.999990 2000-12-31 23:59:59.99999 20659 23:59:59.999999 2000-12-31 23:59:59.999999 2070 00:00:00.000000 2000-12-31 23:59:59.9999999 208Warnings: 209Note 1292 Truncated incorrect datetime value: '2000-12-31 23:59:59.9999999' 210Note 1292 Truncated incorrect datetime value: '2000-12-31 23:59:59.9999999' 211SELECT EXTRACT(MINUTE FROM a), CAST(a AS TIME(6)), a FROM t1_datetime_in_varchar ORDER BY id; 212EXTRACT(MINUTE FROM a) CAST(a AS TIME(6)) a 21359 23:59:59.000000 2000-12-31 23:59:59 21459 23:59:59.900000 2000-12-31 23:59:59.9 21559 23:59:59.990000 2000-12-31 23:59:59.99 21659 23:59:59.999000 2000-12-31 23:59:59.999 21759 23:59:59.999900 2000-12-31 23:59:59.9999 21859 23:59:59.999990 2000-12-31 23:59:59.99999 21959 23:59:59.999999 2000-12-31 23:59:59.999999 2200 00:00:00.000000 2000-12-31 23:59:59.9999999 221Warnings: 222Note 1292 Truncated incorrect datetime value: '2000-12-31 23:59:59.9999999' 223Note 1292 Truncated incorrect datetime value: '2000-12-31 23:59:59.9999999' 224SELECT EXTRACT(HOUR FROM a), CAST(a AS TIME(6)), a FROM t1_datetime_in_varchar ORDER BY id; 225EXTRACT(HOUR FROM a) CAST(a AS TIME(6)) a 22623 23:59:59.000000 2000-12-31 23:59:59 22723 23:59:59.900000 2000-12-31 23:59:59.9 22823 23:59:59.990000 2000-12-31 23:59:59.99 22923 23:59:59.999000 2000-12-31 23:59:59.999 23023 23:59:59.999900 2000-12-31 23:59:59.9999 23123 23:59:59.999990 2000-12-31 23:59:59.99999 23223 23:59:59.999999 2000-12-31 23:59:59.999999 2330 00:00:00.000000 2000-12-31 23:59:59.9999999 234Warnings: 235Note 1292 Truncated incorrect datetime value: '2000-12-31 23:59:59.9999999' 236Note 1292 Truncated incorrect datetime value: '2000-12-31 23:59:59.9999999' 237SELECT TIME_TO_SEC(a), CAST(a AS TIME(6)), a FROM t1_datetime_in_varchar ORDER BY id; 238TIME_TO_SEC(a) CAST(a AS TIME(6)) a 23986399.000000 23:59:59.000000 2000-12-31 23:59:59 24086399.900000 23:59:59.900000 2000-12-31 23:59:59.9 24186399.990000 23:59:59.990000 2000-12-31 23:59:59.99 24286399.999000 23:59:59.999000 2000-12-31 23:59:59.999 24386399.999900 23:59:59.999900 2000-12-31 23:59:59.9999 24486399.999990 23:59:59.999990 2000-12-31 23:59:59.99999 24586399.999999 23:59:59.999999 2000-12-31 23:59:59.999999 2460.000000 00:00:00.000000 2000-12-31 23:59:59.9999999 247Warnings: 248Note 1292 Truncated incorrect datetime value: '2000-12-31 23:59:59.9999999' 249Note 1292 Truncated incorrect datetime value: '2000-12-31 23:59:59.9999999' 250# 251# Functions with a single TIME input, conversion from DATETIME-in-DECIMAL 252# 253SELECT SECOND(a), CAST(a AS TIME(6)), a FROM t1_datetime_in_decimal ORDER BY id; 254SECOND(a) CAST(a AS TIME(6)) a 25559 23:59:59.000000 20001231235959.0000000000 25659 23:59:59.900000 20001231235959.9000000000 25759 23:59:59.990000 20001231235959.9900000000 25859 23:59:59.999000 20001231235959.9990000000 25959 23:59:59.999900 20001231235959.9999000000 26059 23:59:59.999990 20001231235959.9999900000 26159 23:59:59.999999 20001231235959.9999990000 2620 00:00:00.000000 20001231235959.9999999000 263SELECT MINUTE(a), CAST(a AS TIME(6)), a FROM t1_datetime_in_decimal ORDER BY id; 264MINUTE(a) CAST(a AS TIME(6)) a 26559 23:59:59.000000 20001231235959.0000000000 26659 23:59:59.900000 20001231235959.9000000000 26759 23:59:59.990000 20001231235959.9900000000 26859 23:59:59.999000 20001231235959.9990000000 26959 23:59:59.999900 20001231235959.9999000000 27059 23:59:59.999990 20001231235959.9999900000 27159 23:59:59.999999 20001231235959.9999990000 2720 00:00:00.000000 20001231235959.9999999000 273SELECT HOUR(a), CAST(a AS TIME(6)), a FROM t1_datetime_in_decimal ORDER BY id; 274HOUR(a) CAST(a AS TIME(6)) a 27523 23:59:59.000000 20001231235959.0000000000 27623 23:59:59.900000 20001231235959.9000000000 27723 23:59:59.990000 20001231235959.9900000000 27823 23:59:59.999000 20001231235959.9990000000 27923 23:59:59.999900 20001231235959.9999000000 28023 23:59:59.999990 20001231235959.9999900000 28123 23:59:59.999999 20001231235959.9999990000 2820 00:00:00.000000 20001231235959.9999999000 283SELECT EXTRACT(SECOND FROM a), CAST(a AS TIME(6)), a FROM t1_datetime_in_decimal ORDER BY id; 284EXTRACT(SECOND FROM a) CAST(a AS TIME(6)) a 28559 23:59:59.000000 20001231235959.0000000000 28659 23:59:59.900000 20001231235959.9000000000 28759 23:59:59.990000 20001231235959.9900000000 28859 23:59:59.999000 20001231235959.9990000000 28959 23:59:59.999900 20001231235959.9999000000 29059 23:59:59.999990 20001231235959.9999900000 29159 23:59:59.999999 20001231235959.9999990000 2920 00:00:00.000000 20001231235959.9999999000 293SELECT EXTRACT(MINUTE FROM a), CAST(a AS TIME(6)), a FROM t1_datetime_in_decimal ORDER BY id; 294EXTRACT(MINUTE FROM a) CAST(a AS TIME(6)) a 29559 23:59:59.000000 20001231235959.0000000000 29659 23:59:59.900000 20001231235959.9000000000 29759 23:59:59.990000 20001231235959.9900000000 29859 23:59:59.999000 20001231235959.9990000000 29959 23:59:59.999900 20001231235959.9999000000 30059 23:59:59.999990 20001231235959.9999900000 30159 23:59:59.999999 20001231235959.9999990000 3020 00:00:00.000000 20001231235959.9999999000 303SELECT EXTRACT(HOUR FROM a), CAST(a AS TIME(6)), a FROM t1_datetime_in_decimal ORDER BY id; 304EXTRACT(HOUR FROM a) CAST(a AS TIME(6)) a 30523 23:59:59.000000 20001231235959.0000000000 30623 23:59:59.900000 20001231235959.9000000000 30723 23:59:59.990000 20001231235959.9900000000 30823 23:59:59.999000 20001231235959.9990000000 30923 23:59:59.999900 20001231235959.9999000000 31023 23:59:59.999990 20001231235959.9999900000 31123 23:59:59.999999 20001231235959.9999990000 3120 00:00:00.000000 20001231235959.9999999000 313SELECT TIME_TO_SEC(a), CAST(a AS TIME(6)), a FROM t1_datetime_in_decimal ORDER BY id; 314TIME_TO_SEC(a) CAST(a AS TIME(6)) a 31586399.000000 23:59:59.000000 20001231235959.0000000000 31686399.900000 23:59:59.900000 20001231235959.9000000000 31786399.990000 23:59:59.990000 20001231235959.9900000000 31886399.999000 23:59:59.999000 20001231235959.9990000000 31986399.999900 23:59:59.999900 20001231235959.9999000000 32086399.999990 23:59:59.999990 20001231235959.9999900000 32186399.999999 23:59:59.999999 20001231235959.9999990000 3220.000000 00:00:00.000000 20001231235959.9999999000 323# 324# Functions with a single TIME interval input, conversion from TIME-interval-in-VARCHAR 325# 326SELECT 327EXTRACT(DAY FROM a), 328EXTRACT(HOUR FROM a), 329EXTRACT(MINUTE FROM a), 330EXTRACT(SECOND FROM a), 331EXTRACT(MICROSECOND FROM a), 332CAST(a AS INTERVAL DAY_SECOND(6)), 333a 334FROM t1_time_in_varchar ORDER BY id; 335EXTRACT(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 3360 0 0 0 0 00:00:00.000000 00:00:00 3370 0 0 0 900000 00:00:00.900000 00:00:00.9 3380 0 0 0 990000 00:00:00.990000 00:00:00.99 3390 0 0 0 999000 00:00:00.999000 00:00:00.999 3400 0 0 0 999900 00:00:00.999900 00:00:00.9999 3410 0 0 0 999990 00:00:00.999990 00:00:00.99999 3420 0 0 0 999999 00:00:00.999999 00:00:00.999999 3430 0 0 1 0 00:00:01.000000 00:00:00.9999999 34434 22 0 0 0 34 22:00:00.000000 837:59:59.9999999 34534 22 59 59 0 34 22:59:59.000000 838:59:59 34634 22 59 59 900000 34 22:59:59.900000 838:59:59.9 34734 22 59 59 990000 34 22:59:59.990000 838:59:59.99 34834 22 59 59 999000 34 22:59:59.999000 838:59:59.999 34934 22 59 59 999900 34 22:59:59.999900 838:59:59.9999 35034 22 59 59 999990 34 22:59:59.999990 838:59:59.99999 35134 22 59 59 999999 34 22:59:59.999999 838:59:59.999999 35234 23 0 0 0 34 23:00:00.000000 838:59:59.9999999 35335 0 0 0 0 35 00:00:00.000000 839:59:59.9999999 3543652058 22 59 59 999999 3652058 22:59:59.999999 87649414:59:59.999999 3553652058 23 0 0 0 3652058 23:00:00.000000 87649414:59:59.9999999 3563652058 23 59 59 999999 3652058 23:59:59.999999 87649415:59:59.999999 3573652058 23 59 59 999999 3652058 23:59:59.999999 87649415:59:59.9999999 358Warnings: 359Note 1292 Truncated incorrect time value: '00:00:00.9999999' 360Note 1292 Truncated incorrect time value: '00:00:00.9999999' 361Note 1292 Truncated incorrect time value: '00:00:00.9999999' 362Note 1292 Truncated incorrect time value: '00:00:00.9999999' 363Note 1292 Truncated incorrect time value: '00:00:00.9999999' 364Note 1292 Truncated incorrect INTERVAL DAY TO SECOND value: '00:00:00.9999999' 365Note 1292 Truncated incorrect time value: '837:59:59.9999999' 366Note 1292 Truncated incorrect time value: '837:59:59.9999999' 367Note 1292 Truncated incorrect time value: '837:59:59.9999999' 368Note 1292 Truncated incorrect time value: '837:59:59.9999999' 369Note 1292 Truncated incorrect time value: '837:59:59.9999999' 370Note 1292 Truncated incorrect INTERVAL DAY TO SECOND value: '837:59:59.9999999' 371Note 1292 Truncated incorrect time value: '838:59:59.9999999' 372Note 1292 Truncated incorrect time value: '838:59:59.9999999' 373Note 1292 Truncated incorrect time value: '838:59:59.9999999' 374Note 1292 Truncated incorrect time value: '838:59:59.9999999' 375Note 1292 Truncated incorrect time value: '838:59:59.9999999' 376Note 1292 Truncated incorrect INTERVAL DAY TO SECOND value: '838:59:59.9999999' 377Note 1292 Truncated incorrect time value: '839:59:59.9999999' 378Note 1292 Truncated incorrect time value: '839:59:59.9999999' 379Note 1292 Truncated incorrect time value: '839:59:59.9999999' 380Note 1292 Truncated incorrect time value: '839:59:59.9999999' 381Note 1292 Truncated incorrect time value: '839:59:59.9999999' 382Note 1292 Truncated incorrect INTERVAL DAY TO SECOND value: '839:59:59.9999999' 383Note 1292 Truncated incorrect time value: '87649414:59:59.9999999' 384Note 1292 Truncated incorrect time value: '87649414:59:59.9999999' 385Note 1292 Truncated incorrect time value: '87649414:59:59.9999999' 386Note 1292 Truncated incorrect time value: '87649414:59:59.9999999' 387Note 1292 Truncated incorrect time value: '87649414:59:59.9999999' 388Note 1292 Truncated incorrect INTERVAL DAY TO SECOND value: '87649414:59:59.9999999' 389Warning 1292 Truncated incorrect time value: '87649415:59:59.9999999' 390Warning 1292 Truncated incorrect time value: '87649415:59:59.9999999' 391Warning 1292 Truncated incorrect time value: '87649415:59:59.9999999' 392Warning 1292 Truncated incorrect time value: '87649415:59:59.9999999' 393Warning 1292 Truncated incorrect time value: '87649415:59:59.9999999' 394Warning 1292 Incorrect INTERVAL DAY TO SECOND value: '87649415:59:59.9999999' 395SELECT 396TIME_TO_SEC(a), 397CAST(a AS TIME(6)), 398a 399FROM t1_time_in_varchar ORDER BY id; 400TIME_TO_SEC(a) CAST(a AS TIME(6)) a 4010.000000 00:00:00.000000 00:00:00 4020.900000 00:00:00.900000 00:00:00.9 4030.990000 00:00:00.990000 00:00:00.99 4040.999000 00:00:00.999000 00:00:00.999 4050.999900 00:00:00.999900 00:00:00.9999 4060.999990 00:00:00.999990 00:00:00.99999 4070.999999 00:00:00.999999 00:00:00.999999 4081.000000 00:00:01.000000 00:00:00.9999999 4093016800.000000 838:00:00.000000 837:59:59.9999999 4103020399.000000 838:59:59.000000 838:59:59 4113020399.900000 838:59:59.900000 838:59:59.9 4123020399.990000 838:59:59.990000 838:59:59.99 4133020399.999000 838:59:59.999000 838:59:59.999 4143020399.999900 838:59:59.999900 838:59:59.9999 4153020399.999990 838:59:59.999990 838:59:59.99999 4163020399.999999 838:59:59.999999 838:59:59.999999 4173020399.999999 838:59:59.999999 838:59:59.9999999 4183020399.999999 838:59:59.999999 839:59:59.9999999 4193020399.999999 838:59:59.999999 87649414:59:59.999999 4203020399.999999 838:59:59.999999 87649414:59:59.9999999 4213020399.999999 838:59:59.999999 87649415:59:59.999999 4223020399.999999 838:59:59.999999 87649415:59:59.9999999 423Warnings: 424Note 1292 Truncated incorrect time value: '00:00:00.9999999' 425Note 1292 Truncated incorrect time value: '00:00:00.9999999' 426Note 1292 Truncated incorrect time value: '837:59:59.9999999' 427Note 1292 Truncated incorrect time value: '837:59:59.9999999' 428Warning 1292 Truncated incorrect time value: '838:59:59.9999999' 429Warning 1292 Truncated incorrect time value: '838:59:59.9999999' 430Warning 1292 Truncated incorrect time value: '839:59:59.9999999' 431Warning 1292 Truncated incorrect time value: '839:59:59.9999999' 432Warning 1292 Truncated incorrect time value: '87649414:59:59.999999' 433Warning 1292 Truncated incorrect time value: '87649414:59:59.999999' 434Warning 1292 Truncated incorrect time value: '87649414:59:59.9999999' 435Warning 1292 Truncated incorrect time value: '87649414:59:59.9999999' 436Warning 1292 Truncated incorrect time value: '87649415:59:59.999999' 437Warning 1292 Truncated incorrect time value: '87649415:59:59.999999' 438Warning 1292 Truncated incorrect time value: '87649415:59:59.9999999' 439Warning 1292 Truncated incorrect time value: '87649415:59:59.9999999' 440# 441# Functions with a single TIME interval input, conversion from TIME-interval-in-DECIMAL 442# 443SELECT 444EXTRACT(DAY FROM a), 445EXTRACT(HOUR FROM a), 446EXTRACT(MINUTE FROM a), 447EXTRACT(SECOND FROM a), 448EXTRACT(MICROSECOND FROM a), 449CAST(a AS INTERVAL DAY_SECOND(6)), 450a 451FROM t1_time_in_decimal ORDER BY id; 452EXTRACT(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 4530 0 0 0 0 00:00:00.000000 0.0000000000 4540 0 0 0 900000 00:00:00.900000 0.9000000000 4550 0 0 0 990000 00:00:00.990000 0.9900000000 4560 0 0 0 999000 00:00:00.999000 0.9990000000 4570 0 0 0 999900 00:00:00.999900 0.9999000000 4580 0 0 0 999990 00:00:00.999990 0.9999900000 4590 0 0 0 999999 00:00:00.999999 0.9999990000 4600 0 0 1 0 00:00:01.000000 0.9999999000 46134 22 0 0 0 34 22:00:00.000000 8375959.9999999000 46234 22 59 59 0 34 22:59:59.000000 8385959.0000000000 46334 22 59 59 900000 34 22:59:59.900000 8385959.9000000000 46434 22 59 59 990000 34 22:59:59.990000 8385959.9900000000 46534 22 59 59 999000 34 22:59:59.999000 8385959.9990000000 46634 22 59 59 999900 34 22:59:59.999900 8385959.9999000000 46734 22 59 59 999990 34 22:59:59.999990 8385959.9999900000 46834 22 59 59 999999 34 22:59:59.999999 8385959.9999990000 46934 23 0 0 0 34 23:00:00.000000 8385959.9999999000 47035 0 0 0 0 35 00:00:00.000000 8395959.9999999000 4713652058 22 59 59 999999 3652058 22:59:59.999999 876494145959.9999990000 4723652058 23 0 0 0 3652058 23:00:00.000000 876494145959.9999999000 4733652058 23 59 59 999999 3652058 23:59:59.999999 876494155959.9999990000 4743652058 23 59 59 999999 3652058 23:59:59.999999 876494155959.9999999000 475Warnings: 476Note 1292 Truncated incorrect INTERVAL DAY TO SECOND value: '0.0000000000' 477Note 1292 Truncated incorrect INTERVAL DAY TO SECOND value: '0.9000000000' 478Note 1292 Truncated incorrect INTERVAL DAY TO SECOND value: '0.9900000000' 479Note 1292 Truncated incorrect INTERVAL DAY TO SECOND value: '0.9990000000' 480Note 1292 Truncated incorrect INTERVAL DAY TO SECOND value: '0.9999000000' 481Note 1292 Truncated incorrect INTERVAL DAY TO SECOND value: '0.9999900000' 482Note 1292 Truncated incorrect INTERVAL DAY TO SECOND value: '0.9999990000' 483Note 1292 Truncated incorrect INTERVAL DAY TO SECOND value: '0.9999999000' 484Note 1292 Truncated incorrect INTERVAL DAY TO SECOND value: '8375959.9999999000' 485Note 1292 Truncated incorrect INTERVAL DAY TO SECOND value: '8385959.0000000000' 486Note 1292 Truncated incorrect INTERVAL DAY TO SECOND value: '8385959.9000000000' 487Note 1292 Truncated incorrect INTERVAL DAY TO SECOND value: '8385959.9900000000' 488Note 1292 Truncated incorrect INTERVAL DAY TO SECOND value: '8385959.9990000000' 489Note 1292 Truncated incorrect INTERVAL DAY TO SECOND value: '8385959.9999000000' 490Note 1292 Truncated incorrect INTERVAL DAY TO SECOND value: '8385959.9999900000' 491Note 1292 Truncated incorrect INTERVAL DAY TO SECOND value: '8385959.9999990000' 492Note 1292 Truncated incorrect INTERVAL DAY TO SECOND value: '8385959.9999999000' 493Note 1292 Truncated incorrect INTERVAL DAY TO SECOND value: '8395959.9999999000' 494Note 1292 Truncated incorrect INTERVAL DAY TO SECOND value: '876494145959.9999990000' 495Note 1292 Truncated incorrect INTERVAL DAY TO SECOND value: '876494145959.9999999000' 496Note 1292 Truncated incorrect INTERVAL DAY TO SECOND value: '876494155959.9999990000' 497Warning 1292 Incorrect time value: '876494155959.9999999000' for column `test`.`t1_time_in_decimal`.`a` at row 22 498Warning 1292 Incorrect time value: '876494155959.9999999000' for column `test`.`t1_time_in_decimal`.`a` at row 22 499Warning 1292 Incorrect time value: '876494155959.9999999000' for column `test`.`t1_time_in_decimal`.`a` at row 22 500Warning 1292 Incorrect time value: '876494155959.9999999000' for column `test`.`t1_time_in_decimal`.`a` at row 22 501Warning 1292 Incorrect time value: '876494155959.9999999000' for column `test`.`t1_time_in_decimal`.`a` at row 22 502Warning 1292 Incorrect INTERVAL DAY TO SECOND value: '876494155959.9999999000' 503SELECT 504TIME_TO_SEC(a), 505CAST(a AS TIME(6)), 506a 507FROM t1_time_in_decimal ORDER BY id; 508TIME_TO_SEC(a) CAST(a AS TIME(6)) a 5090.000000 00:00:00.000000 0.0000000000 5100.900000 00:00:00.900000 0.9000000000 5110.990000 00:00:00.990000 0.9900000000 5120.999000 00:00:00.999000 0.9990000000 5130.999900 00:00:00.999900 0.9999000000 5140.999990 00:00:00.999990 0.9999900000 5150.999999 00:00:00.999999 0.9999990000 5161.000000 00:00:01.000000 0.9999999000 5173016800.000000 838:00:00.000000 8375959.9999999000 5183020399.000000 838:59:59.000000 8385959.0000000000 5193020399.900000 838:59:59.900000 8385959.9000000000 5203020399.990000 838:59:59.990000 8385959.9900000000 5213020399.999000 838:59:59.999000 8385959.9990000000 5223020399.999900 838:59:59.999900 8385959.9999000000 5233020399.999990 838:59:59.999990 8385959.9999900000 5243020399.999999 838:59:59.999999 8385959.9999990000 5253020399.999999 838:59:59.999999 8385959.9999999000 5263020399.999999 838:59:59.999999 8395959.9999999000 527NULL NULL 876494145959.9999990000 528NULL NULL 876494145959.9999999000 529NULL NULL 876494155959.9999990000 530NULL NULL 876494155959.9999999000 531Warnings: 532Warning 1292 Incorrect time value: '8385959.9999999000' for column `test`.`t1_time_in_decimal`.`a` at row 17 533Warning 1292 Incorrect time value: '8385959.9999999000' for column `test`.`t1_time_in_decimal`.`a` at row 17 534Warning 1292 Incorrect time value: '8395959.9999999000' for column `test`.`t1_time_in_decimal`.`a` at row 18 535Warning 1292 Incorrect time value: '8395959.9999999000' for column `test`.`t1_time_in_decimal`.`a` at row 18 536Warning 1292 Incorrect time value: '876494145959.9999990000' for column `test`.`t1_time_in_decimal`.`a` at row 19 537Warning 1292 Incorrect time value: '876494145959.9999990000' for column `test`.`t1_time_in_decimal`.`a` at row 19 538Warning 1292 Incorrect time value: '876494145959.9999999000' for column `test`.`t1_time_in_decimal`.`a` at row 20 539Warning 1292 Incorrect time value: '876494145959.9999999000' for column `test`.`t1_time_in_decimal`.`a` at row 20 540Warning 1292 Incorrect time value: '876494155959.9999990000' for column `test`.`t1_time_in_decimal`.`a` at row 21 541Warning 1292 Incorrect time value: '876494155959.9999990000' for column `test`.`t1_time_in_decimal`.`a` at row 21 542Warning 1292 Incorrect time value: '876494155959.9999999000' for column `test`.`t1_time_in_decimal`.`a` at row 22 543Warning 1292 Incorrect time value: '876494155959.9999999000' for column `test`.`t1_time_in_decimal`.`a` at row 22 544# 545# Functions with a single DATE input, conversion from DATETIME-in-VARCHAR 546# 547SELECT QUARTER(a), CAST(a AS DATE), a FROM t1_datetime_in_varchar ORDER BY id; 548QUARTER(a) CAST(a AS DATE) a 5494 2000-12-31 2000-12-31 23:59:59 5504 2000-12-31 2000-12-31 23:59:59.9 5514 2000-12-31 2000-12-31 23:59:59.99 5524 2000-12-31 2000-12-31 23:59:59.999 5534 2000-12-31 2000-12-31 23:59:59.9999 5544 2000-12-31 2000-12-31 23:59:59.99999 5554 2000-12-31 2000-12-31 23:59:59.999999 5561 2000-12-31 2000-12-31 23:59:59.9999999 557Warnings: 558Note 1292 Truncated incorrect datetime value: '2000-12-31 23:59:59.9999999' 559Note 1292 Truncated incorrect datetime value: '2000-12-31 23:59:59.9999999' 560SELECT DAY(a), CAST(a AS DATE), a FROM t1_datetime_in_varchar ORDER BY id; 561DAY(a) CAST(a AS DATE) a 56231 2000-12-31 2000-12-31 23:59:59 56331 2000-12-31 2000-12-31 23:59:59.9 56431 2000-12-31 2000-12-31 23:59:59.99 56531 2000-12-31 2000-12-31 23:59:59.999 56631 2000-12-31 2000-12-31 23:59:59.9999 56731 2000-12-31 2000-12-31 23:59:59.99999 56831 2000-12-31 2000-12-31 23:59:59.999999 5691 2000-12-31 2000-12-31 23:59:59.9999999 570Warnings: 571Note 1292 Truncated incorrect datetime value: '2000-12-31 23:59:59.9999999' 572Note 1292 Truncated incorrect datetime value: '2000-12-31 23:59:59.9999999' 573SELECT MONTH(a), CAST(a AS DATE), a FROM t1_datetime_in_varchar ORDER BY id; 574MONTH(a) CAST(a AS DATE) a 57512 2000-12-31 2000-12-31 23:59:59 57612 2000-12-31 2000-12-31 23:59:59.9 57712 2000-12-31 2000-12-31 23:59:59.99 57812 2000-12-31 2000-12-31 23:59:59.999 57912 2000-12-31 2000-12-31 23:59:59.9999 58012 2000-12-31 2000-12-31 23:59:59.99999 58112 2000-12-31 2000-12-31 23:59:59.999999 5821 2000-12-31 2000-12-31 23:59:59.9999999 583Warnings: 584Note 1292 Truncated incorrect datetime value: '2000-12-31 23:59:59.9999999' 585Note 1292 Truncated incorrect datetime value: '2000-12-31 23:59:59.9999999' 586SELECT YEAR(a), CAST(a AS DATE), a FROM t1_datetime_in_varchar ORDER BY id; 587YEAR(a) CAST(a AS DATE) a 5882000 2000-12-31 2000-12-31 23:59:59 5892000 2000-12-31 2000-12-31 23:59:59.9 5902000 2000-12-31 2000-12-31 23:59:59.99 5912000 2000-12-31 2000-12-31 23:59:59.999 5922000 2000-12-31 2000-12-31 23:59:59.9999 5932000 2000-12-31 2000-12-31 23:59:59.99999 5942000 2000-12-31 2000-12-31 23:59:59.999999 5952001 2000-12-31 2000-12-31 23:59:59.9999999 596Warnings: 597Note 1292 Truncated incorrect datetime value: '2000-12-31 23:59:59.9999999' 598Note 1292 Truncated incorrect datetime value: '2000-12-31 23:59:59.9999999' 599SELECT DAYNAME(a), CAST(a AS DATE), a FROM t1_datetime_in_varchar ORDER BY id; 600DAYNAME(a) CAST(a AS DATE) a 601Sunday 2000-12-31 2000-12-31 23:59:59 602Sunday 2000-12-31 2000-12-31 23:59:59.9 603Sunday 2000-12-31 2000-12-31 23:59:59.99 604Sunday 2000-12-31 2000-12-31 23:59:59.999 605Sunday 2000-12-31 2000-12-31 23:59:59.9999 606Sunday 2000-12-31 2000-12-31 23:59:59.99999 607Sunday 2000-12-31 2000-12-31 23:59:59.999999 608Monday 2000-12-31 2000-12-31 23:59:59.9999999 609Warnings: 610Note 1292 Truncated incorrect datetime value: '2000-12-31 23:59:59.9999999' 611Note 1292 Truncated incorrect datetime value: '2000-12-31 23:59:59.9999999' 612SELECT MONTHNAME(a), CAST(a AS DATE), a FROM t1_datetime_in_varchar ORDER BY id; 613MONTHNAME(a) CAST(a AS DATE) a 614December 2000-12-31 2000-12-31 23:59:59 615December 2000-12-31 2000-12-31 23:59:59.9 616December 2000-12-31 2000-12-31 23:59:59.99 617December 2000-12-31 2000-12-31 23:59:59.999 618December 2000-12-31 2000-12-31 23:59:59.9999 619December 2000-12-31 2000-12-31 23:59:59.99999 620December 2000-12-31 2000-12-31 23:59:59.999999 621January 2000-12-31 2000-12-31 23:59:59.9999999 622Warnings: 623Note 1292 Truncated incorrect datetime value: '2000-12-31 23:59:59.9999999' 624Note 1292 Truncated incorrect datetime value: '2000-12-31 23:59:59.9999999' 625SELECT LAST_DAY(a), CAST(a AS DATE), a FROM t1_datetime_in_varchar ORDER BY id; 626LAST_DAY(a) CAST(a AS DATE) a 6272000-12-31 2000-12-31 2000-12-31 23:59:59 6282000-12-31 2000-12-31 2000-12-31 23:59:59.9 6292000-12-31 2000-12-31 2000-12-31 23:59:59.99 6302000-12-31 2000-12-31 2000-12-31 23:59:59.999 6312000-12-31 2000-12-31 2000-12-31 23:59:59.9999 6322000-12-31 2000-12-31 2000-12-31 23:59:59.99999 6332000-12-31 2000-12-31 2000-12-31 23:59:59.999999 6342000-12-31 2000-12-31 2000-12-31 23:59:59.9999999 635Warnings: 636Note 1292 Truncated incorrect datetime value: '2000-12-31 23:59:59.9999999' 637Note 1292 Truncated incorrect datetime value: '2000-12-31 23:59:59.9999999' 638SELECT TO_DAYS(a), CAST(a AS DATE), a FROM t1_datetime_in_varchar ORDER BY id; 639TO_DAYS(a) CAST(a AS DATE) a 640730850 2000-12-31 2000-12-31 23:59:59 641730850 2000-12-31 2000-12-31 23:59:59.9 642730850 2000-12-31 2000-12-31 23:59:59.99 643730850 2000-12-31 2000-12-31 23:59:59.999 644730850 2000-12-31 2000-12-31 23:59:59.9999 645730850 2000-12-31 2000-12-31 23:59:59.99999 646730850 2000-12-31 2000-12-31 23:59:59.999999 647730851 2000-12-31 2000-12-31 23:59:59.9999999 648Warnings: 649Note 1292 Truncated incorrect datetime value: '2000-12-31 23:59:59.9999999' 650Note 1292 Truncated incorrect datetime value: '2000-12-31 23:59:59.9999999' 651SELECT DAYOFYEAR(a), CAST(a AS DATE), a FROM t1_datetime_in_varchar ORDER BY id; 652DAYOFYEAR(a) CAST(a AS DATE) a 653366 2000-12-31 2000-12-31 23:59:59 654366 2000-12-31 2000-12-31 23:59:59.9 655366 2000-12-31 2000-12-31 23:59:59.99 656366 2000-12-31 2000-12-31 23:59:59.999 657366 2000-12-31 2000-12-31 23:59:59.9999 658366 2000-12-31 2000-12-31 23:59:59.99999 659366 2000-12-31 2000-12-31 23:59:59.999999 6601 2000-12-31 2000-12-31 23:59:59.9999999 661Warnings: 662Note 1292 Truncated incorrect datetime value: '2000-12-31 23:59:59.9999999' 663Note 1292 Truncated incorrect datetime value: '2000-12-31 23:59:59.9999999' 664CREATE TABLE t1 (a VARCHAR(32)); 665INSERT INTO t1 VALUES 666('2002-01-05 23:59:59'), 667('2002-01-05 23:59:59.999999'), 668('2002-01-05 23:59:59.9999999'); 669SELECT YEARWEEK(a), a FROM t1; 670YEARWEEK(a) a 671200152 2002-01-05 23:59:59 672200152 2002-01-05 23:59:59.999999 673200201 2002-01-05 23:59:59.9999999 674Warnings: 675Note 1292 Truncated incorrect datetime value: '2002-01-05 23:59:59.9999999' 676SELECT WEEK(a), a FROM t1; 677WEEK(a) a 6780 2002-01-05 23:59:59 6790 2002-01-05 23:59:59.999999 6801 2002-01-05 23:59:59.9999999 681Warnings: 682Note 1292 Truncated incorrect datetime value: '2002-01-05 23:59:59.9999999' 683SELECT WEEKDAY(a), a FROM t1; 684WEEKDAY(a) a 6855 2002-01-05 23:59:59 6865 2002-01-05 23:59:59.999999 6876 2002-01-05 23:59:59.9999999 688Warnings: 689Note 1292 Truncated incorrect datetime value: '2002-01-05 23:59:59.9999999' 690DROP TABLE t1; 691CREATE TABLE t1 (a DECIMAL(32,9)); 692INSERT INTO t1 VALUES 693(20020105235959), 694(20020105235959.999999), 695(20020105235959.9999999); 696SELECT YEARWEEK(a), a FROM t1; 697YEARWEEK(a) a 698200152 20020105235959.000000000 699200152 20020105235959.999999000 700200201 20020105235959.999999900 701SELECT WEEK(a), a FROM t1; 702WEEK(a) a 7030 20020105235959.000000000 7040 20020105235959.999999000 7051 20020105235959.999999900 706SELECT WEEKDAY(a), a FROM t1; 707WEEKDAY(a) a 7085 20020105235959.000000000 7095 20020105235959.999999000 7106 20020105235959.999999900 711DROP TABLE t1; 712# 713# Functions with a single DATE input, conversion from DATETIME-in-DECIMAL 714# 715SELECT QUARTER(a), CAST(a AS DATE), a FROM t1_datetime_in_decimal ORDER BY id; 716QUARTER(a) CAST(a AS DATE) a 7174 2000-12-31 20001231235959.0000000000 7184 2000-12-31 20001231235959.9000000000 7194 2000-12-31 20001231235959.9900000000 7204 2000-12-31 20001231235959.9990000000 7214 2000-12-31 20001231235959.9999000000 7224 2000-12-31 20001231235959.9999900000 7234 2000-12-31 20001231235959.9999990000 7241 2000-12-31 20001231235959.9999999000 725SELECT DAY(a), CAST(a AS DATE), a FROM t1_datetime_in_decimal ORDER BY id; 726DAY(a) CAST(a AS DATE) a 72731 2000-12-31 20001231235959.0000000000 72831 2000-12-31 20001231235959.9000000000 72931 2000-12-31 20001231235959.9900000000 73031 2000-12-31 20001231235959.9990000000 73131 2000-12-31 20001231235959.9999000000 73231 2000-12-31 20001231235959.9999900000 73331 2000-12-31 20001231235959.9999990000 7341 2000-12-31 20001231235959.9999999000 735SELECT MONTH(a), CAST(a AS DATE), a FROM t1_datetime_in_decimal ORDER BY id; 736MONTH(a) CAST(a AS DATE) a 73712 2000-12-31 20001231235959.0000000000 73812 2000-12-31 20001231235959.9000000000 73912 2000-12-31 20001231235959.9900000000 74012 2000-12-31 20001231235959.9990000000 74112 2000-12-31 20001231235959.9999000000 74212 2000-12-31 20001231235959.9999900000 74312 2000-12-31 20001231235959.9999990000 7441 2000-12-31 20001231235959.9999999000 745SELECT YEAR(a), CAST(a AS DATE), a FROM t1_datetime_in_decimal ORDER BY id; 746YEAR(a) CAST(a AS DATE) a 7472000 2000-12-31 20001231235959.0000000000 7482000 2000-12-31 20001231235959.9000000000 7492000 2000-12-31 20001231235959.9900000000 7502000 2000-12-31 20001231235959.9990000000 7512000 2000-12-31 20001231235959.9999000000 7522000 2000-12-31 20001231235959.9999900000 7532000 2000-12-31 20001231235959.9999990000 7542001 2000-12-31 20001231235959.9999999000 755SELECT DAYNAME(a), CAST(a AS DATE), a FROM t1_datetime_in_decimal ORDER BY id; 756DAYNAME(a) CAST(a AS DATE) a 757Sunday 2000-12-31 20001231235959.0000000000 758Sunday 2000-12-31 20001231235959.9000000000 759Sunday 2000-12-31 20001231235959.9900000000 760Sunday 2000-12-31 20001231235959.9990000000 761Sunday 2000-12-31 20001231235959.9999000000 762Sunday 2000-12-31 20001231235959.9999900000 763Sunday 2000-12-31 20001231235959.9999990000 764Monday 2000-12-31 20001231235959.9999999000 765SELECT MONTHNAME(a), CAST(a AS DATE), a FROM t1_datetime_in_decimal ORDER BY id; 766MONTHNAME(a) CAST(a AS DATE) a 767December 2000-12-31 20001231235959.0000000000 768December 2000-12-31 20001231235959.9000000000 769December 2000-12-31 20001231235959.9900000000 770December 2000-12-31 20001231235959.9990000000 771December 2000-12-31 20001231235959.9999000000 772December 2000-12-31 20001231235959.9999900000 773December 2000-12-31 20001231235959.9999990000 774January 2000-12-31 20001231235959.9999999000 775SELECT YEARWEEK(a), CAST(a AS DATE), a FROM t1_datetime_in_decimal ORDER BY id; 776YEARWEEK(a) CAST(a AS DATE) a 777200053 2000-12-31 20001231235959.0000000000 778200053 2000-12-31 20001231235959.9000000000 779200053 2000-12-31 20001231235959.9900000000 780200053 2000-12-31 20001231235959.9990000000 781200053 2000-12-31 20001231235959.9999000000 782200053 2000-12-31 20001231235959.9999900000 783200053 2000-12-31 20001231235959.9999990000 784200053 2000-12-31 20001231235959.9999999000 785SELECT LAST_DAY(a), CAST(a AS DATE), a FROM t1_datetime_in_decimal ORDER BY id; 786LAST_DAY(a) CAST(a AS DATE) a 7872000-12-31 2000-12-31 20001231235959.0000000000 7882000-12-31 2000-12-31 20001231235959.9000000000 7892000-12-31 2000-12-31 20001231235959.9900000000 7902000-12-31 2000-12-31 20001231235959.9990000000 7912000-12-31 2000-12-31 20001231235959.9999000000 7922000-12-31 2000-12-31 20001231235959.9999900000 7932000-12-31 2000-12-31 20001231235959.9999990000 7942000-12-31 2000-12-31 20001231235959.9999999000 795SELECT TO_DAYS(a), CAST(a AS DATE), a FROM t1_datetime_in_decimal ORDER BY id; 796TO_DAYS(a) CAST(a AS DATE) a 797730850 2000-12-31 20001231235959.0000000000 798730850 2000-12-31 20001231235959.9000000000 799730850 2000-12-31 20001231235959.9900000000 800730850 2000-12-31 20001231235959.9990000000 801730850 2000-12-31 20001231235959.9999000000 802730850 2000-12-31 20001231235959.9999900000 803730850 2000-12-31 20001231235959.9999990000 804730851 2000-12-31 20001231235959.9999999000 805SELECT DAYOFYEAR(a), CAST(a AS DATE), a FROM t1_datetime_in_decimal ORDER BY id; 806DAYOFYEAR(a) CAST(a AS DATE) a 807366 2000-12-31 20001231235959.0000000000 808366 2000-12-31 20001231235959.9000000000 809366 2000-12-31 20001231235959.9900000000 810366 2000-12-31 20001231235959.9990000000 811366 2000-12-31 20001231235959.9999000000 812366 2000-12-31 20001231235959.9999900000 813366 2000-12-31 20001231235959.9999990000 8141 2000-12-31 20001231235959.9999999000 815SELECT DAYOFMONTH(a), CAST(a AS DATE), a FROM t1_datetime_in_decimal ORDER BY id; 816DAYOFMONTH(a) CAST(a AS DATE) a 81731 2000-12-31 20001231235959.0000000000 81831 2000-12-31 20001231235959.9000000000 81931 2000-12-31 20001231235959.9900000000 82031 2000-12-31 20001231235959.9990000000 82131 2000-12-31 20001231235959.9999000000 82231 2000-12-31 20001231235959.9999900000 82331 2000-12-31 20001231235959.9999990000 8241 2000-12-31 20001231235959.9999999000 825# 826# Functions with a single DATETIME input, conversion from DATETIME-in-VARCHAR 827# 828SELECT TO_SECONDS(a), CAST(a AS DATETIME(6)), a FROM t1_datetime_in_varchar ORDER BY id; 829TO_SECONDS(a) CAST(a AS DATETIME(6)) a 83063145526399 2000-12-31 23:59:59.000000 2000-12-31 23:59:59 83163145526399 2000-12-31 23:59:59.900000 2000-12-31 23:59:59.9 83263145526399 2000-12-31 23:59:59.990000 2000-12-31 23:59:59.99 83363145526399 2000-12-31 23:59:59.999000 2000-12-31 23:59:59.999 83463145526399 2000-12-31 23:59:59.999900 2000-12-31 23:59:59.9999 83563145526399 2000-12-31 23:59:59.999990 2000-12-31 23:59:59.99999 83663145526399 2000-12-31 23:59:59.999999 2000-12-31 23:59:59.999999 83763145526400 2001-01-01 00:00:00.000000 2000-12-31 23:59:59.9999999 838Warnings: 839Note 1292 Truncated incorrect datetime value: '2000-12-31 23:59:59.9999999' 840Note 1292 Truncated incorrect datetime value: '2000-12-31 23:59:59.9999999' 841SET time_zone='+00:00'; 842SELECT UNIX_TIMESTAMP(a), CAST(a AS DATETIME(6)), a FROM t1_datetime_in_varchar ORDER BY id; 843UNIX_TIMESTAMP(a) CAST(a AS DATETIME(6)) a 844978307199.000000 2000-12-31 23:59:59.000000 2000-12-31 23:59:59 845978307199.900000 2000-12-31 23:59:59.900000 2000-12-31 23:59:59.9 846978307199.990000 2000-12-31 23:59:59.990000 2000-12-31 23:59:59.99 847978307199.999000 2000-12-31 23:59:59.999000 2000-12-31 23:59:59.999 848978307199.999900 2000-12-31 23:59:59.999900 2000-12-31 23:59:59.9999 849978307199.999990 2000-12-31 23:59:59.999990 2000-12-31 23:59:59.99999 850978307199.999999 2000-12-31 23:59:59.999999 2000-12-31 23:59:59.999999 851978307200.000000 2001-01-01 00:00:00.000000 2000-12-31 23:59:59.9999999 852Warnings: 853Note 1292 Truncated incorrect datetime value: '2000-12-31 23:59:59.9999999' 854Note 1292 Truncated incorrect datetime value: '2000-12-31 23:59:59.9999999' 855SET time_zone=DEFAULT; 856SELECT CONVERT_TZ(a, '+00:00','+00:00'), CAST(a AS DATETIME(6)), a FROM t1_datetime_in_varchar ORDER BY id; 857CONVERT_TZ(a, '+00:00','+00:00') CAST(a AS DATETIME(6)) a 8582000-12-31 23:59:59.000000 2000-12-31 23:59:59.000000 2000-12-31 23:59:59 8592000-12-31 23:59:59.900000 2000-12-31 23:59:59.900000 2000-12-31 23:59:59.9 8602000-12-31 23:59:59.990000 2000-12-31 23:59:59.990000 2000-12-31 23:59:59.99 8612000-12-31 23:59:59.999000 2000-12-31 23:59:59.999000 2000-12-31 23:59:59.999 8622000-12-31 23:59:59.999900 2000-12-31 23:59:59.999900 2000-12-31 23:59:59.9999 8632000-12-31 23:59:59.999990 2000-12-31 23:59:59.999990 2000-12-31 23:59:59.99999 8642000-12-31 23:59:59.999999 2000-12-31 23:59:59.999999 2000-12-31 23:59:59.999999 8652001-01-01 00:00:00.000000 2001-01-01 00:00:00.000000 2000-12-31 23:59:59.9999999 866Warnings: 867Note 1292 Truncated incorrect datetime value: '2000-12-31 23:59:59.9999999' 868Note 1292 Truncated incorrect datetime value: '2000-12-31 23:59:59.9999999' 869# 870# Functions with a single DATETIME input, conversion from DATETIME-in-DECIMAL 871# 872SELECT TO_SECONDS(a), CAST(a AS DATETIME(6)), a FROM t1_datetime_in_decimal ORDER BY id; 873TO_SECONDS(a) CAST(a AS DATETIME(6)) a 87463145526399 2000-12-31 23:59:59.000000 20001231235959.0000000000 87563145526399 2000-12-31 23:59:59.900000 20001231235959.9000000000 87663145526399 2000-12-31 23:59:59.990000 20001231235959.9900000000 87763145526399 2000-12-31 23:59:59.999000 20001231235959.9990000000 87863145526399 2000-12-31 23:59:59.999900 20001231235959.9999000000 87963145526399 2000-12-31 23:59:59.999990 20001231235959.9999900000 88063145526399 2000-12-31 23:59:59.999999 20001231235959.9999990000 88163145526400 2001-01-01 00:00:00.000000 20001231235959.9999999000 882SET time_zone='+00:00'; 883SELECT UNIX_TIMESTAMP(a), CAST(a AS DATETIME(6)), a FROM t1_datetime_in_decimal ORDER BY id; 884UNIX_TIMESTAMP(a) CAST(a AS DATETIME(6)) a 885978307199.000000 2000-12-31 23:59:59.000000 20001231235959.0000000000 886978307199.900000 2000-12-31 23:59:59.900000 20001231235959.9000000000 887978307199.990000 2000-12-31 23:59:59.990000 20001231235959.9900000000 888978307199.999000 2000-12-31 23:59:59.999000 20001231235959.9990000000 889978307199.999900 2000-12-31 23:59:59.999900 20001231235959.9999000000 890978307199.999990 2000-12-31 23:59:59.999990 20001231235959.9999900000 891978307199.999999 2000-12-31 23:59:59.999999 20001231235959.9999990000 892978307200.000000 2001-01-01 00:00:00.000000 20001231235959.9999999000 893SET time_zone=DEFAULT; 894SELECT CONVERT_TZ(a, '+00:00','+00:00'), CAST(a AS DATETIME(6)), a FROM t1_datetime_in_decimal ORDER BY id; 895CONVERT_TZ(a, '+00:00','+00:00') CAST(a AS DATETIME(6)) a 8962000-12-31 23:59:59.000000 2000-12-31 23:59:59.000000 20001231235959.0000000000 8972000-12-31 23:59:59.900000 2000-12-31 23:59:59.900000 20001231235959.9000000000 8982000-12-31 23:59:59.990000 2000-12-31 23:59:59.990000 20001231235959.9900000000 8992000-12-31 23:59:59.999000 2000-12-31 23:59:59.999000 20001231235959.9990000000 9002000-12-31 23:59:59.999900 2000-12-31 23:59:59.999900 20001231235959.9999000000 9012000-12-31 23:59:59.999990 2000-12-31 23:59:59.999990 20001231235959.9999900000 9022000-12-31 23:59:59.999999 2000-12-31 23:59:59.999999 20001231235959.9999990000 9032001-01-01 00:00:00.000000 2001-01-01 00:00:00.000000 20001231235959.9999999000 904DROP TABLE t1_datetime_in_varchar; 905DROP TABLE t1_datetime_in_decimal; 906DROP TABLE t1_time_in_varchar; 907DROP TABLE t1_time_in_decimal; 908# 909# Functions that construct DATETIME 910# 911SET time_zone='+00:00'; 912CREATE TABLE t1_unix_timestamp (id SERIAL, a DECIMAL(30,10)); 913INSERT INTO t1_unix_timestamp (a) VALUES 914(980639999), 915(980639999.9), 916(980639999.999999), 917(980639999.9999999), 918(2147483647), 919(2147483647.9), 920(2147483647.999999), 921(2147483647.9999999); 922SELECT a, FROM_UNIXTIME(a) FROM t1_unix_timestamp ORDER BY id; 923a FROM_UNIXTIME(a) 924980639999.0000000000 2001-01-27 23:59:59.000000 925980639999.9000000000 2001-01-27 23:59:59.900000 926980639999.9999990000 2001-01-27 23:59:59.999999 927980639999.9999999000 2001-01-28 00:00:00.000000 9282147483647.0000000000 2038-01-19 03:14:07.000000 9292147483647.9000000000 2038-01-19 03:14:07.900000 9302147483647.9999990000 2038-01-19 03:14:07.999999 9312147483647.9999999000 NULL 932DROP TABLE t1_unix_timestamp; 933SET time_zone=DEFAULT; 934# 935# Functions that construct TIME 936# 937CREATE TABLE t1_sec (id SERIAL, a DECIMAL(38,10)); 938INSERT INTO t1_sec (a) VALUES 939(59), 940(59.9), 941(59.999999), 942(59.9999999), 943(3020398), 944(3020398.999999), 945(3020398.9999999), 946(3020399), 947(3020399.999999), 948(3020399.9999999), 949(9223372036854775807), 950(9223372036854775807.9), 951(9223372036854775807.999999), 952(9223372036854775807.9999999), 953(18446744073709551615), 954(18446744073709551615.9), 955(18446744073709551615.999999), 956(18446744073709551615.9999999); 957SELECT a, SEC_TO_TIME(a) FROM t1_sec ORDER BY id; 958a SEC_TO_TIME(a) 95959.0000000000 00:00:59.000000 96059.9000000000 00:00:59.900000 96159.9999990000 00:00:59.999999 96259.9999999000 00:01:00.000000 9633020398.0000000000 838:59:58.000000 9643020398.9999990000 838:59:58.999999 9653020398.9999999000 838:59:59.000000 9663020399.0000000000 838:59:59.000000 9673020399.9999990000 838:59:59.999999 9683020399.9999999000 838:59:59.999999 9699223372036854775807.0000000000 838:59:59.999999 9709223372036854775807.9000000000 838:59:59.999999 9719223372036854775807.9999990000 838:59:59.999999 9729223372036854775807.9999999000 838:59:59.999999 97318446744073709551615.0000000000 838:59:59.999999 97418446744073709551615.9000000000 838:59:59.999999 97518446744073709551615.9999990000 838:59:59.999999 97618446744073709551615.9999999000 838:59:59.999999 977Warnings: 978Warning 1292 Truncated incorrect seconds value: '3020400' 979Warning 1292 Truncated incorrect seconds value: '9223372036854775807.0000000000' 980Warning 1292 Truncated incorrect seconds value: '9223372036854775807.9000000000' 981Warning 1292 Truncated incorrect seconds value: '9223372036854775807.9999990000' 982Warning 1292 Truncated incorrect seconds value: '9223372036854775807.9999999000' 983Warning 1292 Truncated incorrect seconds value: '18446744073709551615.0000000000' 984Warning 1292 Truncated incorrect seconds value: '18446744073709551615.9000000000' 985Warning 1292 Truncated incorrect seconds value: '18446744073709551615.9999990000' 986Warning 1292 Truncated incorrect seconds value: '18446744073709551615.9999999000' 987DROP TABLE t1_sec; 988CREATE TABLE t1_sec (id SERIAL, a DECIMAL(38,10)); 989INSERT INTO t1_sec (a) VALUES 990(0), 991(0.9), 992(0.999999), 993(0.9999999); 994SELECT a, MAKETIME(0, 0, a) FROM t1_sec ORDER BY id; 995a MAKETIME(0, 0, a) 9960.0000000000 00:00:00.000000 9970.9000000000 00:00:00.900000 9980.9999990000 00:00:00.999999 9990.9999999000 00:00:01.000000 1000DROP TABLE t1_sec; 1001CREATE TABLE t1 (a VARCHAR(64)); 1002INSERT INTO t1 VALUES 1003('2017-12-31 23:59:59'), 1004('2017-12-31 23:59:59.9'), 1005('2017-12-31 23:59:59.999999'), 1006('2017-12-31 23:59:59.9999999'); 1007SELECT 1008'----', 1009a, 1010DATE_FORMAT(a, '%Y') AS yyyy, 1011DATE_FORMAT(a, '%Y-%m-%d') AS d, 1012DATE_FORMAT(a, '%H:%i:%s') AS t0, 1013DATE_FORMAT(a, '%H:%i:%s.%f') AS t6, 1014DATE_FORMAT(a, '%Y-%m-%d %H:%i:%s') AS dt0, 1015DATE_FORMAT(a, '%Y-%m-%d %H:%i:%s.%f') AS dt6 1016FROM t1; 1017---- ---- 1018a 2017-12-31 23:59:59 1019yyyy 2017 1020d 2017-12-31 1021t0 23:59:59 1022t6 23:59:59.000000 1023dt0 2017-12-31 23:59:59 1024dt6 2017-12-31 23:59:59.000000 1025---- ---- 1026a 2017-12-31 23:59:59.9 1027yyyy 2017 1028d 2017-12-31 1029t0 23:59:59 1030t6 23:59:59.900000 1031dt0 2017-12-31 23:59:59 1032dt6 2017-12-31 23:59:59.900000 1033---- ---- 1034a 2017-12-31 23:59:59.999999 1035yyyy 2017 1036d 2017-12-31 1037t0 23:59:59 1038t6 23:59:59.999999 1039dt0 2017-12-31 23:59:59 1040dt6 2017-12-31 23:59:59.999999 1041---- ---- 1042a 2017-12-31 23:59:59.9999999 1043yyyy 2018 1044d 2018-01-01 1045t0 00:00:00 1046t6 00:00:00.000000 1047dt0 2018-01-01 00:00:00 1048dt6 2018-01-01 00:00:00.000000 1049Warnings: 1050Level Note 1051Code 1292 1052Message Truncated incorrect datetime value: '2017-12-31 23:59:59.9999999' 1053Level Note 1054Code 1292 1055Message Truncated incorrect datetime value: '2017-12-31 23:59:59.9999999' 1056Level Note 1057Code 1292 1058Message Truncated incorrect datetime value: '2017-12-31 23:59:59.9999999' 1059Level Note 1060Code 1292 1061Message Truncated incorrect datetime value: '2017-12-31 23:59:59.9999999' 1062Level Note 1063Code 1292 1064Message Truncated incorrect datetime value: '2017-12-31 23:59:59.9999999' 1065Level Note 1066Code 1292 1067Message Truncated incorrect datetime value: '2017-12-31 23:59:59.9999999' 1068DROP TABLE t1; 1069CREATE TABLE t1 (a DECIMAL(32,9)); 1070INSERT INTO t1 VALUES 1071(20171231235959), 1072(20171231235959.9), 1073(20171231235959.999999), 1074(20171231235959.9999999); 1075SELECT 1076'----', 1077a, 1078DATE_FORMAT(a, '%Y') AS yyyy, 1079DATE_FORMAT(a, '%Y-%m-%d') AS d, 1080DATE_FORMAT(a, '%H:%i:%s') AS t0, 1081DATE_FORMAT(a, '%H:%i:%s.%f') AS t6, 1082DATE_FORMAT(a, '%Y-%m-%d %H:%i:%s') AS dt0, 1083DATE_FORMAT(a, '%Y-%m-%d %H:%i:%s.%f') AS dt6 1084FROM t1; 1085---- ---- 1086a 20171231235959.000000000 1087yyyy 2017 1088d 2017-12-31 1089t0 23:59:59 1090t6 23:59:59.000000 1091dt0 2017-12-31 23:59:59 1092dt6 2017-12-31 23:59:59.000000 1093---- ---- 1094a 20171231235959.900000000 1095yyyy 2017 1096d 2017-12-31 1097t0 23:59:59 1098t6 23:59:59.900000 1099dt0 2017-12-31 23:59:59 1100dt6 2017-12-31 23:59:59.900000 1101---- ---- 1102a 20171231235959.999999000 1103yyyy 2017 1104d 2017-12-31 1105t0 23:59:59 1106t6 23:59:59.999999 1107dt0 2017-12-31 23:59:59 1108dt6 2017-12-31 23:59:59.999999 1109---- ---- 1110a 20171231235959.999999900 1111yyyy 2018 1112d 2018-01-01 1113t0 00:00:00 1114t6 00:00:00.000000 1115dt0 2018-01-01 00:00:00 1116dt6 2018-01-01 00:00:00.000000 1117DROP TABLE t1; 1118# 1119# Functions with two temporal parameters that round nanoseconds in both parameters in MySQL 1120# 1121CREATE TABLE t1 (a VARCHAR(64)); 1122CREATE TABLE t2 (a VARCHAR(64)); 1123INSERT INTO t1 VALUES 1124('2017-12-31 23:59:59'), 1125('2017-12-31 23:59:59.9'), 1126('2017-12-31 23:59:59.999999'), 1127('2017-12-31 23:59:59.9999999'); 1128INSERT INTO t2 VALUES 1129('00:00:00'), 1130('00:00:00.9'), 1131('00:00:00.999999'), 1132('00:00:00.9999999'); 1133SELECT TIMESTAMP(t1.a, t2.a), t1.a, t2.a FROM t1,t2 ORDER BY t1.a, t2.a; 1134TIMESTAMP(t1.a, t2.a) a a 11352017-12-31 23:59:59.000000 2017-12-31 23:59:59 00:00:00 11362017-12-31 23:59:59.900000 2017-12-31 23:59:59 00:00:00.9 11372017-12-31 23:59:59.999999 2017-12-31 23:59:59 00:00:00.999999 11382018-01-01 00:00:00.000000 2017-12-31 23:59:59 00:00:00.9999999 11392017-12-31 23:59:59.900000 2017-12-31 23:59:59.9 00:00:00 11402018-01-01 00:00:00.800000 2017-12-31 23:59:59.9 00:00:00.9 11412018-01-01 00:00:00.899999 2017-12-31 23:59:59.9 00:00:00.999999 11422018-01-01 00:00:00.900000 2017-12-31 23:59:59.9 00:00:00.9999999 11432017-12-31 23:59:59.999999 2017-12-31 23:59:59.999999 00:00:00 11442018-01-01 00:00:00.899999 2017-12-31 23:59:59.999999 00:00:00.9 11452018-01-01 00:00:00.999998 2017-12-31 23:59:59.999999 00:00:00.999999 11462018-01-01 00:00:00.999999 2017-12-31 23:59:59.999999 00:00:00.9999999 11472018-01-01 00:00:00.000000 2017-12-31 23:59:59.9999999 00:00:00 11482018-01-01 00:00:00.900000 2017-12-31 23:59:59.9999999 00:00:00.9 11492018-01-01 00:00:00.999999 2017-12-31 23:59:59.9999999 00:00:00.999999 11502018-01-01 00:00:01.000000 2017-12-31 23:59:59.9999999 00:00:00.9999999 1151Warnings: 1152Note 1292 Truncated incorrect datetime value: '2017-12-31 23:59:59.9999999' 1153Note 1292 Truncated incorrect datetime value: '2017-12-31 23:59:59.9999999' 1154Note 1292 Truncated incorrect datetime value: '2017-12-31 23:59:59.9999999' 1155Note 1292 Truncated incorrect INTERVAL DAY TO SECOND value: '00:00:00.9999999' 1156Note 1292 Truncated incorrect INTERVAL DAY TO SECOND value: '00:00:00.9999999' 1157Note 1292 Truncated incorrect INTERVAL DAY TO SECOND value: '00:00:00.9999999' 1158Note 1292 Truncated incorrect datetime value: '2017-12-31 23:59:59.9999999' 1159Note 1292 Truncated incorrect INTERVAL DAY TO SECOND value: '00:00:00.9999999' 1160SELECT ADDTIME(t1.a, t2.a), t1.a, t2.a FROM t1,t2 ORDER BY t1.a, t2.a; 1161ADDTIME(t1.a, t2.a) a a 11622017-12-31 23:59:59 2017-12-31 23:59:59 00:00:00 11632017-12-31 23:59:59.900000 2017-12-31 23:59:59 00:00:00.9 11642017-12-31 23:59:59.999999 2017-12-31 23:59:59 00:00:00.999999 11652018-01-01 00:00:00 2017-12-31 23:59:59 00:00:00.9999999 11662017-12-31 23:59:59.900000 2017-12-31 23:59:59.9 00:00:00 11672018-01-01 00:00:00.800000 2017-12-31 23:59:59.9 00:00:00.9 11682018-01-01 00:00:00.899999 2017-12-31 23:59:59.9 00:00:00.999999 11692018-01-01 00:00:00.900000 2017-12-31 23:59:59.9 00:00:00.9999999 11702017-12-31 23:59:59.999999 2017-12-31 23:59:59.999999 00:00:00 11712018-01-01 00:00:00.899999 2017-12-31 23:59:59.999999 00:00:00.9 11722018-01-01 00:00:00.999998 2017-12-31 23:59:59.999999 00:00:00.999999 11732018-01-01 00:00:00.999999 2017-12-31 23:59:59.999999 00:00:00.9999999 11742018-01-01 00:00:00 2017-12-31 23:59:59.9999999 00:00:00 11752018-01-01 00:00:00.900000 2017-12-31 23:59:59.9999999 00:00:00.9 11762018-01-01 00:00:00.999999 2017-12-31 23:59:59.9999999 00:00:00.999999 11772018-01-01 00:00:01 2017-12-31 23:59:59.9999999 00:00:00.9999999 1178Warnings: 1179Note 1292 Truncated incorrect datetime value: '2017-12-31 23:59:59.9999999' 1180Note 1292 Truncated incorrect datetime value: '2017-12-31 23:59:59.9999999' 1181Note 1292 Truncated incorrect datetime value: '2017-12-31 23:59:59.9999999' 1182Note 1292 Truncated incorrect INTERVAL DAY TO SECOND value: '00:00:00.9999999' 1183Note 1292 Truncated incorrect INTERVAL DAY TO SECOND value: '00:00:00.9999999' 1184Note 1292 Truncated incorrect INTERVAL DAY TO SECOND value: '00:00:00.9999999' 1185Note 1292 Truncated incorrect datetime value: '2017-12-31 23:59:59.9999999' 1186Note 1292 Truncated incorrect INTERVAL DAY TO SECOND value: '00:00:00.9999999' 1187DROP TABLE t1, t2; 1188CREATE TABLE t1 (a VARCHAR(64)); 1189CREATE TABLE t2 (a VARCHAR(64)); 1190INSERT INTO t1 VALUES 1191('23:59:59'), 1192('23:59:59.9'), 1193('23:59:59.999999'), 1194('23:59:59.9999999'); 1195INSERT INTO t2 VALUES 1196('00:00:00'), 1197('00:00:00.9'), 1198('00:00:00.999999'), 1199('00:00:00.9999999'); 1200SELECT TIMEDIFF(t1.a, t2.a), t1.a, t2.a FROM t1,t2 ORDER BY t1.a, t2.a; 1201TIMEDIFF(t1.a, t2.a) a a 120223:59:59.000000 23:59:59 00:00:00 120323:59:58.100000 23:59:59 00:00:00.9 120423:59:58.000001 23:59:59 00:00:00.999999 120523:59:58.000000 23:59:59 00:00:00.9999999 120623:59:59.900000 23:59:59.9 00:00:00 120723:59:59.000000 23:59:59.9 00:00:00.9 120823:59:58.900001 23:59:59.9 00:00:00.999999 120923:59:58.900000 23:59:59.9 00:00:00.9999999 121023:59:59.999999 23:59:59.999999 00:00:00 121123:59:59.099999 23:59:59.999999 00:00:00.9 121223:59:59.000000 23:59:59.999999 00:00:00.999999 121323:59:58.999999 23:59:59.999999 00:00:00.9999999 121424:00:00.000000 23:59:59.9999999 00:00:00 121523:59:59.100000 23:59:59.9999999 00:00:00.9 121623:59:59.000001 23:59:59.9999999 00:00:00.999999 121723:59:59.000000 23:59:59.9999999 00:00:00.9999999 1218Warnings: 1219Note 1292 Truncated incorrect time value: '23:59:59.9999999' 1220Note 1292 Truncated incorrect time value: '23:59:59.9999999' 1221Note 1292 Truncated incorrect time value: '23:59:59.9999999' 1222Note 1292 Truncated incorrect time value: '00:00:00.9999999' 1223Note 1292 Truncated incorrect time value: '00:00:00.9999999' 1224Note 1292 Truncated incorrect time value: '00:00:00.9999999' 1225Note 1292 Truncated incorrect time value: '23:59:59.9999999' 1226Note 1292 Truncated incorrect time value: '00:00:00.9999999' 1227DROP TABLE t1, t2; 1228CREATE TABLE t1 (a VARCHAR(64)); 1229CREATE TABLE t2 (a VARCHAR(64)); 1230INSERT INTO t1 VALUES 1231('2001-12-31 23:59:59'), 1232('2001-12-31 23:59:59.9'), 1233('2001-12-31 23:59:59.999999'), 1234('2001-12-31 23:59:59.9999999'); 1235INSERT INTO t2 VALUES 1236('2001-12-31 23:59:59'), 1237('2001-12-31 23:59:59.9'), 1238('2001-12-31 23:59:59.999999'), 1239('2001-12-31 23:59:59.9999999'); 1240SELECT TIMESTAMPDIFF(MICROSECOND,t1.a, t2.a), t1.a, t2.a FROM t1,t2 ORDER BY t1.a, t2.a; 1241TIMESTAMPDIFF(MICROSECOND,t1.a, t2.a) a a 12420 2001-12-31 23:59:59 2001-12-31 23:59:59 1243900000 2001-12-31 23:59:59 2001-12-31 23:59:59.9 1244999999 2001-12-31 23:59:59 2001-12-31 23:59:59.999999 12451000000 2001-12-31 23:59:59 2001-12-31 23:59:59.9999999 1246-900000 2001-12-31 23:59:59.9 2001-12-31 23:59:59 12470 2001-12-31 23:59:59.9 2001-12-31 23:59:59.9 124899999 2001-12-31 23:59:59.9 2001-12-31 23:59:59.999999 1249100000 2001-12-31 23:59:59.9 2001-12-31 23:59:59.9999999 1250-999999 2001-12-31 23:59:59.999999 2001-12-31 23:59:59 1251-99999 2001-12-31 23:59:59.999999 2001-12-31 23:59:59.9 12520 2001-12-31 23:59:59.999999 2001-12-31 23:59:59.999999 12531 2001-12-31 23:59:59.999999 2001-12-31 23:59:59.9999999 1254-1000000 2001-12-31 23:59:59.9999999 2001-12-31 23:59:59 1255-100000 2001-12-31 23:59:59.9999999 2001-12-31 23:59:59.9 1256-1 2001-12-31 23:59:59.9999999 2001-12-31 23:59:59.999999 12570 2001-12-31 23:59:59.9999999 2001-12-31 23:59:59.9999999 1258Warnings: 1259Note 1292 Truncated incorrect datetime value: '2001-12-31 23:59:59.9999999' 1260Note 1292 Truncated incorrect datetime value: '2001-12-31 23:59:59.9999999' 1261Note 1292 Truncated incorrect datetime value: '2001-12-31 23:59:59.9999999' 1262Note 1292 Truncated incorrect datetime value: '2001-12-31 23:59:59.9999999' 1263Note 1292 Truncated incorrect datetime value: '2001-12-31 23:59:59.9999999' 1264Note 1292 Truncated incorrect datetime value: '2001-12-31 23:59:59.9999999' 1265Note 1292 Truncated incorrect datetime value: '2001-12-31 23:59:59.9999999' 1266Note 1292 Truncated incorrect datetime value: '2001-12-31 23:59:59.9999999' 1267DROP TABLE t1, t2; 1268CREATE TABLE t1 (a VARCHAR(64)); 1269CREATE TABLE t2 (a VARCHAR(64)); 1270INSERT INTO t1 VALUES 1271('23:59:59'), 1272('23:59:59.9'), 1273('23:59:59.999999'), 1274('23:59:59.9999999'); 1275INSERT INTO t2 VALUES 1276('00:00:00'), 1277('00:00:00.9'), 1278('00:00:00.999999'), 1279('00:00:00.9999999'); 1280SELECT TIMEDIFF(t1.a, t2.a), t1.a, t2.a FROM t1,t2 ORDER BY t1.a, t2.a; 1281TIMEDIFF(t1.a, t2.a) a a 128223:59:59.000000 23:59:59 00:00:00 128323:59:58.100000 23:59:59 00:00:00.9 128423:59:58.000001 23:59:59 00:00:00.999999 128523:59:58.000000 23:59:59 00:00:00.9999999 128623:59:59.900000 23:59:59.9 00:00:00 128723:59:59.000000 23:59:59.9 00:00:00.9 128823:59:58.900001 23:59:59.9 00:00:00.999999 128923:59:58.900000 23:59:59.9 00:00:00.9999999 129023:59:59.999999 23:59:59.999999 00:00:00 129123:59:59.099999 23:59:59.999999 00:00:00.9 129223:59:59.000000 23:59:59.999999 00:00:00.999999 129323:59:58.999999 23:59:59.999999 00:00:00.9999999 129424:00:00.000000 23:59:59.9999999 00:00:00 129523:59:59.100000 23:59:59.9999999 00:00:00.9 129623:59:59.000001 23:59:59.9999999 00:00:00.999999 129723:59:59.000000 23:59:59.9999999 00:00:00.9999999 1298Warnings: 1299Note 1292 Truncated incorrect time value: '23:59:59.9999999' 1300Note 1292 Truncated incorrect time value: '23:59:59.9999999' 1301Note 1292 Truncated incorrect time value: '23:59:59.9999999' 1302Note 1292 Truncated incorrect time value: '00:00:00.9999999' 1303Note 1292 Truncated incorrect time value: '00:00:00.9999999' 1304Note 1292 Truncated incorrect time value: '00:00:00.9999999' 1305Note 1292 Truncated incorrect time value: '23:59:59.9999999' 1306Note 1292 Truncated incorrect time value: '00:00:00.9999999' 1307DROP TABLE t1, t2; 1308# 1309# STR_TO_DATE behaviour is questionable in MySQL 5.6 (MySQL Bug #92474) 1310# 1311# It truncates nanoseconds, but this may change in the future. 1312CREATE TABLE t1 (a VARCHAR(64)); 1313INSERT INTO t1 VALUES 1314('2017-12-31 23:59:59'), 1315('2017-12-31 23:59:59.9'), 1316('2017-12-31 23:59:59.999999'), 1317('2017-12-31 23:59:59.9999999'); 1318SELECT 1319a, 1320STR_TO_DATE(a, '%Y-%m-%d %H:%i:%s') AS c0, 1321STR_TO_DATE(a, '%Y-%m-%d %H:%i:%s.%f') AS c6 1322FROM t1; 1323a c0 c6 13242017-12-31 23:59:59 2017-12-31 23:59:59 2017-12-31 23:59:59.000000 13252017-12-31 23:59:59.9 2017-12-31 23:59:59 2017-12-31 23:59:59.900000 13262017-12-31 23:59:59.999999 2017-12-31 23:59:59 2017-12-31 23:59:59.999999 13272017-12-31 23:59:59.9999999 2017-12-31 23:59:59 2017-12-31 23:59:59.999999 1328Warnings: 1329Warning 1292 Truncated incorrect datetime value: '2017-12-31 23:59:59.9' 1330Warning 1292 Truncated incorrect datetime value: '2017-12-31 23:59:59.999999' 1331Warning 1292 Truncated incorrect datetime value: '2017-12-31 23:59:59.9999999' 1332Warning 1292 Truncated incorrect datetime value: '2017-12-31 23:59:59.9999999' 1333DROP TABLE t1; 1334# 1335# DATE_ADD behaviour is questionable in MySQL 5.6 (MySQL Bug#92473) 1336# It rounds nanoseconds in the first argument, but truncates nanoseconds in the second argument. 1337# This may change in the future, to round both arguments. 1338# 1339CREATE TABLE t1 (a VARCHAR(64)); 1340INSERT INTO t1 VALUES 1341('2017-12-31 23:59:59'), 1342('2017-12-31 23:59:59.9'), 1343('2017-12-31 23:59:59.999999'), 1344('2017-12-31 23:59:59.9999999'); 1345CREATE TABLE t2 (b DECIMAL(32,9)); 1346INSERT INTO t2 VALUES 1347(0), 1348(0.9), 1349(0.999999), 1350(0.9999999); 1351SELECT a, b, DATE_ADD(a, INTERVAL b SECOND) FROM t1,t2 ORDER BY a,b; 1352a b DATE_ADD(a, INTERVAL b SECOND) 13532017-12-31 23:59:59 0.000000000 2017-12-31 23:59:59.000000 13542017-12-31 23:59:59 0.900000000 2017-12-31 23:59:59.900000 13552017-12-31 23:59:59 0.999999000 2017-12-31 23:59:59.999999 13562017-12-31 23:59:59 0.999999900 2017-12-31 23:59:59.999999 13572017-12-31 23:59:59.9 0.000000000 2017-12-31 23:59:59.900000 13582017-12-31 23:59:59.9 0.900000000 2018-01-01 00:00:00.800000 13592017-12-31 23:59:59.9 0.999999000 2018-01-01 00:00:00.899999 13602017-12-31 23:59:59.9 0.999999900 2018-01-01 00:00:00.899999 13612017-12-31 23:59:59.999999 0.000000000 2017-12-31 23:59:59.999999 13622017-12-31 23:59:59.999999 0.900000000 2018-01-01 00:00:00.899999 13632017-12-31 23:59:59.999999 0.999999000 2018-01-01 00:00:00.999998 13642017-12-31 23:59:59.999999 0.999999900 2018-01-01 00:00:00.999998 13652017-12-31 23:59:59.9999999 0.000000000 2018-01-01 00:00:00.000000 13662017-12-31 23:59:59.9999999 0.900000000 2018-01-01 00:00:00.900000 13672017-12-31 23:59:59.9999999 0.999999000 2018-01-01 00:00:00.999999 13682017-12-31 23:59:59.9999999 0.999999900 2018-01-01 00:00:00.999999 1369Warnings: 1370Note 1292 Truncated incorrect datetime value: '2017-12-31 23:59:59.9999999' 1371Note 1292 Truncated incorrect datetime value: '2017-12-31 23:59:59.9999999' 1372Note 1292 Truncated incorrect datetime value: '2017-12-31 23:59:59.9999999' 1373Note 1292 Truncated incorrect datetime value: '2017-12-31 23:59:59.9999999' 1374DROP TABLE t1, t2; 1375