github.com/matrixorigin/matrixone@v1.2.0/test/distributed/cases/function/func_datetime_timestampdiff.result (about) 1 SELECT TIMESTAMPDIFF( MICROSECOND, '2017-12-01 12:15:12','2018-01-01 7:18:20'); 2 timestampdiff(time_stamp_unit(microsecond), 2017-12-01 12:15:12, 2018-01-01 7:18:20) 3 2660588000000 4 SELECT TIMESTAMPDIFF( SECOND, '2017-12-01 12:15:12','2018-01-01 7:18:20'); 5 timestampdiff(time_stamp_unit(second), 2017-12-01 12:15:12, 2018-01-01 7:18:20) 6 2660588 7 SELECT TIMESTAMPDIFF( MINUTE, '2017-12-01 12:15:12','2018-01-01 7:18:20'); 8 timestampdiff(time_stamp_unit(minute), 2017-12-01 12:15:12, 2018-01-01 7:18:20) 9 44343 10 SELECT TIMESTAMPDIFF( HOUR, '2017-12-01 12:15:12','2018-01-01 7:18:20'); 11 timestampdiff(time_stamp_unit(hour), 2017-12-01 12:15:12, 2018-01-01 7:18:20) 12 739 13 SELECT TIMESTAMPDIFF( DAY, '2017-12-01 12:15:12','2018-01-01 7:18:20'); 14 timestampdiff(time_stamp_unit(day), 2017-12-01 12:15:12, 2018-01-01 7:18:20) 15 30 16 SELECT TIMESTAMPDIFF( WEEK, '2018-01-01 12:15:12','2018-01-08 12:15:12'); 17 timestampdiff(time_stamp_unit(week), 2018-01-01 12:15:12, 2018-01-08 12:15:12) 18 1 19 SELECT TIMESTAMPDIFF( MONTH, '2017-11-01 12:15:12','2018-01-01 12:15:12'); 20 timestampdiff(time_stamp_unit(month), 2017-11-01 12:15:12, 2018-01-01 12:15:12) 21 2 22 SELECT TIMESTAMPDIFF( QUARTER, '2017-01-01 12:15:12','2018-01-01 12:15:12'); 23 timestampdiff(time_stamp_unit(quarter), 2017-01-01 12:15:12, 2018-01-01 12:15:12) 24 4 25 SELECT TIMESTAMPDIFF( YEAR, '2017-01-01 12:15:12','2018-01-01 12:15:12'); 26 timestampdiff(time_stamp_unit(year), 2017-01-01 12:15:12, 2018-01-01 12:15:12) 27 1 28 SELECT TIMESTAMPDIFF( MICROSECOND, '2018-01-01 7:18:20','2017-12-01 12:15:12'); 29 timestampdiff(time_stamp_unit(microsecond), 2018-01-01 7:18:20, 2017-12-01 12:15:12) 30 -2660588000000 31 SELECT TIMESTAMPDIFF( SECOND, '2018-01-01 7:18:20', '2017-12-01 12:15:12'); 32 timestampdiff(time_stamp_unit(second), 2018-01-01 7:18:20, 2017-12-01 12:15:12) 33 -2660588 34 SELECT TIMESTAMPDIFF( MINUTE, '2018-01-01 7:18:20','2017-12-01 12:15:12'); 35 timestampdiff(time_stamp_unit(minute), 2018-01-01 7:18:20, 2017-12-01 12:15:12) 36 -44343 37 SELECT TIMESTAMPDIFF( HOUR, '2018-01-01 7:18:20','2017-12-01 12:15:12'); 38 timestampdiff(time_stamp_unit(hour), 2018-01-01 7:18:20, 2017-12-01 12:15:12) 39 -739 40 SELECT TIMESTAMPDIFF( DAY, '2018-01-01 7:18:20', '2017-12-01 12:15:12'); 41 timestampdiff(time_stamp_unit(day), 2018-01-01 7:18:20, 2017-12-01 12:15:12) 42 -30 43 SELECT TIMESTAMPDIFF( WEEK, '2018-01-08 12:15:12', '2018-01-01 12:15:12'); 44 timestampdiff(time_stamp_unit(week), 2018-01-08 12:15:12, 2018-01-01 12:15:12) 45 -1 46 SELECT TIMESTAMPDIFF( MONTH, '2018-01-01 12:15:12', '2017-11-01 12:15:12'); 47 timestampdiff(time_stamp_unit(month), 2018-01-01 12:15:12, 2017-11-01 12:15:12) 48 -2 49 SELECT TIMESTAMPDIFF( QUARTER, '2018-01-01 12:15:12', '2017-01-01 12:15:12'); 50 timestampdiff(time_stamp_unit(quarter), 2018-01-01 12:15:12, 2017-01-01 12:15:12) 51 -4 52 SELECT TIMESTAMPDIFF( YEAR, '2018-01-01 12:15:12', '2017-01-01 12:15:12'); 53 timestampdiff(time_stamp_unit(year), 2018-01-01 12:15:12, 2017-01-01 12:15:12) 54 -1 55 SELECT TIMESTAMPDIFF( MICROSECOND, NULL,'2017-12-01 12:15:12'); 56 timestampdiff(time_stamp_unit(microsecond), null, 2017-12-01 12:15:12) 57 null 58 SELECT TIMESTAMPDIFF( SECOND, '2018-01-01 7:18:20', NULL); 59 timestampdiff(time_stamp_unit(second), 2018-01-01 7:18:20, null) 60 null 61 SELECT TIMESTAMPDIFF( HOUR, NULL,'2017-12-01 12:15:12'); 62 timestampdiff(time_stamp_unit(hour), null, 2017-12-01 12:15:12) 63 null 64 SELECT TIMESTAMPDIFF( DAY, '2018-01-01 7:18:20', NULL); 65 timestampdiff(time_stamp_unit(day), 2018-01-01 7:18:20, null) 66 null 67 SELECT TIMESTAMPDIFF( MONTH, NULL, '2017-11-01 12:15:12'); 68 timestampdiff(time_stamp_unit(month), null, 2017-11-01 12:15:12) 69 null 70 SELECT TIMESTAMPDIFF( QUARTER, '2018-01-01 12:15:12', NULL); 71 timestampdiff(time_stamp_unit(quarter), 2018-01-01 12:15:12, null) 72 null 73 SELECT TIMESTAMPDIFF( MONTH, '2018-01-01 12:15:12', '2019-11-01 12:15:12'); 74 timestampdiff(time_stamp_unit(month), 2018-01-01 12:15:12, 2019-11-01 12:15:12) 75 22 76 SELECT TIMESTAMPDIFF( MONTH, '2018-01-01 12:15:12', '2019-10-01 12:15:12'); 77 timestampdiff(time_stamp_unit(month), 2018-01-01 12:15:12, 2019-10-01 12:15:12) 78 21 79 SELECT TIMESTAMPDIFF( MONTH, '2018-01-01 12:15:12', '2020-10-01 12:15:12'); 80 timestampdiff(time_stamp_unit(month), 2018-01-01 12:15:12, 2020-10-01 12:15:12) 81 33 82 SELECT TIMESTAMPDIFF( MONTH, '2018-01-01 12:15:12', '2021-11-01 12:15:12'); 83 timestampdiff(time_stamp_unit(month), 2018-01-01 12:15:12, 2021-11-01 12:15:12) 84 46 85 SELECT TIMESTAMPDIFF( MONTH, '2018-01-01 12:15:12', '2022-01-01 12:15:12'); 86 timestampdiff(time_stamp_unit(month), 2018-01-01 12:15:12, 2022-01-01 12:15:12) 87 48 88 SELECT TIMESTAMPDIFF( QUARTER, '2018-01-01 12:15:12', '2019-11-01 12:15:12'); 89 timestampdiff(time_stamp_unit(quarter), 2018-01-01 12:15:12, 2019-11-01 12:15:12) 90 7 91 SELECT TIMESTAMPDIFF( QUARTER, '2018-01-01 12:15:12', '2019-10-01 12:15:12'); 92 timestampdiff(time_stamp_unit(quarter), 2018-01-01 12:15:12, 2019-10-01 12:15:12) 93 7 94 SELECT TIMESTAMPDIFF( QUARTER, '2018-01-01 12:15:12', '2020-10-01 12:15:12'); 95 timestampdiff(time_stamp_unit(quarter), 2018-01-01 12:15:12, 2020-10-01 12:15:12) 96 11 97 SELECT TIMESTAMPDIFF( QUARTER, '2018-01-01 12:15:12', '2021-11-01 12:15:12'); 98 timestampdiff(time_stamp_unit(quarter), 2018-01-01 12:15:12, 2021-11-01 12:15:12) 99 15 100 SELECT TIMESTAMPDIFF( QUARTER, '2018-01-01 12:15:12', '2022-01-01 12:15:12'); 101 timestampdiff(time_stamp_unit(quarter), 2018-01-01 12:15:12, 2022-01-01 12:15:12) 102 16 103 SELECT TIMESTAMPDIFF( YEAR, '2018-01-01 12:15:12', '2019-11-01 12:15:12'); 104 timestampdiff(time_stamp_unit(year), 2018-01-01 12:15:12, 2019-11-01 12:15:12) 105 1 106 SELECT TIMESTAMPDIFF( YEAR, '2018-01-01 12:15:12', '2019-10-01 12:15:12'); 107 timestampdiff(time_stamp_unit(year), 2018-01-01 12:15:12, 2019-10-01 12:15:12) 108 1 109 SELECT TIMESTAMPDIFF( YEAR, '2018-01-01 12:15:12', '2020-10-01 12:15:12'); 110 timestampdiff(time_stamp_unit(year), 2018-01-01 12:15:12, 2020-10-01 12:15:12) 111 2 112 SELECT TIMESTAMPDIFF( YEAR, '2018-01-01 12:15:12', '2021-11-01 12:15:12'); 113 timestampdiff(time_stamp_unit(year), 2018-01-01 12:15:12, 2021-11-01 12:15:12) 114 3 115 SELECT TIMESTAMPDIFF( YEAR, '2018-01-01 12:15:12', '2022-01-01 12:15:12'); 116 timestampdiff(time_stamp_unit(year), 2018-01-01 12:15:12, 2022-01-01 12:15:12) 117 4 118 SELECT TIMESTAMPDIFF( MONTH, '2019-11-01 12:15:12', '2018-01-01 12:15:12'); 119 timestampdiff(time_stamp_unit(month), 2019-11-01 12:15:12, 2018-01-01 12:15:12) 120 -22 121 SELECT TIMESTAMPDIFF( MONTH, '2019-10-01 12:15:12', '2018-01-01 12:15:12'); 122 timestampdiff(time_stamp_unit(month), 2019-10-01 12:15:12, 2018-01-01 12:15:12) 123 -21 124 SELECT TIMESTAMPDIFF( MONTH, '2020-10-01 12:15:12', '2018-01-01 12:15:12'); 125 timestampdiff(time_stamp_unit(month), 2020-10-01 12:15:12, 2018-01-01 12:15:12) 126 -33 127 SELECT TIMESTAMPDIFF( MONTH, '2021-11-01 12:15:12', '2018-01-01 12:15:12'); 128 timestampdiff(time_stamp_unit(month), 2021-11-01 12:15:12, 2018-01-01 12:15:12) 129 -46 130 SELECT TIMESTAMPDIFF( MONTH, '2022-01-01 12:15:12', '2018-01-01 12:15:12'); 131 timestampdiff(time_stamp_unit(month), 2022-01-01 12:15:12, 2018-01-01 12:15:12) 132 -48 133 SELECT TIMESTAMPDIFF( QUARTER, '2019-11-01 12:15:12', '2018-01-01 12:15:12'); 134 timestampdiff(time_stamp_unit(quarter), 2019-11-01 12:15:12, 2018-01-01 12:15:12) 135 -7 136 SELECT TIMESTAMPDIFF( QUARTER, '2019-10-01 12:15:12', '2018-01-01 12:15:12'); 137 timestampdiff(time_stamp_unit(quarter), 2019-10-01 12:15:12, 2018-01-01 12:15:12) 138 -7 139 SELECT TIMESTAMPDIFF( QUARTER, '2020-10-01 12:15:12', '2018-01-01 12:15:12'); 140 timestampdiff(time_stamp_unit(quarter), 2020-10-01 12:15:12, 2018-01-01 12:15:12) 141 -11 142 SELECT TIMESTAMPDIFF( QUARTER, '2021-11-01 12:15:12', '2018-01-01 12:15:12'); 143 timestampdiff(time_stamp_unit(quarter), 2021-11-01 12:15:12, 2018-01-01 12:15:12) 144 -15 145 SELECT TIMESTAMPDIFF( QUARTER, '2022-01-01 12:15:12', '2018-01-01 12:15:12'); 146 timestampdiff(time_stamp_unit(quarter), 2022-01-01 12:15:12, 2018-01-01 12:15:12) 147 -16 148 SELECT TIMESTAMPDIFF( YEAR, '2019-11-01 12:15:12', '2018-01-01 12:15:12'); 149 timestampdiff(time_stamp_unit(year), 2019-11-01 12:15:12, 2018-01-01 12:15:12) 150 -1 151 SELECT TIMESTAMPDIFF( YEAR, '2019-10-01 12:15:12', '2018-01-01 12:15:12'); 152 timestampdiff(time_stamp_unit(year), 2019-10-01 12:15:12, 2018-01-01 12:15:12) 153 -1 154 SELECT TIMESTAMPDIFF( YEAR, '2020-10-01 12:15:12', '2018-01-01 12:15:12'); 155 timestampdiff(time_stamp_unit(year), 2020-10-01 12:15:12, 2018-01-01 12:15:12) 156 -2 157 SELECT TIMESTAMPDIFF( YEAR, '2021-11-01 12:15:12', '2018-01-01 12:15:12'); 158 timestampdiff(time_stamp_unit(year), 2021-11-01 12:15:12, 2018-01-01 12:15:12) 159 -3 160 SELECT TIMESTAMPDIFF( YEAR, '2022-01-01 12:15:12', '2018-01-01 12:15:12'); 161 timestampdiff(time_stamp_unit(year), 2022-01-01 12:15:12, 2018-01-01 12:15:12) 162 -4 163 drop table if exists t1; 164 create table t1(a date, b date); 165 insert into t1 values('2019-11-01 12:15:12', '2018-01-01 12:15:12'); 166 insert into t1 values('2019-10-01 12:15:12', '2018-01-01 12:15:12'); 167 insert into t1 values('2020-10-01 12:15:12', '2018-01-01 12:15:12'); 168 insert into t1 values('2021-11-01 12:15:12', '2018-01-01 12:15:12'); 169 insert into t1 values('2022-01-01 12:15:12', '2018-01-01 12:15:12'); 170 insert into t1 values('2018-01-01 12:15:12', '2019-11-01 12:15:12'); 171 insert into t1 values( '2018-01-01 12:15:12', '2019-10-01 12:15:12'); 172 insert into t1 values( '2018-01-01 12:15:12', '2020-10-01 12:15:12'); 173 insert into t1 values( '2018-01-01 12:15:12', '2021-11-01 12:15:12'); 174 insert into t1 values( '2018-01-01 12:15:12', '2022-01-01 12:15:12'); 175 SELECT a, b, TIMESTAMPDIFF(MICROSECOND, a, b) from t1; 176 a b timestampdiff(time_stamp_unit(microsecond), a, b) 177 2019-11-01 2018-01-01 -57801600000000 178 2019-10-01 2018-01-01 -55123200000000 179 2020-10-01 2018-01-01 -86745600000000 180 2021-11-01 2018-01-01 -120960000000000 181 2022-01-01 2018-01-01 -126230400000000 182 2018-01-01 2019-11-01 57801600000000 183 2018-01-01 2019-10-01 55123200000000 184 2018-01-01 2020-10-01 86745600000000 185 2018-01-01 2021-11-01 120960000000000 186 2018-01-01 2022-01-01 126230400000000 187 drop table t1; 188 drop table if exists t1; 189 create table t1(a date, b date); 190 insert into t1 values('2019-11-01 12:15:12', '2018-01-01 12:15:12'); 191 insert into t1 values('2019-10-01 12:15:12', '2018-01-01 12:15:12'); 192 insert into t1 values('2020-10-01 12:15:12', '2018-01-01 12:15:12'); 193 insert into t1 values('2021-11-01 12:15:12', '2018-01-01 12:15:12'); 194 insert into t1 values('2022-01-01 12:15:12', '2018-01-01 12:15:12'); 195 insert into t1 values('2018-01-01 12:15:12', '2019-11-01 12:15:12'); 196 insert into t1 values( '2018-01-01 12:15:12', '2019-10-01 12:15:12'); 197 insert into t1 values( '2018-01-01 12:15:12', '2020-10-01 12:15:12'); 198 insert into t1 values( '2018-01-01 12:15:12', '2021-11-01 12:15:12'); 199 insert into t1 values( '2018-01-01 12:15:12', '2022-01-01 12:15:12'); 200 SELECT a, b, TIMESTAMPDIFF(SECOND, a, b) from t1; 201 a b timestampdiff(time_stamp_unit(second), a, b) 202 2019-11-01 2018-01-01 -57801600 203 2019-10-01 2018-01-01 -55123200 204 2020-10-01 2018-01-01 -86745600 205 2021-11-01 2018-01-01 -120960000 206 2022-01-01 2018-01-01 -126230400 207 2018-01-01 2019-11-01 57801600 208 2018-01-01 2019-10-01 55123200 209 2018-01-01 2020-10-01 86745600 210 2018-01-01 2021-11-01 120960000 211 2018-01-01 2022-01-01 126230400 212 drop table t1; 213 drop table if exists t1; 214 create table t1(a date, b date); 215 insert into t1 values('2019-11-01 12:15:12', '2018-01-01 12:15:12'); 216 insert into t1 values('2019-10-01 12:15:12', '2018-01-01 12:15:12'); 217 insert into t1 values('2020-10-01 12:15:12', '2018-01-01 12:15:12'); 218 insert into t1 values('2021-11-01 12:15:12', '2018-01-01 12:15:12'); 219 insert into t1 values('2022-01-01 12:15:12', '2018-01-01 12:15:12'); 220 insert into t1 values('2018-01-01 12:15:12', '2019-11-01 12:15:12'); 221 insert into t1 values( '2018-01-01 12:15:12', '2019-10-01 12:15:12'); 222 insert into t1 values( '2018-01-01 12:15:12', '2020-10-01 12:15:12'); 223 insert into t1 values( '2018-01-01 12:15:12', '2021-11-01 12:15:12'); 224 insert into t1 values( '2018-01-01 12:15:12', '2022-01-01 12:15:12'); 225 SELECT a, b, TIMESTAMPDIFF(MINUTE, a, b) from t1; 226 a b timestampdiff(time_stamp_unit(minute), a, b) 227 2019-11-01 2018-01-01 -963360 228 2019-10-01 2018-01-01 -918720 229 2020-10-01 2018-01-01 -1445760 230 2021-11-01 2018-01-01 -2016000 231 2022-01-01 2018-01-01 -2103840 232 2018-01-01 2019-11-01 963360 233 2018-01-01 2019-10-01 918720 234 2018-01-01 2020-10-01 1445760 235 2018-01-01 2021-11-01 2016000 236 2018-01-01 2022-01-01 2103840 237 drop table t1; 238 drop table if exists t1; 239 create table t1(a date, b date); 240 insert into t1 values('2019-11-01 12:15:12', '2018-01-01 12:15:12'); 241 insert into t1 values('2019-10-01 12:15:12', '2018-01-01 12:15:12'); 242 insert into t1 values('2020-10-01 12:15:12', '2018-01-01 12:15:12'); 243 insert into t1 values('2021-11-01 12:15:12', '2018-01-01 12:15:12'); 244 insert into t1 values('2022-01-01 12:15:12', '2018-01-01 12:15:12'); 245 insert into t1 values('2018-01-01 12:15:12', '2019-11-01 12:15:12'); 246 insert into t1 values( '2018-01-01 12:15:12', '2019-10-01 12:15:12'); 247 insert into t1 values( '2018-01-01 12:15:12', '2020-10-01 12:15:12'); 248 insert into t1 values( '2018-01-01 12:15:12', '2021-11-01 12:15:12'); 249 insert into t1 values( '2018-01-01 12:15:12', '2022-01-01 12:15:12'); 250 SELECT a, b, TIMESTAMPDIFF(HOUR, a, b) from t1; 251 a b timestampdiff(time_stamp_unit(hour), a, b) 252 2019-11-01 2018-01-01 -16056 253 2019-10-01 2018-01-01 -15312 254 2020-10-01 2018-01-01 -24096 255 2021-11-01 2018-01-01 -33600 256 2022-01-01 2018-01-01 -35064 257 2018-01-01 2019-11-01 16056 258 2018-01-01 2019-10-01 15312 259 2018-01-01 2020-10-01 24096 260 2018-01-01 2021-11-01 33600 261 2018-01-01 2022-01-01 35064 262 drop table t1; 263 drop table if exists t1; 264 create table t1(a date, b date); 265 insert into t1 values('2019-11-01 12:15:12', '2018-01-01 12:15:12'); 266 insert into t1 values('2019-10-01 12:15:12', '2018-01-01 12:15:12'); 267 insert into t1 values('2020-10-01 12:15:12', '2018-01-01 12:15:12'); 268 insert into t1 values('2021-11-01 12:15:12', '2018-01-01 12:15:12'); 269 insert into t1 values('2022-01-01 12:15:12', '2018-01-01 12:15:12'); 270 insert into t1 values('2018-01-01 12:15:12', '2019-11-01 12:15:12'); 271 insert into t1 values( '2018-01-01 12:15:12', '2019-10-01 12:15:12'); 272 insert into t1 values( '2018-01-01 12:15:12', '2020-10-01 12:15:12'); 273 insert into t1 values( '2018-01-01 12:15:12', '2021-11-01 12:15:12'); 274 insert into t1 values( '2018-01-01 12:15:12', '2022-01-01 12:15:12'); 275 SELECT a, b, TIMESTAMPDIFF(DAY, a, b) from t1; 276 a b timestampdiff(time_stamp_unit(day), a, b) 277 2019-11-01 2018-01-01 -669 278 2019-10-01 2018-01-01 -638 279 2020-10-01 2018-01-01 -1004 280 2021-11-01 2018-01-01 -1400 281 2022-01-01 2018-01-01 -1461 282 2018-01-01 2019-11-01 669 283 2018-01-01 2019-10-01 638 284 2018-01-01 2020-10-01 1004 285 2018-01-01 2021-11-01 1400 286 2018-01-01 2022-01-01 1461 287 drop table t1; 288 drop table if exists t1; 289 create table t1(a date, b date); 290 insert into t1 values('2019-11-01 12:15:12', '2018-01-01 12:15:12'); 291 insert into t1 values('2019-10-01 12:15:12', '2018-01-01 12:15:12'); 292 insert into t1 values('2020-10-01 12:15:12', '2018-01-01 12:15:12'); 293 insert into t1 values('2021-11-01 12:15:12', '2018-01-01 12:15:12'); 294 insert into t1 values('2022-01-01 12:15:12', '2018-01-01 12:15:12'); 295 insert into t1 values('2018-01-01 12:15:12', '2019-11-01 12:15:12'); 296 insert into t1 values( '2018-01-01 12:15:12', '2019-10-01 12:15:12'); 297 insert into t1 values( '2018-01-01 12:15:12', '2020-10-01 12:15:12'); 298 insert into t1 values( '2018-01-01 12:15:12', '2021-11-01 12:15:12'); 299 insert into t1 values( '2018-01-01 12:15:12', '2022-01-01 12:15:12'); 300 SELECT a, b, TIMESTAMPDIFF(WEEK, a, b) from t1; 301 a b timestampdiff(time_stamp_unit(week), a, b) 302 2019-11-01 2018-01-01 -95 303 2019-10-01 2018-01-01 -91 304 2020-10-01 2018-01-01 -143 305 2021-11-01 2018-01-01 -200 306 2022-01-01 2018-01-01 -208 307 2018-01-01 2019-11-01 95 308 2018-01-01 2019-10-01 91 309 2018-01-01 2020-10-01 143 310 2018-01-01 2021-11-01 200 311 2018-01-01 2022-01-01 208 312 drop table t1; 313 drop table if exists t1; 314 create table t1(a date, b date); 315 insert into t1 values('2019-11-01 12:15:12', '2018-01-01 12:15:12'); 316 insert into t1 values('2019-10-01 12:15:12', '2018-01-01 12:15:12'); 317 insert into t1 values('2020-10-01 12:15:12', '2018-01-01 12:15:12'); 318 insert into t1 values('2021-11-01 12:15:12', '2018-01-01 12:15:12'); 319 insert into t1 values('2022-01-01 12:15:12', '2018-01-01 12:15:12'); 320 insert into t1 values('2018-01-01 12:15:12', '2019-11-01 12:15:12'); 321 insert into t1 values( '2018-01-01 12:15:12', '2019-10-01 12:15:12'); 322 insert into t1 values( '2018-01-01 12:15:12', '2020-10-01 12:15:12'); 323 insert into t1 values( '2018-01-01 12:15:12', '2021-11-01 12:15:12'); 324 insert into t1 values( '2018-01-01 12:15:12', '2022-01-01 12:15:12'); 325 SELECT a, b, TIMESTAMPDIFF(MONTH, a, b) from t1; 326 a b timestampdiff(time_stamp_unit(month), a, b) 327 2019-11-01 2018-01-01 -22 328 2019-10-01 2018-01-01 -21 329 2020-10-01 2018-01-01 -33 330 2021-11-01 2018-01-01 -46 331 2022-01-01 2018-01-01 -48 332 2018-01-01 2019-11-01 22 333 2018-01-01 2019-10-01 21 334 2018-01-01 2020-10-01 33 335 2018-01-01 2021-11-01 46 336 2018-01-01 2022-01-01 48 337 drop table t1; 338 drop table if exists t1; 339 create table t1(a date, b date); 340 insert into t1 values('2019-11-01 12:15:12', '2018-01-01 12:15:12'); 341 insert into t1 values('2019-10-01 12:15:12', '2018-01-01 12:15:12'); 342 insert into t1 values('2020-10-01 12:15:12', '2018-01-01 12:15:12'); 343 insert into t1 values('2021-11-01 12:15:12', '2018-01-01 12:15:12'); 344 insert into t1 values('2022-01-01 12:15:12', '2018-01-01 12:15:12'); 345 insert into t1 values('2018-01-01 12:15:12', '2019-11-01 12:15:12'); 346 insert into t1 values( '2018-01-01 12:15:12', '2019-10-01 12:15:12'); 347 insert into t1 values( '2018-01-01 12:15:12', '2020-10-01 12:15:12'); 348 insert into t1 values( '2018-01-01 12:15:12', '2021-11-01 12:15:12'); 349 insert into t1 values( '2018-01-01 12:15:12', '2022-01-01 12:15:12'); 350 SELECT a, b, TIMESTAMPDIFF(QUARTER, a, b) from t1; 351 a b timestampdiff(time_stamp_unit(quarter), a, b) 352 2019-11-01 2018-01-01 -7 353 2019-10-01 2018-01-01 -7 354 2020-10-01 2018-01-01 -11 355 2021-11-01 2018-01-01 -15 356 2022-01-01 2018-01-01 -16 357 2018-01-01 2019-11-01 7 358 2018-01-01 2019-10-01 7 359 2018-01-01 2020-10-01 11 360 2018-01-01 2021-11-01 15 361 2018-01-01 2022-01-01 16 362 drop table t1; 363 drop table if exists t1; 364 create table t1(a date, b date); 365 insert into t1 values('2019-11-01 12:15:12', '2018-01-01 12:15:12'); 366 insert into t1 values('2019-10-01 12:15:12', '2018-01-01 12:15:12'); 367 insert into t1 values('2020-10-01 12:15:12', '2018-01-01 12:15:12'); 368 insert into t1 values('2021-11-01 12:15:12', '2018-01-01 12:15:12'); 369 insert into t1 values('2022-01-01 12:15:12', '2018-01-01 12:15:12'); 370 insert into t1 values('2018-01-01 12:15:12', '2019-11-01 12:15:12'); 371 insert into t1 values( '2018-01-01 12:15:12', '2019-10-01 12:15:12'); 372 insert into t1 values( '2018-01-01 12:15:12', '2020-10-01 12:15:12'); 373 insert into t1 values( '2018-01-01 12:15:12', '2021-11-01 12:15:12'); 374 insert into t1 values( '2018-01-01 12:15:12', '2022-01-01 12:15:12'); 375 SELECT a, b, TIMESTAMPDIFF(YEAR, a, b) from t1; 376 a b timestampdiff(time_stamp_unit(year), a, b) 377 2019-11-01 2018-01-01 -1 378 2019-10-01 2018-01-01 -1 379 2020-10-01 2018-01-01 -2 380 2021-11-01 2018-01-01 -3 381 2022-01-01 2018-01-01 -4 382 2018-01-01 2019-11-01 1 383 2018-01-01 2019-10-01 1 384 2018-01-01 2020-10-01 2 385 2018-01-01 2021-11-01 3 386 2018-01-01 2022-01-01 4 387 drop table t1; 388 drop table if exists t1; 389 create table t1(a date, b date); 390 insert into t1 values('2019-11-01 12:15:12', '2019-11-01 12:15:12'); 391 insert into t1 values('2019-10-01 12:15:12', '2019-10-01 12:15:12'); 392 insert into t1 values('2020-10-01 12:15:12', '2020-10-01 12:15:12'); 393 insert into t1 values('2021-11-01 12:15:12', '2021-11-01 12:15:12'); 394 insert into t1 values('2022-01-01 12:15:12', '2022-01-01 12:15:12'); 395 insert into t1 values('2018-01-01 12:15:12', '2018-01-01 12:15:12'); 396 insert into t1 values( '2018-01-01 12:15:12', '2018-01-01 12:15:12'); 397 insert into t1 values( '2018-01-01 12:15:12', '2018-01-01 12:15:12'); 398 insert into t1 values( '2018-01-01 12:15:12', '2018-01-01 12:15:12'); 399 insert into t1 values( '2018-01-01 12:15:12', '2018-01-01 12:15:12'); 400 SELECT a, b, TIMESTAMPDIFF(YEAR, a, b) from t1; 401 a b timestampdiff(time_stamp_unit(year), a, b) 402 2019-11-01 2019-11-01 0 403 2019-10-01 2019-10-01 0 404 2020-10-01 2020-10-01 0 405 2021-11-01 2021-11-01 0 406 2022-01-01 2022-01-01 0 407 2018-01-01 2018-01-01 0 408 2018-01-01 2018-01-01 0 409 2018-01-01 2018-01-01 0 410 2018-01-01 2018-01-01 0 411 2018-01-01 2018-01-01 0 412 drop table t1;