SET timestamp=UNIX_TIMESTAMP('2001-02-03 10:20:30'); drop table if exists t1, t2, t3; create table t1 (a time(7)); ERROR 42000: Too big precision specified for 'a'. Maximum is 6 create table t1 (a time(3), key(a)); insert t1 values ('2010-12-11 00:20:03.1234'); Warnings: Note 1265 Data truncated for column 'a' at row 1 insert t1 values ('2010-12-11 15:47:11.1234'); Warnings: Note 1265 Data truncated for column 'a' at row 1 insert t1 values (20101211010203.45678); Warnings: Note 1265 Data truncated for column 'a' at row 1 insert t1 values (20101211030405.789e0); Warnings: Note 1265 Data truncated for column 'a' at row 1 insert ignore t1 values (99991231235959e1); Warnings: Warning 1264 Out of range value for column 'a' at row 1 select * from t1; a 00:20:03.123 01:02:03.456 03:04:05.789 15:47:11.123 838:59:59.999 select cast(a AS double(30,6)) from t1; cast(a AS double(30,6)) 2003.123000 10203.456000 30405.789062 154711.123000 8385959.999000 select a DIV 1 from t1; a DIV 1 2003 10203 30405 154711 8385959 select group_concat(distinct a) from t1; group_concat(distinct a) 00:20:03.123,01:02:03.456,03:04:05.789,15:47:11.123,838:59:59.999 alter table t1 engine=innodb; select * from t1 order by a; a 00:20:03.123 01:02:03.456 03:04:05.789 15:47:11.123 838:59:59.999 select * from t1 order by a+0; a 00:20:03.123 01:02:03.456 03:04:05.789 15:47:11.123 838:59:59.999 drop table t1; create table t1 (a time(4)) engine=innodb; insert t1 values ('2010-12-11 01:02:03.456789'); Warnings: Note 1265 Data truncated for column 'a' at row 1 select * from t1; a 01:02:03.4567 select extract(microsecond from a + interval 100 microsecond) from t1 where a>'2010-11-12 01:02:03.456'; extract(microsecond from a + interval 100 microsecond) 456800 select a from t1 where a>'2010-11-12 01:02:03.456' group by a; a 01:02:03.4567 show create table t1; Table Create Table t1 CREATE TABLE `t1` ( `a` time(4) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci show columns from t1; Field Type Null Key Default Extra a time(4) YES NULL select 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'; table_name t1 column_name a column_default NULL is_nullable YES data_type time character_maximum_length NULL character_octet_length NULL numeric_precision NULL numeric_scale NULL datetime_precision 4 character_set_name NULL collation_name NULL column_type time(4) column_key extra select a, a+interval 9876543 microsecond from t1; a a+interval 9876543 microsecond 01:02:03.4567 01:02:13.333243 update t1 set a=a+interval 9876543 microsecond; select * from t1; a 01:02:13.3332 select a, a + interval 2 year from t1; a a + interval 2 year 01:02:13.3332 NULL Warnings: Warning 1441 Datetime function: time field overflow insert ignore t1 select a + interval 2 year from t1; Warnings: Warning 1441 Datetime function: time field overflow select * from t1; a 01:02:13.3332 NULL delete from t1 where a < 20110101; select * from t1; a 01:02:13.3332 NULL delete from t1 where a is not null; select * from t1; a NULL create table t2 select * from t1; create table t3 like t1; show create table t2; Table Create Table t2 CREATE TABLE `t2` ( `a` time(4) DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci show create table t3; Table Create Table t3 CREATE TABLE `t3` ( `a` time(4) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci drop table t2, t3; insert t1 values ('2010-12-13 14:15:16.222222'); Warnings: Note 1265 Data truncated for column 'a' at row 1 select a, a+0, a-1, a*1, a/2 from t1; a a+0 a-1 a*1 a/2 NULL NULL NULL NULL NULL 14:15:16.2222 141516.2222 141515.2222 141516.2222 70758.11110000 select max(a), min(a), sum(a), avg(a) from t1; max(a) min(a) sum(a) avg(a) 14:15:16.2222 14:15:16.2222 141516.2222 141516.22220000 create table t2 select a, a+0, a-1, a*1, a/2 from t1; create table t3 select max(a), min(a), sum(a), avg(a) from t1; show create table t2; Table Create Table t2 CREATE TABLE `t2` ( `a` time(4) DEFAULT NULL, `a+0` decimal(12,4) DEFAULT NULL, `a-1` decimal(12,4) DEFAULT NULL, `a*1` decimal(12,4) DEFAULT NULL, `a/2` decimal(15,8) DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci show create table t3; Table Create Table t3 CREATE TABLE `t3` ( `max(a)` time(4) DEFAULT NULL, `min(a)` time(4) DEFAULT NULL, `sum(a)` decimal(33,4) DEFAULT NULL, `avg(a)` decimal(15,8) DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci drop table t1, t2, t3; create 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)); insert 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'); Warnings: Level Note Code 1265 Message Data truncated for column 'f0_time' at row 1 Level Note Code 1265 Message Data truncated for column 'f1_time' at row 1 Level Note Code 1265 Message Data truncated for column 'f2_time' at row 1 Level Note Code 1265 Message Data truncated for column 'f3_time' at row 1 Level Note Code 1265 Message Data truncated for column 'f4_time' at row 1 Level Note Code 1265 Message Data truncated for column 'f5_time' at row 1 Level Note Code 1265 Message Data truncated for column 'f6_time' at row 1 select * from t1; f0_time 11:14:17 f1_time 11:14:17.7 f2_time 11:14:17.76 f3_time 11:14:17.765 f4_time 11:14:17.7654 f5_time 11:14:17.76543 f6_time 11:14:17.765432 select 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; time4_f0_time 11:14:17.0000 datetime3_f1_time 2001-02-03 11:14:17.700 date_f2_time 2001-02-03 double_f3_time 111417.7654 decimal5_f4_time 111417.76540 bigint_f5_time 111417 varchar_f6_time 11:14:17.765432 create 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)); insert t2 select * from t1; Warnings: Level Note Code 1265 Message Data truncated for column 'date_f2_time' at row 1 select * from t2; time4_f0_time 11:14:17.0000 datetime3_f1_time 2001-02-03 11:14:17.700 date_f2_time 2001-02-03 double_f3_time 111417.765 decimal5_f4_time 111417.76540 bigint_f5_time 111417 varchar_f6_time 11:14:17.765432 alter 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); Warnings: Level Note Code 1265 Message Data truncated for column 'date_f2_time' at row 1 select * from t1; time4_f0_time 11:14:17.0000 datetime3_f1_time 2001-02-03 11:14:17.700 date_f2_time 2001-02-03 double_f3_time 111417.765 decimal5_f4_time 111417.76540 bigint_f5_time 111417 varchar_f6_time 11:14:17.765432 alter 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); Warnings: Level Note Code 1265 Message Data truncated for column 'datetime3_f1_time' at row 1 Level Note Code 1265 Message Data truncated for column 'date_f2_time' at row 1 select * from t1; time4_f0_time 11:14:17 datetime3_f1_time 11:14:17.7 date_f2_time 00:00:00.00 double_f3_time 11:14:17.765 decimal5_f4_time 11:14:17.7654 bigint_f5_time 11:14:17.00000 varchar_f6_time 11:14:17.765432 delete from t1; insert t1 select * from t2; Warnings: Level Note Code 1265 Message Data truncated for column 'datetime3_f1_time' at row 1 Level Note Code 1265 Message Data truncated for column 'date_f2_time' at row 1 select * from t1; time4_f0_time 11:14:17 datetime3_f1_time 11:14:17.7 date_f2_time 00:00:00.00 double_f3_time 11:14:17.764 decimal5_f4_time 11:14:17.7654 bigint_f5_time 11:14:17.00000 varchar_f6_time 11:14:17.765432 drop table t1, t2; create table t1 (a time(6), b time(6)); create procedure foo(x time, y time(4)) insert into t1 values (x, y); call foo('2010-02-03 4:5:6.789123', '2010-02-03 4:5:6.789123'); Warnings: Note 1265 Data truncated for column 'x' at row 0 Note 1265 Data truncated for column 'y' at row 0 select * from t1; a b 04:05:06.000000 04:05:06.789100 create procedure bar(a int, c time(5)) begin declare b time(4); set b = c + interval a microsecond; insert t1 values (b, c + interval a microsecond); end| call bar(1111111, '2011-01-02 3:4:5.123456'); Warnings: Note 1265 Data truncated for column 'c' at row 0 select * from t1; a b 04:05:06.000000 04:05:06.789100 03:04:06.234500 03:04:06.234561 drop procedure foo; drop procedure bar; create function xyz(s char(20)) returns time(4) return addtime('2010-10-10 10:10:10.101010', s); select xyz('1:1:1.010101'); xyz('1:1:1.010101') 11:11:11.1111 Warnings: Note 1265 Data truncated for column 'xyz('1:1:1.010101')' at row 0 drop function xyz; create view v1 as select * from t1 group by a,b; select * from v1; a b 03:04:06.234500 03:04:06.234561 04:05:06.000000 04:05:06.789100 show columns from v1; Field Type Null Key Default Extra a time(6) YES NULL b time(6) YES NULL create table t2 select * from v1; show create table t2; Table Create Table t2 CREATE TABLE `t2` ( `a` time(6) DEFAULT NULL, `b` time(6) DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci select * from t2; a b 03:04:06.234500 03:04:06.234561 04:05:06.000000 04:05:06.789100 drop view v1; drop table t1, t2; SET timestamp=DEFAULT; create table t1 (a time(4) not null, key(a)); insert 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'); select * from t1 order by a; a -00:00:01.1000 -00:00:01.1000 -00:00:01.0900 -00:00:01.0000 -00:00:01.0000 -00:00:01.0000 -00:00:00.9000 -00:00:00.8000 -00:00:00.7000 -00:00:00.6000 01:02:03.0000 01:02:03.0010 select * from t1 order by a desc; a 01:02:03.0010 01:02:03.0000 -00:00:00.6000 -00:00:00.7000 -00:00:00.8000 -00:00:00.9000 -00:00:01.0000 -00:00:01.0000 -00:00:01.0000 -00:00:01.0900 -00:00:01.1000 -00:00:01.1000 select min(a - interval 1 hour), max(a - interval 1 hour) from t1 where a < 0; min(a - interval 1 hour) max(a - interval 1 hour) -01:00:01.1000 -01:00:00.6000 drop table t1; select cast(1e-6 as time(6)); cast(1e-6 as time(6)) 00:00:00.000001 # # End of 5.5 tests # # # MDEV-20397 Support TIMESTAMP, DATETIME, TIME in ROUND() and TRUNCATE() # CREATE TABLE t1 (a1 TIME(6), a2 TIME(6) NOT NULL); CREATE TABLE t2 AS SELECT ROUND(a1) AS r1, ROUND(a2) AS r2, TRUNCATE(a1,0) AS t1, TRUNCATE(a2,0) AS t2 FROM t1; SHOW CREATE TABLE t2; Table Create Table t2 CREATE TABLE `t2` ( `r1` time DEFAULT NULL, `r2` time NOT NULL, `t1` time DEFAULT NULL, `t2` time NOT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci DROP TABLE t2; DROP TABLE t1; CREATE TABLE t1 (a TIME(6)); INSERT INTO t1 VALUES ('-838:59:59.999999'), ('-837:59:59.999999'), ('-23:59:59.999999'), ('-00:59:59.999999'), ('-00:00:59.999999'), ('00:00:00.999999'), ('00:00:59.999999'), ('00:59:59.999999'), ('23:59:59.999999'), ('837:59:59.999999'), ('838:59:59.999999'); SELECT a, TRUNCATE(a,0) FROM t1; a TRUNCATE(a,0) -838:59:59.999999 -838:59:59 -837:59:59.999999 -837:59:59 -23:59:59.999999 -23:59:59 -00:59:59.999999 -00:59:59 -00:00:59.999999 -00:00:59 00:00:00.999999 00:00:00 00:00:59.999999 00:00:59 00:59:59.999999 00:59:59 23:59:59.999999 23:59:59 837:59:59.999999 837:59:59 838:59:59.999999 838:59:59 SELECT a, TRUNCATE(a,1) FROM t1; a TRUNCATE(a,1) -838:59:59.999999 -838:59:59.9 -837:59:59.999999 -837:59:59.9 -23:59:59.999999 -23:59:59.9 -00:59:59.999999 -00:59:59.9 -00:00:59.999999 -00:00:59.9 00:00:00.999999 00:00:00.9 00:00:59.999999 00:00:59.9 00:59:59.999999 00:59:59.9 23:59:59.999999 23:59:59.9 837:59:59.999999 837:59:59.9 838:59:59.999999 838:59:59.9 SELECT a, TRUNCATE(a,2) FROM t1; a TRUNCATE(a,2) -838:59:59.999999 -838:59:59.99 -837:59:59.999999 -837:59:59.99 -23:59:59.999999 -23:59:59.99 -00:59:59.999999 -00:59:59.99 -00:00:59.999999 -00:00:59.99 00:00:00.999999 00:00:00.99 00:00:59.999999 00:00:59.99 00:59:59.999999 00:59:59.99 23:59:59.999999 23:59:59.99 837:59:59.999999 837:59:59.99 838:59:59.999999 838:59:59.99 SELECT a, TRUNCATE(a,3) FROM t1; a TRUNCATE(a,3) -838:59:59.999999 -838:59:59.999 -837:59:59.999999 -837:59:59.999 -23:59:59.999999 -23:59:59.999 -00:59:59.999999 -00:59:59.999 -00:00:59.999999 -00:00:59.999 00:00:00.999999 00:00:00.999 00:00:59.999999 00:00:59.999 00:59:59.999999 00:59:59.999 23:59:59.999999 23:59:59.999 837:59:59.999999 837:59:59.999 838:59:59.999999 838:59:59.999 SELECT a, TRUNCATE(a,4) FROM t1; a TRUNCATE(a,4) -838:59:59.999999 -838:59:59.9999 -837:59:59.999999 -837:59:59.9999 -23:59:59.999999 -23:59:59.9999 -00:59:59.999999 -00:59:59.9999 -00:00:59.999999 -00:00:59.9999 00:00:00.999999 00:00:00.9999 00:00:59.999999 00:00:59.9999 00:59:59.999999 00:59:59.9999 23:59:59.999999 23:59:59.9999 837:59:59.999999 837:59:59.9999 838:59:59.999999 838:59:59.9999 SELECT a, TRUNCATE(a,5) FROM t1; a TRUNCATE(a,5) -838:59:59.999999 -838:59:59.99999 -837:59:59.999999 -837:59:59.99999 -23:59:59.999999 -23:59:59.99999 -00:59:59.999999 -00:59:59.99999 -00:00:59.999999 -00:00:59.99999 00:00:00.999999 00:00:00.99999 00:00:59.999999 00:00:59.99999 00:59:59.999999 00:59:59.99999 23:59:59.999999 23:59:59.99999 837:59:59.999999 837:59:59.99999 838:59:59.999999 838:59:59.99999 SELECT a, TRUNCATE(a,6) FROM t1; a TRUNCATE(a,6) -838:59:59.999999 -838:59:59.999999 -837:59:59.999999 -837:59:59.999999 -23:59:59.999999 -23:59:59.999999 -00:59:59.999999 -00:59:59.999999 -00:00:59.999999 -00:00:59.999999 00:00:00.999999 00:00:00.999999 00:00:59.999999 00:00:59.999999 00:59:59.999999 00:59:59.999999 23:59:59.999999 23:59:59.999999 837:59:59.999999 837:59:59.999999 838:59:59.999999 838:59:59.999999 SELECT a, TRUNCATE(a,7) FROM t1; a TRUNCATE(a,7) -838:59:59.999999 -838:59:59.999999 -837:59:59.999999 -837:59:59.999999 -23:59:59.999999 -23:59:59.999999 -00:59:59.999999 -00:59:59.999999 -00:00:59.999999 -00:00:59.999999 00:00:00.999999 00:00:00.999999 00:00:59.999999 00:00:59.999999 00:59:59.999999 00:59:59.999999 23:59:59.999999 23:59:59.999999 837:59:59.999999 837:59:59.999999 838:59:59.999999 838:59:59.999999 SELECT a, TRUNCATE(a,-1) FROM t1; a TRUNCATE(a,-1) -838:59:59.999999 -838:59:59 -837:59:59.999999 -837:59:59 -23:59:59.999999 -23:59:59 -00:59:59.999999 -00:59:59 -00:00:59.999999 -00:00:59 00:00:00.999999 00:00:00 00:00:59.999999 00:00:59 00:59:59.999999 00:59:59 23:59:59.999999 23:59:59 837:59:59.999999 837:59:59 838:59:59.999999 838:59:59 SELECT a, TRUNCATE(a,-6) FROM t1; a TRUNCATE(a,-6) -838:59:59.999999 -838:59:59 -837:59:59.999999 -837:59:59 -23:59:59.999999 -23:59:59 -00:59:59.999999 -00:59:59 -00:00:59.999999 -00:00:59 00:00:00.999999 00:00:00 00:00:59.999999 00:00:59 00:59:59.999999 00:59:59 23:59:59.999999 23:59:59 837:59:59.999999 837:59:59 838:59:59.999999 838:59:59 SELECT a, ROUND(a) FROM t1; a ROUND(a) -838:59:59.999999 -838:59:59 -837:59:59.999999 -838:00:00 -23:59:59.999999 -24:00:00 -00:59:59.999999 -01:00:00 -00:00:59.999999 -00:01:00 00:00:00.999999 00:00:01 00:00:59.999999 00:01:00 00:59:59.999999 01:00:00 23:59:59.999999 24:00:00 837:59:59.999999 838:00:00 838:59:59.999999 838:59:59 SELECT a, ROUND(a,0) FROM t1; a ROUND(a,0) -838:59:59.999999 -838:59:59 -837:59:59.999999 -838:00:00 -23:59:59.999999 -24:00:00 -00:59:59.999999 -01:00:00 -00:00:59.999999 -00:01:00 00:00:00.999999 00:00:01 00:00:59.999999 00:01:00 00:59:59.999999 01:00:00 23:59:59.999999 24:00:00 837:59:59.999999 838:00:00 838:59:59.999999 838:59:59 SELECT a, ROUND(a,1) FROM t1; a ROUND(a,1) -838:59:59.999999 -838:59:59.9 -837:59:59.999999 -838:00:00.0 -23:59:59.999999 -24:00:00.0 -00:59:59.999999 -01:00:00.0 -00:00:59.999999 -00:01:00.0 00:00:00.999999 00:00:01.0 00:00:59.999999 00:01:00.0 00:59:59.999999 01:00:00.0 23:59:59.999999 24:00:00.0 837:59:59.999999 838:00:00.0 838:59:59.999999 838:59:59.9 SELECT a, ROUND(a,2) FROM t1; a ROUND(a,2) -838:59:59.999999 -838:59:59.99 -837:59:59.999999 -838:00:00.00 -23:59:59.999999 -24:00:00.00 -00:59:59.999999 -01:00:00.00 -00:00:59.999999 -00:01:00.00 00:00:00.999999 00:00:01.00 00:00:59.999999 00:01:00.00 00:59:59.999999 01:00:00.00 23:59:59.999999 24:00:00.00 837:59:59.999999 838:00:00.00 838:59:59.999999 838:59:59.99 SELECT a, ROUND(a,3) FROM t1; a ROUND(a,3) -838:59:59.999999 -838:59:59.999 -837:59:59.999999 -838:00:00.000 -23:59:59.999999 -24:00:00.000 -00:59:59.999999 -01:00:00.000 -00:00:59.999999 -00:01:00.000 00:00:00.999999 00:00:01.000 00:00:59.999999 00:01:00.000 00:59:59.999999 01:00:00.000 23:59:59.999999 24:00:00.000 837:59:59.999999 838:00:00.000 838:59:59.999999 838:59:59.999 SELECT a, ROUND(a,4) FROM t1; a ROUND(a,4) -838:59:59.999999 -838:59:59.9999 -837:59:59.999999 -838:00:00.0000 -23:59:59.999999 -24:00:00.0000 -00:59:59.999999 -01:00:00.0000 -00:00:59.999999 -00:01:00.0000 00:00:00.999999 00:00:01.0000 00:00:59.999999 00:01:00.0000 00:59:59.999999 01:00:00.0000 23:59:59.999999 24:00:00.0000 837:59:59.999999 838:00:00.0000 838:59:59.999999 838:59:59.9999 SELECT a, ROUND(a,5) FROM t1; a ROUND(a,5) -838:59:59.999999 -838:59:59.99999 -837:59:59.999999 -838:00:00.00000 -23:59:59.999999 -24:00:00.00000 -00:59:59.999999 -01:00:00.00000 -00:00:59.999999 -00:01:00.00000 00:00:00.999999 00:00:01.00000 00:00:59.999999 00:01:00.00000 00:59:59.999999 01:00:00.00000 23:59:59.999999 24:00:00.00000 837:59:59.999999 838:00:00.00000 838:59:59.999999 838:59:59.99999 SELECT a, ROUND(a,6) FROM t1; a ROUND(a,6) -838:59:59.999999 -838:59:59.999999 -837:59:59.999999 -837:59:59.999999 -23:59:59.999999 -23:59:59.999999 -00:59:59.999999 -00:59:59.999999 -00:00:59.999999 -00:00:59.999999 00:00:00.999999 00:00:00.999999 00:00:59.999999 00:00:59.999999 00:59:59.999999 00:59:59.999999 23:59:59.999999 23:59:59.999999 837:59:59.999999 837:59:59.999999 838:59:59.999999 838:59:59.999999 SELECT a, ROUND(a,7) FROM t1; a ROUND(a,7) -838:59:59.999999 -838:59:59.999999 -837:59:59.999999 -837:59:59.999999 -23:59:59.999999 -23:59:59.999999 -00:59:59.999999 -00:59:59.999999 -00:00:59.999999 -00:00:59.999999 00:00:00.999999 00:00:00.999999 00:00:59.999999 00:00:59.999999 00:59:59.999999 00:59:59.999999 23:59:59.999999 23:59:59.999999 837:59:59.999999 837:59:59.999999 838:59:59.999999 838:59:59.999999 SELECT a, ROUND(a,-1) FROM t1; a ROUND(a,-1) -838:59:59.999999 -838:59:59 -837:59:59.999999 -838:00:00 -23:59:59.999999 -24:00:00 -00:59:59.999999 -01:00:00 -00:00:59.999999 -00:01:00 00:00:00.999999 00:00:01 00:00:59.999999 00:01:00 00:59:59.999999 01:00:00 23:59:59.999999 24:00:00 837:59:59.999999 838:00:00 838:59:59.999999 838:59:59 SELECT a, ROUND(a,-6) FROM t1; a ROUND(a,-6) -838:59:59.999999 -838:59:59 -837:59:59.999999 -838:00:00 -23:59:59.999999 -24:00:00 -00:59:59.999999 -01:00:00 -00:00:59.999999 -00:01:00 00:00:00.999999 00:00:01 00:00:59.999999 00:01:00 00:59:59.999999 01:00:00 23:59:59.999999 24:00:00 837:59:59.999999 838:00:00 838:59:59.999999 838:59:59 DROP TABLE t1; SET time_zone=DEFAULT; # # MDEV-23311 CEILING() and FLOOR() convert temporal input to numbers, unlike ROUND() and TRUNCATE() # CREATE TABLE t1 AS SELECT FLOOR(TIME'00:00:00.999999'), CEILING(TIME'00:00:00.999999'); SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( `FLOOR(TIME'00:00:00.999999')` time NOT NULL, `CEILING(TIME'00:00:00.999999')` time NOT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci DROP TABLE t1; CREATE TABLE t1 (a TIME(6)); INSERT INTO t1 VALUES ('838:59:59.999999'); INSERT INTO t1 VALUES ('838:59:59.99999'); INSERT INTO t1 VALUES ('838:59:59.9999'); INSERT INTO t1 VALUES ('838:59:59.999'); INSERT INTO t1 VALUES ('838:59:59.99'); INSERT INTO t1 VALUES ('838:59:59.9'); INSERT INTO t1 VALUES ('838:59:59.1'); INSERT INTO t1 VALUES ('838:59:59.0'); INSERT INTO t1 VALUES ('837:59:59.999999'); INSERT INTO t1 VALUES ('837:59:59.99999'); INSERT INTO t1 VALUES ('837:59:59.9999'); INSERT INTO t1 VALUES ('837:59:59.999'); INSERT INTO t1 VALUES ('837:59:59.99'); INSERT INTO t1 VALUES ('837:59:59.9'); INSERT INTO t1 VALUES ('837:59:59.1'); INSERT INTO t1 VALUES ('837:59:59.0'); INSERT INTO t1 VALUES ('23:59:59.999999'); INSERT INTO t1 VALUES ('23:59:59.99999'); INSERT INTO t1 VALUES ('23:59:59.9999'); INSERT INTO t1 VALUES ('23:59:59.999'); INSERT INTO t1 VALUES ('23:59:59.99'); INSERT INTO t1 VALUES ('23:59:59.9'); INSERT INTO t1 VALUES ('23:59:59.1'); INSERT INTO t1 VALUES ('23:59:59.0'); INSERT INTO t1 VALUES ('00:00:00.999999'); INSERT INTO t1 VALUES ('00:00:00.99999'); INSERT INTO t1 VALUES ('00:00:00.9999'); INSERT INTO t1 VALUES ('00:00:00.999'); INSERT INTO t1 VALUES ('00:00:00.99'); INSERT INTO t1 VALUES ('00:00:00.9'); INSERT INTO t1 VALUES ('00:00:00.1'); INSERT INTO t1 VALUES ('00:00:00.0'); INSERT INTO t1 VALUES ('-00:00:00.999999'); INSERT INTO t1 VALUES ('-00:00:00.99999'); INSERT INTO t1 VALUES ('-00:00:00.9999'); INSERT INTO t1 VALUES ('-00:00:00.999'); INSERT INTO t1 VALUES ('-00:00:00.99'); INSERT INTO t1 VALUES ('-00:00:00.9'); INSERT INTO t1 VALUES ('-00:00:00.1'); INSERT INTO t1 VALUES ('-00:00:00.0'); INSERT INTO t1 VALUES ('-23:59:59.999999'); INSERT INTO t1 VALUES ('-23:59:59.99999'); INSERT INTO t1 VALUES ('-23:59:59.9999'); INSERT INTO t1 VALUES ('-23:59:59.999'); INSERT INTO t1 VALUES ('-23:59:59.99'); INSERT INTO t1 VALUES ('-23:59:59.9'); INSERT INTO t1 VALUES ('-23:59:59.1'); INSERT INTO t1 VALUES ('-23:59:59.0'); INSERT INTO t1 VALUES ('-837:59:59.999999'); INSERT INTO t1 VALUES ('-837:59:59.99999'); INSERT INTO t1 VALUES ('-837:59:59.9999'); INSERT INTO t1 VALUES ('-837:59:59.999'); INSERT INTO t1 VALUES ('-837:59:59.99'); INSERT INTO t1 VALUES ('-837:59:59.9'); INSERT INTO t1 VALUES ('-837:59:59.1'); INSERT INTO t1 VALUES ('-837:59:59.0'); INSERT INTO t1 VALUES ('-838:59:59.999999'); INSERT INTO t1 VALUES ('-838:59:59.99999'); INSERT INTO t1 VALUES ('-838:59:59.9999'); INSERT INTO t1 VALUES ('-838:59:59.999'); INSERT INTO t1 VALUES ('-838:59:59.99'); INSERT INTO t1 VALUES ('-838:59:59.9'); INSERT INTO t1 VALUES ('-838:59:59.1'); INSERT INTO t1 VALUES ('-838:59:59.0'); CREATE FUNCTION FLOOR_SP(a TIME(6)) RETURNS TIME BEGIN RETURN CASE WHEN TRUNCATE(a,0)=TIME'838:59:59' THEN a WHEN TRUNCATE(a,0)=TIME'-838:59:59' THEN a WHEN a=TRUNCATE(a,0) THEN a -- no fractional digits WHEN a<0 THEN TRUNCATE(a,0)-INTERVAL 1 SECOND -- negative values WHEN a>0 THEN TRUNCATE(a,0) -- positive values END; END; $$ CREATE FUNCTION CEILING_SP(a TIME(6)) RETURNS TIME BEGIN RETURN CASE WHEN TRUNCATE(a,0)=TIME'838:59:59' THEN a WHEN TRUNCATE(a,0)=TIME'-838:59:59' THEN a WHEN a=TRUNCATE(a,0) THEN a -- no fractional digits WHEN a<0 THEN TRUNCATE(a,0) -- negative values WHEN a>0 THEN TRUNCATE(a,0)+INTERVAL 1 SECOND -- positive values END; END; $$ SELECT a, FLOOR(a), FLOOR_SP(a), FLOOR(a)=FLOOR_SP(a) FROM t1 ORDER BY a; a FLOOR(a) FLOOR_SP(a) FLOOR(a)=FLOOR_SP(a) -838:59:59.999999 -838:59:59 -838:59:59 1 -838:59:59.999990 -838:59:59 -838:59:59 1 -838:59:59.999900 -838:59:59 -838:59:59 1 -838:59:59.999000 -838:59:59 -838:59:59 1 -838:59:59.990000 -838:59:59 -838:59:59 1 -838:59:59.900000 -838:59:59 -838:59:59 1 -838:59:59.100000 -838:59:59 -838:59:59 1 -838:59:59.000000 -838:59:59 -838:59:59 1 -837:59:59.999999 -838:00:00 -838:00:00 1 -837:59:59.999990 -838:00:00 -838:00:00 1 -837:59:59.999900 -838:00:00 -838:00:00 1 -837:59:59.999000 -838:00:00 -838:00:00 1 -837:59:59.990000 -838:00:00 -838:00:00 1 -837:59:59.900000 -838:00:00 -838:00:00 1 -837:59:59.100000 -838:00:00 -838:00:00 1 -837:59:59.000000 -837:59:59 -837:59:59 1 -23:59:59.999999 -24:00:00 -24:00:00 1 -23:59:59.999990 -24:00:00 -24:00:00 1 -23:59:59.999900 -24:00:00 -24:00:00 1 -23:59:59.999000 -24:00:00 -24:00:00 1 -23:59:59.990000 -24:00:00 -24:00:00 1 -23:59:59.900000 -24:00:00 -24:00:00 1 -23:59:59.100000 -24:00:00 -24:00:00 1 -23:59:59.000000 -23:59:59 -23:59:59 1 -00:00:00.999999 -00:00:01 -00:00:01 1 -00:00:00.999990 -00:00:01 -00:00:01 1 -00:00:00.999900 -00:00:01 -00:00:01 1 -00:00:00.999000 -00:00:01 -00:00:01 1 -00:00:00.990000 -00:00:01 -00:00:01 1 -00:00:00.900000 -00:00:01 -00:00:01 1 -00:00:00.100000 -00:00:01 -00:00:01 1 00:00:00.000000 00:00:00 00:00:00 1 00:00:00.000000 00:00:00 00:00:00 1 00:00:00.100000 00:00:00 00:00:00 1 00:00:00.900000 00:00:00 00:00:00 1 00:00:00.990000 00:00:00 00:00:00 1 00:00:00.999000 00:00:00 00:00:00 1 00:00:00.999900 00:00:00 00:00:00 1 00:00:00.999990 00:00:00 00:00:00 1 00:00:00.999999 00:00:00 00:00:00 1 23:59:59.000000 23:59:59 23:59:59 1 23:59:59.100000 23:59:59 23:59:59 1 23:59:59.900000 23:59:59 23:59:59 1 23:59:59.990000 23:59:59 23:59:59 1 23:59:59.999000 23:59:59 23:59:59 1 23:59:59.999900 23:59:59 23:59:59 1 23:59:59.999990 23:59:59 23:59:59 1 23:59:59.999999 23:59:59 23:59:59 1 837:59:59.000000 837:59:59 837:59:59 1 837:59:59.100000 837:59:59 837:59:59 1 837:59:59.900000 837:59:59 837:59:59 1 837:59:59.990000 837:59:59 837:59:59 1 837:59:59.999000 837:59:59 837:59:59 1 837:59:59.999900 837:59:59 837:59:59 1 837:59:59.999990 837:59:59 837:59:59 1 837:59:59.999999 837:59:59 837:59:59 1 838:59:59.000000 838:59:59 838:59:59 1 838:59:59.100000 838:59:59 838:59:59 1 838:59:59.900000 838:59:59 838:59:59 1 838:59:59.990000 838:59:59 838:59:59 1 838:59:59.999000 838:59:59 838:59:59 1 838:59:59.999900 838:59:59 838:59:59 1 838:59:59.999990 838:59:59 838:59:59 1 838:59:59.999999 838:59:59 838:59:59 1 SELECT a, CEILING(a), CEILING_SP(a), CEILING(a)=CEILING_SP(a) FROM t1 ORDER BY a; a CEILING(a) CEILING_SP(a) CEILING(a)=CEILING_SP(a) -838:59:59.999999 -838:59:59 -838:59:59 1 -838:59:59.999990 -838:59:59 -838:59:59 1 -838:59:59.999900 -838:59:59 -838:59:59 1 -838:59:59.999000 -838:59:59 -838:59:59 1 -838:59:59.990000 -838:59:59 -838:59:59 1 -838:59:59.900000 -838:59:59 -838:59:59 1 -838:59:59.100000 -838:59:59 -838:59:59 1 -838:59:59.000000 -838:59:59 -838:59:59 1 -837:59:59.999999 -837:59:59 -837:59:59 1 -837:59:59.999990 -837:59:59 -837:59:59 1 -837:59:59.999900 -837:59:59 -837:59:59 1 -837:59:59.999000 -837:59:59 -837:59:59 1 -837:59:59.990000 -837:59:59 -837:59:59 1 -837:59:59.900000 -837:59:59 -837:59:59 1 -837:59:59.100000 -837:59:59 -837:59:59 1 -837:59:59.000000 -837:59:59 -837:59:59 1 -23:59:59.999999 -23:59:59 -23:59:59 1 -23:59:59.999990 -23:59:59 -23:59:59 1 -23:59:59.999900 -23:59:59 -23:59:59 1 -23:59:59.999000 -23:59:59 -23:59:59 1 -23:59:59.990000 -23:59:59 -23:59:59 1 -23:59:59.900000 -23:59:59 -23:59:59 1 -23:59:59.100000 -23:59:59 -23:59:59 1 -23:59:59.000000 -23:59:59 -23:59:59 1 -00:00:00.999999 00:00:00 00:00:00 1 -00:00:00.999990 00:00:00 00:00:00 1 -00:00:00.999900 00:00:00 00:00:00 1 -00:00:00.999000 00:00:00 00:00:00 1 -00:00:00.990000 00:00:00 00:00:00 1 -00:00:00.900000 00:00:00 00:00:00 1 -00:00:00.100000 00:00:00 00:00:00 1 00:00:00.000000 00:00:00 00:00:00 1 00:00:00.000000 00:00:00 00:00:00 1 00:00:00.100000 00:00:01 00:00:01 1 00:00:00.900000 00:00:01 00:00:01 1 00:00:00.990000 00:00:01 00:00:01 1 00:00:00.999000 00:00:01 00:00:01 1 00:00:00.999900 00:00:01 00:00:01 1 00:00:00.999990 00:00:01 00:00:01 1 00:00:00.999999 00:00:01 00:00:01 1 23:59:59.000000 23:59:59 23:59:59 1 23:59:59.100000 24:00:00 24:00:00 1 23:59:59.900000 24:00:00 24:00:00 1 23:59:59.990000 24:00:00 24:00:00 1 23:59:59.999000 24:00:00 24:00:00 1 23:59:59.999900 24:00:00 24:00:00 1 23:59:59.999990 24:00:00 24:00:00 1 23:59:59.999999 24:00:00 24:00:00 1 837:59:59.000000 837:59:59 837:59:59 1 837:59:59.100000 838:00:00 838:00:00 1 837:59:59.900000 838:00:00 838:00:00 1 837:59:59.990000 838:00:00 838:00:00 1 837:59:59.999000 838:00:00 838:00:00 1 837:59:59.999900 838:00:00 838:00:00 1 837:59:59.999990 838:00:00 838:00:00 1 837:59:59.999999 838:00:00 838:00:00 1 838:59:59.000000 838:59:59 838:59:59 1 838:59:59.100000 838:59:59 838:59:59 1 838:59:59.900000 838:59:59 838:59:59 1 838:59:59.990000 838:59:59 838:59:59 1 838:59:59.999000 838:59:59 838:59:59 1 838:59:59.999900 838:59:59 838:59:59 1 838:59:59.999990 838:59:59 838:59:59 1 838:59:59.999999 838:59:59 838:59:59 1 DROP FUNCTION FLOOR_SP; DROP FUNCTION CEILING_SP; DROP TABLE t1; # # MDEV-29924 Assertion `(((nr) % (1LL << 24)) % (int) log_10_int[6 - dec]) == 0' failed in my_time_packed_to_binary on SELECT when using TIME field # create table t1 (c decimal(3,1),d time(6)); insert into t1 values (null,0.1),(null,0.1), (0.1,0.2); select c from t1 where c