1SET timestamp=UNIX_TIMESTAMP('2001-02-03 10:20:30'); 2drop table if exists t1, t2, t3; 3create table t1 (a time(7)); 4ERROR 42000: Too big precision 7 specified for 'a'. Maximum is 6 5create table t1 (a time(3), key(a)); 6insert t1 values ('2010-12-11 00:20:03.1234'); 7Warnings: 8Note 1265 Data truncated for column 'a' at row 1 9insert t1 values ('2010-12-11 15:47:11.1234'); 10Warnings: 11Note 1265 Data truncated for column 'a' at row 1 12insert t1 values (20101211010203.45678); 13Warnings: 14Note 1265 Data truncated for column 'a' at row 1 15insert t1 values (20101211030405.789e0); 16Warnings: 17Note 1265 Data truncated for column 'a' at row 1 18insert ignore t1 values (99991231235959e1); 19Warnings: 20Warning 1264 Out of range value for column 'a' at row 1 21select * from t1; 22a 2300:20:03.123 2401:02:03.456 2503:04:05.789 2615:47:11.123 27838:59:59.999 28select cast(a AS double(30,6)) from t1; 29cast(a AS double(30,6)) 302003.123000 3110203.456000 3230405.789062 33154711.123000 348385959.999000 35select a DIV 1 from t1; 36a DIV 1 372003 3810203 3930405 40154711 418385959 42select group_concat(distinct a) from t1; 43group_concat(distinct a) 4400:20:03.123,01:02:03.456,03:04:05.789,15:47:11.123,838:59:59.999 45alter table t1 engine=innodb; 46select * from t1 order by a; 47a 4800:20:03.123 4901:02:03.456 5003:04:05.789 5115:47:11.123 52838:59:59.999 53select * from t1 order by a+0; 54a 5500:20:03.123 5601:02:03.456 5703:04:05.789 5815:47:11.123 59838:59:59.999 60drop table t1; 61create table t1 (a time(4)) engine=innodb; 62insert t1 values ('2010-12-11 01:02:03.456789'); 63Warnings: 64Note 1265 Data truncated for column 'a' at row 1 65select * from t1; 66a 6701:02:03.4567 68select extract(microsecond from a + interval 100 microsecond) from t1 where a>'2010-11-12 01:02:03.456'; 69extract(microsecond from a + interval 100 microsecond) 70456800 71select a from t1 where a>'2010-11-12 01:02:03.456' group by a; 72a 7301:02:03.4567 74show create table t1; 75Table Create Table 76t1 CREATE TABLE `t1` ( 77 `a` time(4) DEFAULT NULL 78) ENGINE=InnoDB DEFAULT CHARSET=latin1 79show columns from t1; 80Field Type Null Key Default Extra 81a time(4) YES NULL 82select table_name, column_name, column_default, is_nullable, data_type, character_maximum_length, character_octet_length, numeric_precision, numeric_scale, datetime_precision, character_set_name, collation_name, column_type, column_key, extra from information_schema.columns where table_name='t1'; 83table_name t1 84column_name a 85column_default NULL 86is_nullable YES 87data_type time 88character_maximum_length NULL 89character_octet_length NULL 90numeric_precision NULL 91numeric_scale NULL 92datetime_precision 4 93character_set_name NULL 94collation_name NULL 95column_type time(4) 96column_key 97extra 98select a, a+interval 9876543 microsecond from t1; 99a a+interval 9876543 microsecond 10001:02:03.4567 01:02:13.333243 101update t1 set a=a+interval 9876543 microsecond; 102select * from t1; 103a 10401:02:13.3332 105select a, a + interval 2 year from t1; 106a a + interval 2 year 10701:02:13.3332 NULL 108Warnings: 109Warning 1441 Datetime function: time field overflow 110insert ignore t1 select a + interval 2 year from t1; 111Warnings: 112Warning 1441 Datetime function: time field overflow 113select * from t1; 114a 11501:02:13.3332 116NULL 117delete from t1 where a < 20110101; 118select * from t1; 119a 12001:02:13.3332 121NULL 122delete from t1 where a is not null; 123select * from t1; 124a 125NULL 126create table t2 select * from t1; 127create table t3 like t1; 128show create table t2; 129Table Create Table 130t2 CREATE TABLE `t2` ( 131 `a` time(4) DEFAULT NULL 132) ENGINE=MyISAM DEFAULT CHARSET=latin1 133show create table t3; 134Table Create Table 135t3 CREATE TABLE `t3` ( 136 `a` time(4) DEFAULT NULL 137) ENGINE=InnoDB DEFAULT CHARSET=latin1 138drop table t2, t3; 139insert t1 values ('2010-12-13 14:15:16.222222'); 140Warnings: 141Note 1265 Data truncated for column 'a' at row 1 142select a, a+0, a-1, a*1, a/2 from t1; 143a a+0 a-1 a*1 a/2 144NULL NULL NULL NULL NULL 14514:15:16.2222 141516.2222 141515.2222 141516.2222 70758.11110000 146select max(a), min(a), sum(a), avg(a) from t1; 147max(a) min(a) sum(a) avg(a) 14814:15:16.2222 14:15:16.2222 141516.2222 141516.22220000 149create table t2 select a, a+0, a-1, a*1, a/2 from t1; 150create table t3 select max(a), min(a), sum(a), avg(a) from t1; 151show create table t2; 152Table Create Table 153t2 CREATE TABLE `t2` ( 154 `a` time(4) DEFAULT NULL, 155 `a+0` decimal(12,4) DEFAULT NULL, 156 `a-1` decimal(12,4) DEFAULT NULL, 157 `a*1` decimal(12,4) DEFAULT NULL, 158 `a/2` decimal(15,8) DEFAULT NULL 159) ENGINE=MyISAM DEFAULT CHARSET=latin1 160show create table t3; 161Table Create Table 162t3 CREATE TABLE `t3` ( 163 `max(a)` time(4) DEFAULT NULL, 164 `min(a)` time(4) DEFAULT NULL, 165 `sum(a)` decimal(33,4) DEFAULT NULL, 166 `avg(a)` decimal(15,8) DEFAULT NULL 167) ENGINE=MyISAM DEFAULT CHARSET=latin1 168drop table t1, t2, t3; 169create table t1 (f0_time time(0), f1_time time(1), f2_time time(2), f3_time time(3), f4_time time(4), f5_time time(5), f6_time time(6)); 170insert t1 values ( '2010-11-12 11:14:17.765432', '2010-11-12 11:14:17.765432', '2010-11-12 11:14:17.765432', '2010-11-12 11:14:17.765432', '2010-11-12 11:14:17.765432', '2010-11-12 11:14:17.765432', '2010-11-12 11:14:17.765432'); 171Warnings: 172Level Note 173Code 1265 174Message Data truncated for column 'f0_time' at row 1 175Level Note 176Code 1265 177Message Data truncated for column 'f1_time' at row 1 178Level Note 179Code 1265 180Message Data truncated for column 'f2_time' at row 1 181Level Note 182Code 1265 183Message Data truncated for column 'f3_time' at row 1 184Level Note 185Code 1265 186Message Data truncated for column 'f4_time' at row 1 187Level Note 188Code 1265 189Message Data truncated for column 'f5_time' at row 1 190Level Note 191Code 1265 192Message Data truncated for column 'f6_time' at row 1 193select * from t1; 194f0_time 11:14:17 195f1_time 11:14:17.7 196f2_time 11:14:17.76 197f3_time 11:14:17.765 198f4_time 11:14:17.7654 199f5_time 11:14:17.76543 200f6_time 11:14:17.765432 201select cast(f0_time as time(4)) time4_f0_time, cast(f1_time as datetime(3)) datetime3_f1_time, cast(f2_time as date) date_f2_time, cast(f4_time as double) double_f3_time, cast(f4_time as decimal(40,5)) decimal5_f4_time, cast(f5_time as signed) bigint_f5_time, cast(f6_time as char(255)) varchar_f6_time from t1; 202time4_f0_time 11:14:17.0000 203datetime3_f1_time 2001-02-03 11:14:17.700 204date_f2_time 2001-02-03 205double_f3_time 111417.7654 206decimal5_f4_time 111417.76540 207bigint_f5_time 111417 208varchar_f6_time 11:14:17.765432 209create table t2 (time4_f0_time time(4), datetime3_f1_time datetime(3), date_f2_time date, double_f3_time double, decimal5_f4_time decimal(40,5), bigint_f5_time bigint, varchar_f6_time varchar(255)); 210insert t2 select * from t1; 211Warnings: 212Level Note 213Code 1265 214Message Data truncated for column 'date_f2_time' at row 1 215select * from t2; 216time4_f0_time 11:14:17.0000 217datetime3_f1_time 2001-02-03 11:14:17.700 218date_f2_time 2001-02-03 219double_f3_time 111417.765 220decimal5_f4_time 111417.76540 221bigint_f5_time 111417 222varchar_f6_time 11:14:17.765432 223alter table t1 change f0_time time4_f0_time time(4), change f1_time datetime3_f1_time datetime(3), change f2_time date_f2_time date, change f3_time double_f3_time double, change f4_time decimal5_f4_time decimal(40,5), change f5_time bigint_f5_time bigint, change f6_time varchar_f6_time varchar(255); 224Warnings: 225Level Note 226Code 1265 227Message Data truncated for column 'date_f2_time' at row 1 228select * from t1; 229time4_f0_time 11:14:17.0000 230datetime3_f1_time 2001-02-03 11:14:17.700 231date_f2_time 2001-02-03 232double_f3_time 111417.765 233decimal5_f4_time 111417.76540 234bigint_f5_time 111417 235varchar_f6_time 11:14:17.765432 236alter table t1 modify time4_f0_time time(0), modify datetime3_f1_time time(1), modify date_f2_time time(2), modify double_f3_time time(3), modify decimal5_f4_time time(4), modify bigint_f5_time time(5), modify varchar_f6_time time(6); 237Warnings: 238Level Note 239Code 1265 240Message Data truncated for column 'datetime3_f1_time' at row 1 241Level Note 242Code 1265 243Message Data truncated for column 'date_f2_time' at row 1 244select * from t1; 245time4_f0_time 11:14:17 246datetime3_f1_time 11:14:17.7 247date_f2_time 00:00:00.00 248double_f3_time 11:14:17.765 249decimal5_f4_time 11:14:17.7654 250bigint_f5_time 11:14:17.00000 251varchar_f6_time 11:14:17.765432 252delete from t1; 253insert t1 select * from t2; 254Warnings: 255Level Note 256Code 1265 257Message Data truncated for column 'datetime3_f1_time' at row 1 258Level Note 259Code 1265 260Message Data truncated for column 'date_f2_time' at row 1 261select * from t1; 262time4_f0_time 11:14:17 263datetime3_f1_time 11:14:17.7 264date_f2_time 00:00:00.00 265double_f3_time 11:14:17.764 266decimal5_f4_time 11:14:17.7654 267bigint_f5_time 11:14:17.00000 268varchar_f6_time 11:14:17.765432 269drop table t1, t2; 270create table t1 (a time(6), b time(6)); 271create procedure foo(x time, y time(4)) insert into t1 values (x, y); 272call foo('2010-02-03 4:5:6.789123', '2010-02-03 4:5:6.789123'); 273Warnings: 274Note 1265 Data truncated for column 'x' at row 1 275Note 1265 Data truncated for column 'y' at row 1 276select * from t1; 277a b 27804:05:06.000000 04:05:06.789100 279create procedure bar(a int, c time(5)) 280begin 281declare b time(4); 282set b = c + interval a microsecond; 283insert t1 values (b, c + interval a microsecond); 284end| 285call bar(1111111, '2011-01-02 3:4:5.123456'); 286Warnings: 287Note 1265 Data truncated for column 'c' at row 1 288select * from t1; 289a b 29004:05:06.000000 04:05:06.789100 29103:04:06.234500 03:04:06.234561 292drop procedure foo; 293drop procedure bar; 294create function xyz(s char(20)) returns time(4) 295return addtime('2010-10-10 10:10:10.101010', s); 296select xyz('1:1:1.010101'); 297xyz('1:1:1.010101') 29811:11:11.1111 299Warnings: 300Note 1265 Data truncated for column 'xyz('1:1:1.010101')' at row 1 301drop function xyz; 302create view v1 as select * from t1 group by a,b; 303select * from v1; 304a b 30503:04:06.234500 03:04:06.234561 30604:05:06.000000 04:05:06.789100 307show columns from v1; 308Field Type Null Key Default Extra 309a time(6) YES NULL 310b time(6) YES NULL 311create table t2 select * from v1; 312show create table t2; 313Table Create Table 314t2 CREATE TABLE `t2` ( 315 `a` time(6) DEFAULT NULL, 316 `b` time(6) DEFAULT NULL 317) ENGINE=MyISAM DEFAULT CHARSET=latin1 318select * from t2; 319a b 32003:04:06.234500 03:04:06.234561 32104:05:06.000000 04:05:06.789100 322drop view v1; 323drop table t1, t2; 324SET timestamp=DEFAULT; 325create table t1 (a time(4) not null, key(a)); 326insert into t1 values ('1:2:3.001'),('1:2:3'), ('-00:00:00.6'),('-00:00:00.7'),('-00:00:00.8'),('-00:00:00.9'),('-00:00:01.0'),('-00:00:01.1'),('-00:00:01.000000'),('-00:00:01.100001'),('-00:00:01.000002'),('-00:00:01.090000'); 327select * from t1 order by a; 328a 329-00:00:01.1000 330-00:00:01.1000 331-00:00:01.0900 332-00:00:01.0000 333-00:00:01.0000 334-00:00:01.0000 335-00:00:00.9000 336-00:00:00.8000 337-00:00:00.7000 338-00:00:00.6000 33901:02:03.0000 34001:02:03.0010 341select * from t1 order by a desc; 342a 34301:02:03.0010 34401:02:03.0000 345-00:00:00.6000 346-00:00:00.7000 347-00:00:00.8000 348-00:00:00.9000 349-00:00:01.0000 350-00:00:01.0000 351-00:00:01.0000 352-00:00:01.0900 353-00:00:01.1000 354-00:00:01.1000 355select min(a - interval 1 hour), max(a - interval 1 hour) from t1 where a < 0; 356min(a - interval 1 hour) max(a - interval 1 hour) 357-01:00:01.1000 -01:00:00.6000 358drop table t1; 359select cast(1e-6 as time(6)); 360cast(1e-6 as time(6)) 36100:00:00.000001 362# 363# Start of 10.4 tests 364# 365# 366# MDEV-20397 Support TIMESTAMP, DATETIME, TIME in ROUND() and TRUNCATE() 367# 368CREATE TABLE t1 (a1 TIME(6), a2 TIME(6) NOT NULL); 369CREATE TABLE t2 AS SELECT 370ROUND(a1) AS r1, 371ROUND(a2) AS r2, 372TRUNCATE(a1,0) AS t1, 373TRUNCATE(a2,0) AS t2 374FROM t1; 375SHOW CREATE TABLE t2; 376Table Create Table 377t2 CREATE TABLE `t2` ( 378 `r1` time DEFAULT NULL, 379 `r2` time NOT NULL, 380 `t1` time DEFAULT NULL, 381 `t2` time NOT NULL 382) ENGINE=MyISAM DEFAULT CHARSET=latin1 383DROP TABLE t2; 384DROP TABLE t1; 385CREATE TABLE t1 (a TIME(6)); 386INSERT INTO t1 VALUES 387('-838:59:59.999999'), 388('-837:59:59.999999'), 389('-23:59:59.999999'), 390('-00:59:59.999999'), 391('-00:00:59.999999'), 392('00:00:00.999999'), 393('00:00:59.999999'), 394('00:59:59.999999'), 395('23:59:59.999999'), 396('837:59:59.999999'), 397('838:59:59.999999'); 398SELECT a, TRUNCATE(a,0) FROM t1; 399a TRUNCATE(a,0) 400-838:59:59.999999 -838:59:59 401-837:59:59.999999 -837:59:59 402-23:59:59.999999 -23:59:59 403-00:59:59.999999 -00:59:59 404-00:00:59.999999 -00:00:59 40500:00:00.999999 00:00:00 40600:00:59.999999 00:00:59 40700:59:59.999999 00:59:59 40823:59:59.999999 23:59:59 409837:59:59.999999 837:59:59 410838:59:59.999999 838:59:59 411SELECT a, TRUNCATE(a,1) FROM t1; 412a TRUNCATE(a,1) 413-838:59:59.999999 -838:59:59.9 414-837:59:59.999999 -837:59:59.9 415-23:59:59.999999 -23:59:59.9 416-00:59:59.999999 -00:59:59.9 417-00:00:59.999999 -00:00:59.9 41800:00:00.999999 00:00:00.9 41900:00:59.999999 00:00:59.9 42000:59:59.999999 00:59:59.9 42123:59:59.999999 23:59:59.9 422837:59:59.999999 837:59:59.9 423838:59:59.999999 838:59:59.9 424SELECT a, TRUNCATE(a,2) FROM t1; 425a TRUNCATE(a,2) 426-838:59:59.999999 -838:59:59.99 427-837:59:59.999999 -837:59:59.99 428-23:59:59.999999 -23:59:59.99 429-00:59:59.999999 -00:59:59.99 430-00:00:59.999999 -00:00:59.99 43100:00:00.999999 00:00:00.99 43200:00:59.999999 00:00:59.99 43300:59:59.999999 00:59:59.99 43423:59:59.999999 23:59:59.99 435837:59:59.999999 837:59:59.99 436838:59:59.999999 838:59:59.99 437SELECT a, TRUNCATE(a,3) FROM t1; 438a TRUNCATE(a,3) 439-838:59:59.999999 -838:59:59.999 440-837:59:59.999999 -837:59:59.999 441-23:59:59.999999 -23:59:59.999 442-00:59:59.999999 -00:59:59.999 443-00:00:59.999999 -00:00:59.999 44400:00:00.999999 00:00:00.999 44500:00:59.999999 00:00:59.999 44600:59:59.999999 00:59:59.999 44723:59:59.999999 23:59:59.999 448837:59:59.999999 837:59:59.999 449838:59:59.999999 838:59:59.999 450SELECT a, TRUNCATE(a,4) FROM t1; 451a TRUNCATE(a,4) 452-838:59:59.999999 -838:59:59.9999 453-837:59:59.999999 -837:59:59.9999 454-23:59:59.999999 -23:59:59.9999 455-00:59:59.999999 -00:59:59.9999 456-00:00:59.999999 -00:00:59.9999 45700:00:00.999999 00:00:00.9999 45800:00:59.999999 00:00:59.9999 45900:59:59.999999 00:59:59.9999 46023:59:59.999999 23:59:59.9999 461837:59:59.999999 837:59:59.9999 462838:59:59.999999 838:59:59.9999 463SELECT a, TRUNCATE(a,5) FROM t1; 464a TRUNCATE(a,5) 465-838:59:59.999999 -838:59:59.99999 466-837:59:59.999999 -837:59:59.99999 467-23:59:59.999999 -23:59:59.99999 468-00:59:59.999999 -00:59:59.99999 469-00:00:59.999999 -00:00:59.99999 47000:00:00.999999 00:00:00.99999 47100:00:59.999999 00:00:59.99999 47200:59:59.999999 00:59:59.99999 47323:59:59.999999 23:59:59.99999 474837:59:59.999999 837:59:59.99999 475838:59:59.999999 838:59:59.99999 476SELECT a, TRUNCATE(a,6) FROM t1; 477a TRUNCATE(a,6) 478-838:59:59.999999 -838:59:59.999999 479-837:59:59.999999 -837:59:59.999999 480-23:59:59.999999 -23:59:59.999999 481-00:59:59.999999 -00:59:59.999999 482-00:00:59.999999 -00:00:59.999999 48300:00:00.999999 00:00:00.999999 48400:00:59.999999 00:00:59.999999 48500:59:59.999999 00:59:59.999999 48623:59:59.999999 23:59:59.999999 487837:59:59.999999 837:59:59.999999 488838:59:59.999999 838:59:59.999999 489SELECT a, TRUNCATE(a,7) FROM t1; 490a TRUNCATE(a,7) 491-838:59:59.999999 -838:59:59.999999 492-837:59:59.999999 -837:59:59.999999 493-23:59:59.999999 -23:59:59.999999 494-00:59:59.999999 -00:59:59.999999 495-00:00:59.999999 -00:00:59.999999 49600:00:00.999999 00:00:00.999999 49700:00:59.999999 00:00:59.999999 49800:59:59.999999 00:59:59.999999 49923:59:59.999999 23:59:59.999999 500837:59:59.999999 837:59:59.999999 501838:59:59.999999 838:59:59.999999 502SELECT a, TRUNCATE(a,-1) FROM t1; 503a TRUNCATE(a,-1) 504-838:59:59.999999 -838:59:59 505-837:59:59.999999 -837:59:59 506-23:59:59.999999 -23:59:59 507-00:59:59.999999 -00:59:59 508-00:00:59.999999 -00:00:59 50900:00:00.999999 00:00:00 51000:00:59.999999 00:00:59 51100:59:59.999999 00:59:59 51223:59:59.999999 23:59:59 513837:59:59.999999 837:59:59 514838:59:59.999999 838:59:59 515SELECT a, TRUNCATE(a,-6) FROM t1; 516a TRUNCATE(a,-6) 517-838:59:59.999999 -838:59:59 518-837:59:59.999999 -837:59:59 519-23:59:59.999999 -23:59:59 520-00:59:59.999999 -00:59:59 521-00:00:59.999999 -00:00:59 52200:00:00.999999 00:00:00 52300:00:59.999999 00:00:59 52400:59:59.999999 00:59:59 52523:59:59.999999 23:59:59 526837:59:59.999999 837:59:59 527838:59:59.999999 838:59:59 528SELECT a, ROUND(a) FROM t1; 529a ROUND(a) 530-838:59:59.999999 -838:59:59 531-837:59:59.999999 -838:00:00 532-23:59:59.999999 -24:00:00 533-00:59:59.999999 -01:00:00 534-00:00:59.999999 -00:01:00 53500:00:00.999999 00:00:01 53600:00:59.999999 00:01:00 53700:59:59.999999 01:00:00 53823:59:59.999999 24:00:00 539837:59:59.999999 838:00:00 540838:59:59.999999 838:59:59 541SELECT a, ROUND(a,0) FROM t1; 542a ROUND(a,0) 543-838:59:59.999999 -838:59:59 544-837:59:59.999999 -838:00:00 545-23:59:59.999999 -24:00:00 546-00:59:59.999999 -01:00:00 547-00:00:59.999999 -00:01:00 54800:00:00.999999 00:00:01 54900:00:59.999999 00:01:00 55000:59:59.999999 01:00:00 55123:59:59.999999 24:00:00 552837:59:59.999999 838:00:00 553838:59:59.999999 838:59:59 554SELECT a, ROUND(a,1) FROM t1; 555a ROUND(a,1) 556-838:59:59.999999 -838:59:59.9 557-837:59:59.999999 -838:00:00.0 558-23:59:59.999999 -24:00:00.0 559-00:59:59.999999 -01:00:00.0 560-00:00:59.999999 -00:01:00.0 56100:00:00.999999 00:00:01.0 56200:00:59.999999 00:01:00.0 56300:59:59.999999 01:00:00.0 56423:59:59.999999 24:00:00.0 565837:59:59.999999 838:00:00.0 566838:59:59.999999 838:59:59.9 567SELECT a, ROUND(a,2) FROM t1; 568a ROUND(a,2) 569-838:59:59.999999 -838:59:59.99 570-837:59:59.999999 -838:00:00.00 571-23:59:59.999999 -24:00:00.00 572-00:59:59.999999 -01:00:00.00 573-00:00:59.999999 -00:01:00.00 57400:00:00.999999 00:00:01.00 57500:00:59.999999 00:01:00.00 57600:59:59.999999 01:00:00.00 57723:59:59.999999 24:00:00.00 578837:59:59.999999 838:00:00.00 579838:59:59.999999 838:59:59.99 580SELECT a, ROUND(a,3) FROM t1; 581a ROUND(a,3) 582-838:59:59.999999 -838:59:59.999 583-837:59:59.999999 -838:00:00.000 584-23:59:59.999999 -24:00:00.000 585-00:59:59.999999 -01:00:00.000 586-00:00:59.999999 -00:01:00.000 58700:00:00.999999 00:00:01.000 58800:00:59.999999 00:01:00.000 58900:59:59.999999 01:00:00.000 59023:59:59.999999 24:00:00.000 591837:59:59.999999 838:00:00.000 592838:59:59.999999 838:59:59.999 593SELECT a, ROUND(a,4) FROM t1; 594a ROUND(a,4) 595-838:59:59.999999 -838:59:59.9999 596-837:59:59.999999 -838:00:00.0000 597-23:59:59.999999 -24:00:00.0000 598-00:59:59.999999 -01:00:00.0000 599-00:00:59.999999 -00:01:00.0000 60000:00:00.999999 00:00:01.0000 60100:00:59.999999 00:01:00.0000 60200:59:59.999999 01:00:00.0000 60323:59:59.999999 24:00:00.0000 604837:59:59.999999 838:00:00.0000 605838:59:59.999999 838:59:59.9999 606SELECT a, ROUND(a,5) FROM t1; 607a ROUND(a,5) 608-838:59:59.999999 -838:59:59.99999 609-837:59:59.999999 -838:00:00.00000 610-23:59:59.999999 -24:00:00.00000 611-00:59:59.999999 -01:00:00.00000 612-00:00:59.999999 -00:01:00.00000 61300:00:00.999999 00:00:01.00000 61400:00:59.999999 00:01:00.00000 61500:59:59.999999 01:00:00.00000 61623:59:59.999999 24:00:00.00000 617837:59:59.999999 838:00:00.00000 618838:59:59.999999 838:59:59.99999 619SELECT a, ROUND(a,6) FROM t1; 620a ROUND(a,6) 621-838:59:59.999999 -838:59:59.999999 622-837:59:59.999999 -837:59:59.999999 623-23:59:59.999999 -23:59:59.999999 624-00:59:59.999999 -00:59:59.999999 625-00:00:59.999999 -00:00:59.999999 62600:00:00.999999 00:00:00.999999 62700:00:59.999999 00:00:59.999999 62800:59:59.999999 00:59:59.999999 62923:59:59.999999 23:59:59.999999 630837:59:59.999999 837:59:59.999999 631838:59:59.999999 838:59:59.999999 632SELECT a, ROUND(a,7) FROM t1; 633a ROUND(a,7) 634-838:59:59.999999 -838:59:59.999999 635-837:59:59.999999 -837:59:59.999999 636-23:59:59.999999 -23:59:59.999999 637-00:59:59.999999 -00:59:59.999999 638-00:00:59.999999 -00:00:59.999999 63900:00:00.999999 00:00:00.999999 64000:00:59.999999 00:00:59.999999 64100:59:59.999999 00:59:59.999999 64223:59:59.999999 23:59:59.999999 643837:59:59.999999 837:59:59.999999 644838:59:59.999999 838:59:59.999999 645SELECT a, ROUND(a,-1) FROM t1; 646a ROUND(a,-1) 647-838:59:59.999999 -838:59:59 648-837:59:59.999999 -838:00:00 649-23:59:59.999999 -24:00:00 650-00:59:59.999999 -01:00:00 651-00:00:59.999999 -00:01:00 65200:00:00.999999 00:00:01 65300:00:59.999999 00:01:00 65400:59:59.999999 01:00:00 65523:59:59.999999 24:00:00 656837:59:59.999999 838:00:00 657838:59:59.999999 838:59:59 658SELECT a, ROUND(a,-6) FROM t1; 659a ROUND(a,-6) 660-838:59:59.999999 -838:59:59 661-837:59:59.999999 -838:00:00 662-23:59:59.999999 -24:00:00 663-00:59:59.999999 -01:00:00 664-00:00:59.999999 -00:01:00 66500:00:00.999999 00:00:01 66600:00:59.999999 00:01:00 66700:59:59.999999 01:00:00 66823:59:59.999999 24:00:00 669837:59:59.999999 838:00:00 670838:59:59.999999 838:59:59 671DROP TABLE t1; 672SET time_zone=DEFAULT; 673# 674# MDEV-23311 CEILING() and FLOOR() convert temporal input to numbers, unlike ROUND() and TRUNCATE() 675# 676CREATE TABLE t1 AS SELECT 677FLOOR(TIME'00:00:00.999999'), 678CEILING(TIME'00:00:00.999999'); 679SHOW CREATE TABLE t1; 680Table Create Table 681t1 CREATE TABLE `t1` ( 682 `FLOOR(TIME'00:00:00.999999')` time NOT NULL, 683 `CEILING(TIME'00:00:00.999999')` time NOT NULL 684) ENGINE=MyISAM DEFAULT CHARSET=latin1 685DROP TABLE t1; 686CREATE TABLE t1 (a TIME(6)); 687INSERT INTO t1 VALUES ('838:59:59.999999'); 688INSERT INTO t1 VALUES ('838:59:59.99999'); 689INSERT INTO t1 VALUES ('838:59:59.9999'); 690INSERT INTO t1 VALUES ('838:59:59.999'); 691INSERT INTO t1 VALUES ('838:59:59.99'); 692INSERT INTO t1 VALUES ('838:59:59.9'); 693INSERT INTO t1 VALUES ('838:59:59.1'); 694INSERT INTO t1 VALUES ('838:59:59.0'); 695INSERT INTO t1 VALUES ('837:59:59.999999'); 696INSERT INTO t1 VALUES ('837:59:59.99999'); 697INSERT INTO t1 VALUES ('837:59:59.9999'); 698INSERT INTO t1 VALUES ('837:59:59.999'); 699INSERT INTO t1 VALUES ('837:59:59.99'); 700INSERT INTO t1 VALUES ('837:59:59.9'); 701INSERT INTO t1 VALUES ('837:59:59.1'); 702INSERT INTO t1 VALUES ('837:59:59.0'); 703INSERT INTO t1 VALUES ('23:59:59.999999'); 704INSERT INTO t1 VALUES ('23:59:59.99999'); 705INSERT INTO t1 VALUES ('23:59:59.9999'); 706INSERT INTO t1 VALUES ('23:59:59.999'); 707INSERT INTO t1 VALUES ('23:59:59.99'); 708INSERT INTO t1 VALUES ('23:59:59.9'); 709INSERT INTO t1 VALUES ('23:59:59.1'); 710INSERT INTO t1 VALUES ('23:59:59.0'); 711INSERT INTO t1 VALUES ('00:00:00.999999'); 712INSERT INTO t1 VALUES ('00:00:00.99999'); 713INSERT INTO t1 VALUES ('00:00:00.9999'); 714INSERT INTO t1 VALUES ('00:00:00.999'); 715INSERT INTO t1 VALUES ('00:00:00.99'); 716INSERT INTO t1 VALUES ('00:00:00.9'); 717INSERT INTO t1 VALUES ('00:00:00.1'); 718INSERT INTO t1 VALUES ('00:00:00.0'); 719INSERT INTO t1 VALUES ('-00:00:00.999999'); 720INSERT INTO t1 VALUES ('-00:00:00.99999'); 721INSERT INTO t1 VALUES ('-00:00:00.9999'); 722INSERT INTO t1 VALUES ('-00:00:00.999'); 723INSERT INTO t1 VALUES ('-00:00:00.99'); 724INSERT INTO t1 VALUES ('-00:00:00.9'); 725INSERT INTO t1 VALUES ('-00:00:00.1'); 726INSERT INTO t1 VALUES ('-00:00:00.0'); 727INSERT INTO t1 VALUES ('-23:59:59.999999'); 728INSERT INTO t1 VALUES ('-23:59:59.99999'); 729INSERT INTO t1 VALUES ('-23:59:59.9999'); 730INSERT INTO t1 VALUES ('-23:59:59.999'); 731INSERT INTO t1 VALUES ('-23:59:59.99'); 732INSERT INTO t1 VALUES ('-23:59:59.9'); 733INSERT INTO t1 VALUES ('-23:59:59.1'); 734INSERT INTO t1 VALUES ('-23:59:59.0'); 735INSERT INTO t1 VALUES ('-837:59:59.999999'); 736INSERT INTO t1 VALUES ('-837:59:59.99999'); 737INSERT INTO t1 VALUES ('-837:59:59.9999'); 738INSERT INTO t1 VALUES ('-837:59:59.999'); 739INSERT INTO t1 VALUES ('-837:59:59.99'); 740INSERT INTO t1 VALUES ('-837:59:59.9'); 741INSERT INTO t1 VALUES ('-837:59:59.1'); 742INSERT INTO t1 VALUES ('-837:59:59.0'); 743INSERT INTO t1 VALUES ('-838:59:59.999999'); 744INSERT INTO t1 VALUES ('-838:59:59.99999'); 745INSERT INTO t1 VALUES ('-838:59:59.9999'); 746INSERT INTO t1 VALUES ('-838:59:59.999'); 747INSERT INTO t1 VALUES ('-838:59:59.99'); 748INSERT INTO t1 VALUES ('-838:59:59.9'); 749INSERT INTO t1 VALUES ('-838:59:59.1'); 750INSERT INTO t1 VALUES ('-838:59:59.0'); 751CREATE FUNCTION FLOOR_SP(a TIME(6)) RETURNS TIME 752BEGIN 753RETURN 754CASE 755WHEN TRUNCATE(a,0)=TIME'838:59:59' THEN a 756WHEN TRUNCATE(a,0)=TIME'-838:59:59' THEN a 757WHEN a=TRUNCATE(a,0) THEN a -- no fractional digits 758WHEN a<0 THEN TRUNCATE(a,0)-INTERVAL 1 SECOND -- negative values 759WHEN a>0 THEN TRUNCATE(a,0) -- positive values 760END; 761END; 762$$ 763CREATE FUNCTION CEILING_SP(a TIME(6)) RETURNS TIME 764BEGIN 765RETURN 766CASE 767WHEN TRUNCATE(a,0)=TIME'838:59:59' THEN a 768WHEN TRUNCATE(a,0)=TIME'-838:59:59' THEN a 769WHEN a=TRUNCATE(a,0) THEN a -- no fractional digits 770WHEN a<0 THEN TRUNCATE(a,0) -- negative values 771WHEN a>0 THEN TRUNCATE(a,0)+INTERVAL 1 SECOND -- positive values 772END; 773END; 774$$ 775SELECT a, FLOOR(a), FLOOR_SP(a), FLOOR(a)=FLOOR_SP(a) FROM t1 ORDER BY a; 776a FLOOR(a) FLOOR_SP(a) FLOOR(a)=FLOOR_SP(a) 777-838:59:59.999999 -838:59:59 -838:59:59 1 778-838:59:59.999990 -838:59:59 -838:59:59 1 779-838:59:59.999900 -838:59:59 -838:59:59 1 780-838:59:59.999000 -838:59:59 -838:59:59 1 781-838:59:59.990000 -838:59:59 -838:59:59 1 782-838:59:59.900000 -838:59:59 -838:59:59 1 783-838:59:59.100000 -838:59:59 -838:59:59 1 784-838:59:59.000000 -838:59:59 -838:59:59 1 785-837:59:59.999999 -838:00:00 -838:00:00 1 786-837:59:59.999990 -838:00:00 -838:00:00 1 787-837:59:59.999900 -838:00:00 -838:00:00 1 788-837:59:59.999000 -838:00:00 -838:00:00 1 789-837:59:59.990000 -838:00:00 -838:00:00 1 790-837:59:59.900000 -838:00:00 -838:00:00 1 791-837:59:59.100000 -838:00:00 -838:00:00 1 792-837:59:59.000000 -837:59:59 -837:59:59 1 793-23:59:59.999999 -24:00:00 -24:00:00 1 794-23:59:59.999990 -24:00:00 -24:00:00 1 795-23:59:59.999900 -24:00:00 -24:00:00 1 796-23:59:59.999000 -24:00:00 -24:00:00 1 797-23:59:59.990000 -24:00:00 -24:00:00 1 798-23:59:59.900000 -24:00:00 -24:00:00 1 799-23:59:59.100000 -24:00:00 -24:00:00 1 800-23:59:59.000000 -23:59:59 -23:59:59 1 801-00:00:00.999999 -00:00:01 -00:00:01 1 802-00:00:00.999990 -00:00:01 -00:00:01 1 803-00:00:00.999900 -00:00:01 -00:00:01 1 804-00:00:00.999000 -00:00:01 -00:00:01 1 805-00:00:00.990000 -00:00:01 -00:00:01 1 806-00:00:00.900000 -00:00:01 -00:00:01 1 807-00:00:00.100000 -00:00:01 -00:00:01 1 80800:00:00.000000 00:00:00 00:00:00 1 80900:00:00.000000 00:00:00 00:00:00 1 81000:00:00.100000 00:00:00 00:00:00 1 81100:00:00.900000 00:00:00 00:00:00 1 81200:00:00.990000 00:00:00 00:00:00 1 81300:00:00.999000 00:00:00 00:00:00 1 81400:00:00.999900 00:00:00 00:00:00 1 81500:00:00.999990 00:00:00 00:00:00 1 81600:00:00.999999 00:00:00 00:00:00 1 81723:59:59.000000 23:59:59 23:59:59 1 81823:59:59.100000 23:59:59 23:59:59 1 81923:59:59.900000 23:59:59 23:59:59 1 82023:59:59.990000 23:59:59 23:59:59 1 82123:59:59.999000 23:59:59 23:59:59 1 82223:59:59.999900 23:59:59 23:59:59 1 82323:59:59.999990 23:59:59 23:59:59 1 82423:59:59.999999 23:59:59 23:59:59 1 825837:59:59.000000 837:59:59 837:59:59 1 826837:59:59.100000 837:59:59 837:59:59 1 827837:59:59.900000 837:59:59 837:59:59 1 828837:59:59.990000 837:59:59 837:59:59 1 829837:59:59.999000 837:59:59 837:59:59 1 830837:59:59.999900 837:59:59 837:59:59 1 831837:59:59.999990 837:59:59 837:59:59 1 832837:59:59.999999 837:59:59 837:59:59 1 833838:59:59.000000 838:59:59 838:59:59 1 834838:59:59.100000 838:59:59 838:59:59 1 835838:59:59.900000 838:59:59 838:59:59 1 836838:59:59.990000 838:59:59 838:59:59 1 837838:59:59.999000 838:59:59 838:59:59 1 838838:59:59.999900 838:59:59 838:59:59 1 839838:59:59.999990 838:59:59 838:59:59 1 840838:59:59.999999 838:59:59 838:59:59 1 841SELECT a, CEILING(a), CEILING_SP(a), CEILING(a)=CEILING_SP(a) FROM t1 ORDER BY a; 842a CEILING(a) CEILING_SP(a) CEILING(a)=CEILING_SP(a) 843-838:59:59.999999 -838:59:59 -838:59:59 1 844-838:59:59.999990 -838:59:59 -838:59:59 1 845-838:59:59.999900 -838:59:59 -838:59:59 1 846-838:59:59.999000 -838:59:59 -838:59:59 1 847-838:59:59.990000 -838:59:59 -838:59:59 1 848-838:59:59.900000 -838:59:59 -838:59:59 1 849-838:59:59.100000 -838:59:59 -838:59:59 1 850-838:59:59.000000 -838:59:59 -838:59:59 1 851-837:59:59.999999 -837:59:59 -837:59:59 1 852-837:59:59.999990 -837:59:59 -837:59:59 1 853-837:59:59.999900 -837:59:59 -837:59:59 1 854-837:59:59.999000 -837:59:59 -837:59:59 1 855-837:59:59.990000 -837:59:59 -837:59:59 1 856-837:59:59.900000 -837:59:59 -837:59:59 1 857-837:59:59.100000 -837:59:59 -837:59:59 1 858-837:59:59.000000 -837:59:59 -837:59:59 1 859-23:59:59.999999 -23:59:59 -23:59:59 1 860-23:59:59.999990 -23:59:59 -23:59:59 1 861-23:59:59.999900 -23:59:59 -23:59:59 1 862-23:59:59.999000 -23:59:59 -23:59:59 1 863-23:59:59.990000 -23:59:59 -23:59:59 1 864-23:59:59.900000 -23:59:59 -23:59:59 1 865-23:59:59.100000 -23:59:59 -23:59:59 1 866-23:59:59.000000 -23:59:59 -23:59:59 1 867-00:00:00.999999 00:00:00 00:00:00 1 868-00:00:00.999990 00:00:00 00:00:00 1 869-00:00:00.999900 00:00:00 00:00:00 1 870-00:00:00.999000 00:00:00 00:00:00 1 871-00:00:00.990000 00:00:00 00:00:00 1 872-00:00:00.900000 00:00:00 00:00:00 1 873-00:00:00.100000 00:00:00 00:00:00 1 87400:00:00.000000 00:00:00 00:00:00 1 87500:00:00.000000 00:00:00 00:00:00 1 87600:00:00.100000 00:00:01 00:00:01 1 87700:00:00.900000 00:00:01 00:00:01 1 87800:00:00.990000 00:00:01 00:00:01 1 87900:00:00.999000 00:00:01 00:00:01 1 88000:00:00.999900 00:00:01 00:00:01 1 88100:00:00.999990 00:00:01 00:00:01 1 88200:00:00.999999 00:00:01 00:00:01 1 88323:59:59.000000 23:59:59 23:59:59 1 88423:59:59.100000 24:00:00 24:00:00 1 88523:59:59.900000 24:00:00 24:00:00 1 88623:59:59.990000 24:00:00 24:00:00 1 88723:59:59.999000 24:00:00 24:00:00 1 88823:59:59.999900 24:00:00 24:00:00 1 88923:59:59.999990 24:00:00 24:00:00 1 89023:59:59.999999 24:00:00 24:00:00 1 891837:59:59.000000 837:59:59 837:59:59 1 892837:59:59.100000 838:00:00 838:00:00 1 893837:59:59.900000 838:00:00 838:00:00 1 894837:59:59.990000 838:00:00 838:00:00 1 895837:59:59.999000 838:00:00 838:00:00 1 896837:59:59.999900 838:00:00 838:00:00 1 897837:59:59.999990 838:00:00 838:00:00 1 898837:59:59.999999 838:00:00 838:00:00 1 899838:59:59.000000 838:59:59 838:59:59 1 900838:59:59.100000 838:59:59 838:59:59 1 901838:59:59.900000 838:59:59 838:59:59 1 902838:59:59.990000 838:59:59 838:59:59 1 903838:59:59.999000 838:59:59 838:59:59 1 904838:59:59.999900 838:59:59 838:59:59 1 905838:59:59.999990 838:59:59 838:59:59 1 906838:59:59.999999 838:59:59 838:59:59 1 907DROP FUNCTION FLOOR_SP; 908DROP FUNCTION CEILING_SP; 909DROP TABLE t1; 910