1 2let type=time; 3--source include/type_hrtime.inc 4 5create table t1 (a time(4) not null, key(a)); 6insert 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'); 7select * from t1 order by a; 8select * from t1 order by a desc; 9select min(a - interval 1 hour), max(a - interval 1 hour) from t1 where a < 0; 10drop table t1; 11 12select cast(1e-6 as time(6)); 13 14 15--echo # 16--echo # Start of 10.4 tests 17--echo # 18 19--echo # 20--echo # MDEV-20397 Support TIMESTAMP, DATETIME, TIME in ROUND() and TRUNCATE() 21--echo # 22 23CREATE TABLE t1 (a1 TIME(6), a2 TIME(6) NOT NULL); 24CREATE TABLE t2 AS SELECT 25 ROUND(a1) AS r1, 26 ROUND(a2) AS r2, 27 TRUNCATE(a1,0) AS t1, 28 TRUNCATE(a2,0) AS t2 29FROM t1; 30SHOW CREATE TABLE t2; 31DROP TABLE t2; 32DROP TABLE t1; 33 34 35CREATE TABLE t1 (a TIME(6)); 36INSERT INTO t1 VALUES 37('-838:59:59.999999'), 38('-837:59:59.999999'), 39('-23:59:59.999999'), 40('-00:59:59.999999'), 41('-00:00:59.999999'), 42('00:00:00.999999'), 43('00:00:59.999999'), 44('00:59:59.999999'), 45('23:59:59.999999'), 46('837:59:59.999999'), 47('838:59:59.999999'); 48 49 50SELECT a, TRUNCATE(a,0) FROM t1; 51SELECT a, TRUNCATE(a,1) FROM t1; 52SELECT a, TRUNCATE(a,2) FROM t1; 53SELECT a, TRUNCATE(a,3) FROM t1; 54SELECT a, TRUNCATE(a,4) FROM t1; 55SELECT a, TRUNCATE(a,5) FROM t1; 56SELECT a, TRUNCATE(a,6) FROM t1; 57SELECT a, TRUNCATE(a,7) FROM t1; 58SELECT a, TRUNCATE(a,-1) FROM t1; 59SELECT a, TRUNCATE(a,-6) FROM t1; 60 61SELECT a, ROUND(a) FROM t1; 62SELECT a, ROUND(a,0) FROM t1; 63SELECT a, ROUND(a,1) FROM t1; 64SELECT a, ROUND(a,2) FROM t1; 65SELECT a, ROUND(a,3) FROM t1; 66SELECT a, ROUND(a,4) FROM t1; 67SELECT a, ROUND(a,5) FROM t1; 68SELECT a, ROUND(a,6) FROM t1; 69SELECT a, ROUND(a,7) FROM t1; 70SELECT a, ROUND(a,-1) FROM t1; 71SELECT a, ROUND(a,-6) FROM t1; 72 73DROP TABLE t1; 74 75SET time_zone=DEFAULT; 76 77 78--echo # 79--echo # MDEV-23311 CEILING() and FLOOR() convert temporal input to numbers, unlike ROUND() and TRUNCATE() 80--echo # 81 82CREATE TABLE t1 AS SELECT 83 FLOOR(TIME'00:00:00.999999'), 84 CEILING(TIME'00:00:00.999999'); 85SHOW CREATE TABLE t1; 86DROP TABLE t1; 87 88CREATE TABLE t1 (a TIME(6)); 89 90INSERT INTO t1 VALUES ('838:59:59.999999'); 91INSERT INTO t1 VALUES ('838:59:59.99999'); 92INSERT INTO t1 VALUES ('838:59:59.9999'); 93INSERT INTO t1 VALUES ('838:59:59.999'); 94INSERT INTO t1 VALUES ('838:59:59.99'); 95INSERT INTO t1 VALUES ('838:59:59.9'); 96INSERT INTO t1 VALUES ('838:59:59.1'); 97INSERT INTO t1 VALUES ('838:59:59.0'); 98 99INSERT INTO t1 VALUES ('837:59:59.999999'); 100INSERT INTO t1 VALUES ('837:59:59.99999'); 101INSERT INTO t1 VALUES ('837:59:59.9999'); 102INSERT INTO t1 VALUES ('837:59:59.999'); 103INSERT INTO t1 VALUES ('837:59:59.99'); 104INSERT INTO t1 VALUES ('837:59:59.9'); 105INSERT INTO t1 VALUES ('837:59:59.1'); 106INSERT INTO t1 VALUES ('837:59:59.0'); 107 108INSERT INTO t1 VALUES ('23:59:59.999999'); 109INSERT INTO t1 VALUES ('23:59:59.99999'); 110INSERT INTO t1 VALUES ('23:59:59.9999'); 111INSERT INTO t1 VALUES ('23:59:59.999'); 112INSERT INTO t1 VALUES ('23:59:59.99'); 113INSERT INTO t1 VALUES ('23:59:59.9'); 114INSERT INTO t1 VALUES ('23:59:59.1'); 115INSERT INTO t1 VALUES ('23:59:59.0'); 116 117INSERT INTO t1 VALUES ('00:00:00.999999'); 118INSERT INTO t1 VALUES ('00:00:00.99999'); 119INSERT INTO t1 VALUES ('00:00:00.9999'); 120INSERT INTO t1 VALUES ('00:00:00.999'); 121INSERT INTO t1 VALUES ('00:00:00.99'); 122INSERT INTO t1 VALUES ('00:00:00.9'); 123INSERT INTO t1 VALUES ('00:00:00.1'); 124INSERT INTO t1 VALUES ('00:00:00.0'); 125 126INSERT INTO t1 VALUES ('-00:00:00.999999'); 127INSERT INTO t1 VALUES ('-00:00:00.99999'); 128INSERT INTO t1 VALUES ('-00:00:00.9999'); 129INSERT INTO t1 VALUES ('-00:00:00.999'); 130INSERT INTO t1 VALUES ('-00:00:00.99'); 131INSERT INTO t1 VALUES ('-00:00:00.9'); 132INSERT INTO t1 VALUES ('-00:00:00.1'); 133INSERT INTO t1 VALUES ('-00:00:00.0'); 134 135INSERT INTO t1 VALUES ('-23:59:59.999999'); 136INSERT INTO t1 VALUES ('-23:59:59.99999'); 137INSERT INTO t1 VALUES ('-23:59:59.9999'); 138INSERT INTO t1 VALUES ('-23:59:59.999'); 139INSERT INTO t1 VALUES ('-23:59:59.99'); 140INSERT INTO t1 VALUES ('-23:59:59.9'); 141INSERT INTO t1 VALUES ('-23:59:59.1'); 142INSERT INTO t1 VALUES ('-23:59:59.0'); 143 144INSERT INTO t1 VALUES ('-837:59:59.999999'); 145INSERT INTO t1 VALUES ('-837:59:59.99999'); 146INSERT INTO t1 VALUES ('-837:59:59.9999'); 147INSERT INTO t1 VALUES ('-837:59:59.999'); 148INSERT INTO t1 VALUES ('-837:59:59.99'); 149INSERT INTO t1 VALUES ('-837:59:59.9'); 150INSERT INTO t1 VALUES ('-837:59:59.1'); 151INSERT INTO t1 VALUES ('-837:59:59.0'); 152 153INSERT INTO t1 VALUES ('-838:59:59.999999'); 154INSERT INTO t1 VALUES ('-838:59:59.99999'); 155INSERT INTO t1 VALUES ('-838:59:59.9999'); 156INSERT INTO t1 VALUES ('-838:59:59.999'); 157INSERT INTO t1 VALUES ('-838:59:59.99'); 158INSERT INTO t1 VALUES ('-838:59:59.9'); 159INSERT INTO t1 VALUES ('-838:59:59.1'); 160INSERT INTO t1 VALUES ('-838:59:59.0'); 161 162DELIMITER $$; 163CREATE FUNCTION FLOOR_SP(a TIME(6)) RETURNS TIME 164BEGIN 165 RETURN 166 CASE 167 WHEN TRUNCATE(a,0)=TIME'838:59:59' THEN a 168 WHEN TRUNCATE(a,0)=TIME'-838:59:59' THEN a 169 WHEN a=TRUNCATE(a,0) THEN a -- no fractional digits 170 WHEN a<0 THEN TRUNCATE(a,0)-INTERVAL 1 SECOND -- negative values 171 WHEN a>0 THEN TRUNCATE(a,0) -- positive values 172 END; 173END; 174$$ 175DELIMITER ;$$ 176 177DELIMITER $$; 178CREATE FUNCTION CEILING_SP(a TIME(6)) RETURNS TIME 179BEGIN 180 RETURN 181 CASE 182 WHEN TRUNCATE(a,0)=TIME'838:59:59' THEN a 183 WHEN TRUNCATE(a,0)=TIME'-838:59:59' THEN a 184 WHEN a=TRUNCATE(a,0) THEN a -- no fractional digits 185 WHEN a<0 THEN TRUNCATE(a,0) -- negative values 186 WHEN a>0 THEN TRUNCATE(a,0)+INTERVAL 1 SECOND -- positive values 187 END; 188END; 189$$ 190DELIMITER ;$$ 191 192SELECT a, FLOOR(a), FLOOR_SP(a), FLOOR(a)=FLOOR_SP(a) FROM t1 ORDER BY a; 193SELECT a, CEILING(a), CEILING_SP(a), CEILING(a)=CEILING_SP(a) FROM t1 ORDER BY a; 194 195DROP FUNCTION FLOOR_SP; 196DROP FUNCTION CEILING_SP; 197 198DROP TABLE t1; 199