github.com/matrixorigin/matrixone@v0.7.0/test/distributed/cases/function/func_datetime_timediff.result (about) 1 select timediff(cast('22:22:22' as time), cast('11:11:11' as time)); 2 timediff(cast(22:22:22 as time(26)), cast(11:11:11 as time(26))) 3 11:11:11 4 select timediff(cast('22:22:22' as time), cast('-11:11:11' as time)); 5 timediff(cast(22:22:22 as time(26)), cast(-11:11:11 as time(26))) 6 33:33:33 7 select timediff(cast('-22:22:22' as time), cast('11:11:11' as time)); 8 timediff(cast(-22:22:22 as time(26)), cast(11:11:11 as time(26))) 9 -33:33:33 10 select timediff(cast('-22:22:22' as time), cast('-11:11:11' as time)); 11 timediff(cast(-22:22:22 as time(26)), cast(-11:11:11 as time(26))) 12 -11:11:11 13 select timediff(cast('11:11:11' as time) , cast('22:22:22' as time)); 14 timediff(cast(11:11:11 as time(26)), cast(22:22:22 as time(26))) 15 -11:11:11 16 select timediff(cast('11:11:11' as time) , cast('-22:22:22' as time)); 17 timediff(cast(11:11:11 as time(26)), cast(-22:22:22 as time(26))) 18 33:33:33 19 select timediff(cast('-11:11:11' as time) , cast('22:22:22' as time)); 20 timediff(cast(-11:11:11 as time(26)), cast(22:22:22 as time(26))) 21 -33:33:33 22 select timediff(cast('-11:11:11' as time) , cast('-22:22:22' as time)); 23 timediff(cast(-11:11:11 as time(26)), cast(-22:22:22 as time(26))) 24 11:11:11 25 select timediff(cast('-838:59:59' as time) , cast('838:59:59' as time)); 26 timediff(cast(-838:59:59 as time(26)), cast(838:59:59 as time(26))) 27 -1677:59:58 28 select timediff(cast('838:59:59' as time) , cast('-838:59:59' as time)); 29 timediff(cast(838:59:59 as time(26)), cast(-838:59:59 as time(26))) 30 1677:59:58 31 select timediff(cast('838:59:59' as time) , cast('838:59:59' as time)); 32 timediff(cast(838:59:59 as time(26)), cast(838:59:59 as time(26))) 33 00:00:00 34 select timediff(cast('22:22:22' as time), null); 35 timediff(cast(22:22:22 as time(26)), null) 36 null 37 select timediff(null, cast('11:11:11' as time)); 38 timediff(null, cast(11:11:11 as time(26))) 39 null 40 select timediff(null, null); 41 timediff(null, null) 42 null 43 select timediff(CAST('2017-08-08 22:22:22' as datetime), CAST('2000-01-02 11:00:00' as datetime)); 44 timediff(cast(2017-08-08 22:22:22 as datetime(26)), cast(2000-01-02 11:00:00 as datetime(26))) 45 154283:22:22 46 select timediff(CAST('2012-12-12 22:22:22' as datetime), CAST('2012-12-12 11:11:11' as datetime)); 47 timediff(cast(2012-12-12 22:22:22 as datetime(26)), cast(2012-12-12 11:11:11 as datetime(26))) 48 11:11:11 49 select timediff(CAST('2012-12-12 22:22:22' as datetime), CAST('2012-12-12 22:22:22' as datetime)); 50 timediff(cast(2012-12-12 22:22:22 as datetime(26)), cast(2012-12-12 22:22:22 as datetime(26))) 51 00:00:00 52 select timediff(CAST('2012-12-12 22:22:22' as datetime), CAST('2000-12-12 11:11:11' as datetime)); 53 timediff(cast(2012-12-12 22:22:22 as datetime(26)), cast(2000-12-12 11:11:11 as datetime(26))) 54 105203:11:11 55 select timediff(CAST('2000-12-12 11:11:11' as datetime), CAST('2012-12-12 22:22:22' as datetime)); 56 timediff(cast(2000-12-12 11:11:11 as datetime(26)), cast(2012-12-12 22:22:22 as datetime(26))) 57 -105203:11:11 58 select timediff(CAST('2012-12-12 22:22:22' as datetime), CAST('2012-10-10 11:11:11' as datetime)); 59 timediff(cast(2012-12-12 22:22:22 as datetime(26)), cast(2012-10-10 11:11:11 as datetime(26))) 60 1523:11:11 61 select timediff(CAST('2012-10-10 11:11:11' as datetime), CAST('2012-12-12 22:22:22' as datetime)); 62 timediff(cast(2012-10-10 11:11:11 as datetime(26)), cast(2012-12-12 22:22:22 as datetime(26))) 63 -1523:11:11 64 select timediff(CAST('2012-12-12 22:22:22' as datetime), CAST('2012-12-10 11:11:11' as datetime)); 65 timediff(cast(2012-12-12 22:22:22 as datetime(26)), cast(2012-12-10 11:11:11 as datetime(26))) 66 59:11:11 67 select timediff(CAST('2012-12-10 11:11:11' as datetime), CAST('2012-12-12 22:22:22' as datetime)); 68 timediff(cast(2012-12-10 11:11:11 as datetime(26)), cast(2012-12-12 22:22:22 as datetime(26))) 69 -59:11:11 70 select timediff(CAST('2012-12-10 11:11:11' as datetime), CAST('2012-12-10 11:11:11' as datetime)); 71 timediff(cast(2012-12-10 11:11:11 as datetime(26)), cast(2012-12-10 11:11:11 as datetime(26))) 72 00:00:00 73 SELECT TIMEDIFF(CAST('2012-12-12 11:11:11' AS DATETIME), NULL); 74 timediff(cast(2012-12-12 11:11:11 as datetime(26)), null) 75 null 76 SELECT TIMEDIFF(NULL, CAST('2012-12-12 11:11:11' AS DATETIME)); 77 timediff(null, cast(2012-12-12 11:11:11 as datetime(26))) 78 null 79 SELECT TIMEDIFF(NULL, NULL); 80 timediff(null, null) 81 null 82 select timediff(CAST('2017-08-08 22:22:22' as datetime), cast('11:11:11' as time)); 83 invalid input: timediff function have invalid input args type 84 select timediff(cast('11:11:11' as time), cast('2017-08-08 22:22:22' as datetime)); 85 invalid input: timediff function have invalid input args type 86 select timediff(CAST('2017-08-08 22:22:22' as datetime), 1); 87 invalid argument function timediff, bad value [DATETIME BIGINT] 88 drop table if exists t1; 89 create table t1(a INT, b time); 90 insert into t1 values(1, '22:22:22'); 91 insert into t1 values(2, '11:11:11'); 92 insert into t1 values(3, '-22:22:22'); 93 insert into t1 values(4, '-11:11:11'); 94 insert into t1 values(5, '838:59:59'); 95 insert into t1 values(6, '-838:59:59'); 96 insert into t1 values(7, '00:00:00'); 97 select a, timediff(cast('22:22:22' as time), b) from t1; 98 a timediff(cast(22:22:22 as time(26)), b) 99 1 00:00:00 100 2 11:11:11 101 3 44:44:44 102 4 33:33:33 103 5 -816:37:37 104 6 861:22:21 105 7 22:22:22 106 select a, timediff(b, cast('22:22:22' as time)) from t1; 107 a timediff(b, cast(22:22:22 as time(26))) 108 1 00:00:00 109 2 -11:11:11 110 3 -44:44:44 111 4 -33:33:33 112 5 816:37:37 113 6 -861:22:21 114 7 -22:22:22 115 drop table t1; 116 drop table if exists t2; 117 create table t2(a INT, b datetime); 118 insert into t2 values(1, '2012-12-12 23:22:22'); 119 insert into t2 values(2, '2012-12-10 11:11:11'); 120 insert into t2 values(3, '2012-12-14 11:11:11'); 121 insert into t2 values(4, '2012-12-20 11:11:11'); 122 insert into t2 values(2, '2012-10-10 11:11:11'); 123 select a, timediff(cast('2012-12-12 22:22:21' as datetime), b) from t2; 124 a timediff(cast(2012-12-12 22:22:21 as datetime(26)), b) 125 1 -1:00:01 126 2 59:11:10 127 3 -36:48:50 128 4 -180:48:50 129 2 1523:11:10 130 select a, timediff(b, cast('2012-12-12 22:22:21' as datetime)) from t2; 131 a timediff(b, cast(2012-12-12 22:22:21 as datetime(26))) 132 1 01:00:01 133 2 -59:11:10 134 3 36:48:50 135 4 180:48:50 136 2 -1523:11:10 137 drop table t2; 138 select timediff('20',NULL); 139 timediff(20, null) 140 null 141 select timediff(NULL,'24:59:09'); 142 timediff(null, 24:59:09) 143 null 144 select timediff('20','24:59:09'); 145 timediff(20, 24:59:09) 146 -24:58:49 147 select timediff('12:00','24:59:09'); 148 timediff(12:00, 24:59:09) 149 -12:59:09 150 select timediff('-838:59:59','-1122'); 151 timediff(-838:59:59, -1122) 152 -838:48:37 153 select timediff('12:00:00','24:59:09'); 154 timediff(12:00:00, 24:59:09) 155 -12:59:09 156 drop table if exists time_01; 157 create table time_01(t1 time,t2 time,t3 time); 158 insert into time_01 values("-838:59:59.0000","838:59:59.00","22:00:00"); 159 insert into time_01 values("0:00:00.0000","0","0:00"); 160 insert into time_01 values(null,NULL,null); 161 insert into time_01 values("23","1122","-1122"); 162 insert into time_01 values("101412","4","-101219"); 163 insert into time_01 values("24:59:09.932823","24:02:00.93282332424","24:20:34.00000000"); 164 insert into time_01 values("2022-09-08 12:00:01","019","23403"); 165 select timediff(t1,t2) from time_01; 166 timediff(t1, t2) 167 -1677:59:58 168 00:00:00 169 null 170 00:10:59 171 10:14:08 172 00:57:09 173 11:59:42 174 drop table time_01;