github.com/matrixorigin/matrixone@v1.2.0/test/distributed/cases/function/func_datetime_date_add.test (about) 1 #隐式调用 2 SELECT CAST('2006-09-26' AS DATE) + INTERVAL 1 DAY; 3 SELECT CAST('2006-09-26' AS DATE) + INTERVAL 1 MONTH; 4 SELECT CAST('2006-09-26' AS DATE) + INTERVAL 1 YEAR; 5 SELECT CAST('2006-09-26' AS DATE) + INTERVAL 1 WEEK; 6 7 8 9 create table t1 (a int, b varchar(10)); 10 insert into t1 values (1, '2001-01-01'),(2, '2002-02-02'); 11 select '2007-01-01' + interval a day from t1; 12 select b + interval a day from t1; 13 drop table t1; 14 15 16 #SELECT, 不同数据类型，EXTREME VALUE 17 18 SELECT ADDDATE(DATE'2021-01-01', INTERVAL 1 DAY); 19 SELECT ADDDATE(DATE'2021-01-01', INTERVAL 1 HOUR); 20 SELECT ADDDATE(TIMESTAMP'2021-01-01 00:00:00', INTERVAL 1 DAY); 21 SELECT ADDDATE(TIMESTAMP'2021-01-01 00:00:00', INTERVAL 1 HOUR); 22 SELECT '2021-01-01 00:00:00' + INTERVAL '1' DAY AS is_tomorrow, DATE(ts) 23 FROM (SELECT ADDDATE(DATE'2000-01-01', INTERVAL 1 DAY) AS ts) AS dt; 24 25 SELECT ADDDATE(TIMESTAMP'2023-01-01 00:00', INTERVAL 1 DAY); 26 SELECT ADDDATE(TIMESTAMP'2023-01-01 00:00', INTERVAL 1 HOUR); 27 28 SELECT ADDDATE(TIME'00:00:00', INTERVAL 1 HOUR); 29 SELECT ADDDATE('2021-01-01', INTERVAL 1 DAY); 30 SELECT ADDDATE('2021-01-01', INTERVAL 1 HOUR); 31 SELECT ADDDATE('2021-01-01 00:00:00', INTERVAL 1 DAY); 32 SELECT ADDDATE('2021-01-01 00:00:00', INTERVAL 1 HOUR); 33 SELECT ADDDATE('00:00:00', INTERVAL 1 DAY); 34 SELECT ADDDATE('00:00:00', INTERVAL 1 HOUR); 35 36 select date_add("1997-12-31 23:59:59",INTERVAL 1 SECOND); 37 select date_add("1997-12-31 23:59:59",INTERVAL 1 MINUTE); 38 select date_add("1997-12-31 23:59:59",INTERVAL 1 HOUR); 39 select date_add("1997-12-31 23:59:59",INTERVAL 1 DAY); 40 select date_add("1997-12-31 23:59:59",INTERVAL 1 MONTH); 41 select date_add("1997-12-31 23:59:59",INTERVAL 1 YEAR); 42 select date_add("1997-12-31 23:59:59",INTERVAL "1:1" MINUTE_SECOND); 43 select date_add("1997-12-31 23:59:59",INTERVAL "1:1" HOUR_MINUTE); 44 select date_add("1997-12-31 23:59:59",INTERVAL "1:1" DAY_HOUR); 45 select date_add("1997-12-31 23:59:59",INTERVAL "1 1" YEAR_MONTH); 46 select date_add("1997-12-31 23:59:59",INTERVAL "1:1:1" HOUR_SECOND); 47 select date_add("1997-12-31 23:59:59",INTERVAL "1 1:1" DAY_MINUTE); 48 select date_add("1997-12-31 23:59:59",INTERVAL "1 1:1:1" DAY_SECOND); 49 select date_add("1997-12-31 23:59:59",INTERVAL 100000 SECOND); 50 select date_add("1997-12-31 23:59:59",INTERVAL -100000 MINUTE); 51 select date_add("1997-12-31 23:59:59",INTERVAL 100000 HOUR); 52 select date_add("1997-12-31 23:59:59",INTERVAL -100000 DAY); 53 select date_add("1997-12-31 23:59:59",INTERVAL 100000 MONTH); 54 select date_add("1997-12-31 23:59:59",INTERVAL -100000 YEAR); 55 select date_add("1997-12-31 23:59:59",INTERVAL "10000:1" MINUTE_SECOND); 56 57 58 select date_add("1997-12-31 23:59:59",INTERVAL "-10000:1" HOUR_MINUTE); 59 60 61 select date_add("1997-12-31 23:59:59",INTERVAL "10000:1" DAY_HOUR); 62 63 select date_add("1997-12-31 23:59:59",INTERVAL "-100 1" YEAR_MONTH); 64 65 66 select date_add("1997-12-31 23:59:59",INTERVAL "10000:99:99" HOUR_SECOND); 67 68 69 select date_add("1997-12-31 23:59:59",INTERVAL " -10000 99:99" DAY_MINUTE); 70 71 72 select date_add("1997-12-31 23:59:59",INTERVAL "10000 99:99:99" DAY_SECOND); 73 select date_add("1997-12-31",INTERVAL 1 SECOND); 74 select date_add("1997-12-31",INTERVAL 1 DAY); 75 select date_add(NULL,INTERVAL 100000 SECOND); 76 select date_add("1997-12-31 23:59:59",INTERVAL NULL SECOND); 77 select date_add("1997-12-31 23:59:59",INTERVAL NULL MINUTE_SECOND); 78 select date_add("9999-12-31 23:59:59",INTERVAL 1 SECOND); 79 select date_add('1998-01-30',Interval 1 month); 80 select date_add('1998-01-30',Interval '2:1' year_month); 81 select date_add('1996-02-29',Interval '1' year); 82 select date_add("1997-12-31",INTERVAL 1 SECOND); 83 select date_add("1997-12-31",INTERVAL "1 1" YEAR_MONTH); 84 85 #函数嵌套 86 -- @bvt:issue#3203 87 SELECT DATE_ADD(to_date('9999-12-30 23:59:00','%Y-%m-%d %H:%i:%s'), INTERVAL 1 MINUTE); 88 -- @bvt:issue 89 90 91 SELECT extract(week from DATE_ADD('2020-12-30 23:59:00', INTERVAL 1 year)); 92 93 94 #不同数据类型 95 CREATE TABLE t1 (datetime datetime, timestamp timestamp, date date); 96 INSERT INTO t1 values ("2001-01-02 03:04:05", "2002-01-02 03:04:05", "2003-01-02"); 97 select date_add(datetime, INTERVAL 1 SECOND) from t1; 98 select date_add(datetime, INTERVAL 1 YEAR) from t1; 99 select date_add(date,INTERVAL 1 SECOND) from t1; 100 select date_add(date,INTERVAL 1 MINUTE) from t1; 101 select date_add(date,INTERVAL 1 HOUR) from t1; 102 select date_add(date,INTERVAL 1 DAY) from t1; 103 select date_add(date,INTERVAL 1 MONTH) from t1; 104 select date_add(date,INTERVAL 1 YEAR) from t1; 105 select date_add(date,INTERVAL "1:1" MINUTE_SECOND) from t1; 106 select date_add(date,INTERVAL "1:1" HOUR_MINUTE) from t1; 107 select date_add(date,INTERVAL "1:1" DAY_HOUR) from t1; 108 select date_add(date,INTERVAL "1 1" YEAR_MONTH) from t1; 109 select date_add(date,INTERVAL "1:1:1" HOUR_SECOND) from t1; 110 select date_add(date,INTERVAL "1 1:1" DAY_MINUTE) from t1; 111 select date_add(date,INTERVAL "1 1:1:1" DAY_SECOND) from t1; 112 select date_add(date,INTERVAL "1" WEEK) from t1; 113 select date_add(date,INTERVAL "1" QUARTER) from t1; 114 drop table t1; 115 116 117 #0.5 doesn't support time 118 #CREATE TABLE t1(time time); 119 #INSERT INTO t1 values ("06:07:08"); 120 #select date_add(time,INTERVAL 1 SECOND) from t1; 121 #drop table t1; 122 123 124 #EXTREME VALUE 125 select DATE_ADD('20071108181000', INTERVAL 1 DAY); 126 127 128 select DATE_ADD(20071108181000, INTERVAL 1 DAY); 129 130 select DATE_ADD('20071108', INTERVAL 1 DAY); 131 132 133 select DATE_ADD(20071108, INTERVAL 1 DAY); 134 135 select date_add('1000-01-01 00:00:00', interval '1.03:02:01.05' day_microsecond); 136 137 138 select date_add('1000-01-01 00:00:00', interval '1.02' day_microsecond); 139 140 141 select date_add("0199-12-31 23:59:59",INTERVAL 2 SECOND); 142 select date_add("2001-01-01 23:59:59",INTERVAL -2000 YEAR); 143 144 #EXTREME VALUE 145 146 147 SELECT 148 date_add('1995-01-05', INTERVAL '9223372036854775807-02' YEAR_MONTH) as result; 149 SELECT 150 date_add('1995-01-05', INTERVAL '9223372036854775808-02' YEAR_MONTH) as result; 151 SELECT 152 date_add('1995-01-05', INTERVAL '9223372036854775808-02' DAY) as result; 153 SELECT 154 date_add('1995-01-05', INTERVAL '9223372036854775808-02' WEEK) as result; 155 SELECT 156 date_add('1995-01-05', INTERVAL '9223372036854775808-02' SECOND) as result; 157 SELECT 158 date_add('1995-01-05', INTERVAL '9223372036854775700-02' YEAR_MONTH) as result; 159 160 SELECT 161 date_add('1995-01-05', INTERVAL 9223372036854775806 SECOND) as result; 162 SELECT 163 date_add('1995-01-05', INTERVAL 9223372036854775806 MINUTE) as result; 164 SELECT 165 date_add('1995-01-05', INTERVAL 9223372036854775806 HOUR) as result; 166 SELECT 167 date_add('1995-01-05', INTERVAL -9223372036854775806 SECOND) as result; 168 SELECT 169 date_add('1995-01-05', INTERVAL -9223372036854775806 MINUTE) as result; 170 SELECT 171 date_add('1995-01-05', INTERVAL -9223372036854775806 HOUR) as result; 172 173 #NULL 174 select date_add("2001-01-01 23:59:59",null); 175 select date_add(null, null); 176 drop table if exists t1; 177 create table t1 (a int, b date); 178 insert into t1 values(1, "2010-10-30"), (2, NULL); 179 select * from t1; 180 select date_add(b, interval 1 day) from t1; 181 drop table t1; 182 183 #INSERT 184 create table t1 (a date); 185 insert into t1 select (DATE_ADD('20071108', INTERVAL 1 DAY)); 186 insert into t1 select (date_add('1998-01-30',Interval 1 month)); 187 select distinct a from t1; 188 drop table t1; 189 190 #WHERE & 逻辑运算 191 192 drop table if exists t1; 193 create table t1(a INT, b date); 194 insert into t1 values(1, "2012-10-12"),(2, "2004-04-24"),(3, "2008-12-04"),(4, "2012-03-23"); 195 select * from t1 where date_add(b, INTERVAL 1 day)="2012-10-13"; 196 drop table t1; 197 198 199 #WHERE & 逻辑运算 200 drop table if exists t1; 201 drop table if exists t2; 202 create table t1(a INT, b date); 203 create table t2(a INT, b date); 204 insert into t1 values(1, "2012-10-11"),(2, "2004-04-24"),(3, "2008-12-04"),(4, "2012-03-23"); 205 insert into t2 values(1, "2011-10-12"),(2, "1994-10-04"),(3, "2018-06-04"),(4, "2012-10-12"); 206 SELECT t1.a, t2.a FROM t1 JOIN t2 ON (date_add(t1.b, INTERVAL 1 day) = date_add(t2.b, INTERVAL 1 year)); 207 drop table t1; 208 drop table t2; 209 210 211 212 #HAVING & 算术运算 213 214 215 drop table if exists t1; 216 create table t1(a INT, b datetime); 217 insert into t1 values(1, "2017-06-15 09:34:21"),(1, "2019-06-25 10:12:21"),(2, "2019-06-25 18:20:49"),(3, "2019-06-25 18:20:49"); 218 select b from t1 group by b having (date_add(t1.b, INTERVAL 1 day)-date_add(t1.b, INTERVAL 1 hour))>10; 219 drop table t1; 220 221 SELECT 222 DATE_SUB('2007-08-03', INTERVAL 1 MINUTE) AS field_str1, 223 DATE_SUB('2007-08-03 17:33:00', INTERVAL 1 MINUTE) AS field1_str2, 224 DATE_SUB(DATE('2007-08-03'), INTERVAL 1 DAY) AS field_date, 225 DATE_SUB(CAST('2007-08-03 17:33:00' AS DATETIME), INTERVAL 1 MINUTE) AS field_datetime; 226 227 SELECT 228 OCT(DATE_SUB('2007-08-03', INTERVAL 1 MINUTE)) AS field_str1, 229 OCT(DATE_SUB('2007-08-03 17:33:00', INTERVAL 1 MINUTE)) AS field1_str2, 230 OCT(DATE_SUB(DATE('2007-08-03'), INTERVAL 1 DAY)) AS field_date, 231 OCT(DATE_SUB(CAST('2007-08-03 17:33:00' AS DATETIME), INTERVAL 1 MINUTE)) AS field_datetime; 232 233 234 select date_sub("1998-01-01 00:00:00.000001",INTERVAL "1 1:1:1.000002" DAY_MICROSECOND); 235 select date_sub("1998-01-01 00:00:00.000001",INTERVAL "1:1:1.000002" HOUR_MICROSECOND); 236 select date_sub("1998-01-01 00:00:00.000001",INTERVAL "1:1.000002" MINUTE_MICROSECOND); 237 select date_sub("1998-01-01 00:00:00.000001",INTERVAL "1.000002" SECOND_MICROSECOND); 238 select date_sub("1998-01-01 00:00:00.000001",INTERVAL "000002" MICROSECOND); 239 select date_sub("1998-01-01 00:00:00",INTERVAL 1 SECOND); 240 select date_sub("1998-01-01 00:00:00",INTERVAL 1 MINUTE); 241 select date_sub("1998-01-01 00:00:00",INTERVAL 1 HOUR); 242 select date_sub("1998-01-01 00:00:00",INTERVAL 1 DAY); 243 select date_sub("1998-01-01 00:00:00",INTERVAL 1 MONTH); 244 select date_sub("1998-01-01 00:00:00",INTERVAL 1 YEAR); 245 select date_sub("1998-01-01 00:00:00",INTERVAL "1:1" MINUTE_SECOND); 246 select date_sub("1998-01-01 00:00:00",INTERVAL "1:1" HOUR_MINUTE); 247 select date_sub("1998-01-01 00:00:00",INTERVAL "1:1" DAY_HOUR); 248 select date_sub("1998-01-01 00:00:00",INTERVAL "1 1" YEAR_MONTH); 249 select date_sub("1998-01-01 00:00:00",INTERVAL "1:1:1" HOUR_SECOND); 250 select date_sub("1998-01-01 00:00:00",INTERVAL "1 1:1" DAY_MINUTE); 251 select date_sub("1998-01-01 00:00:00",INTERVAL "1 1:1:1" DAY_SECOND); 252 select date_sub("0050-01-01 00:00:01",INTERVAL 2 SECOND); 253 select date_sub("0199-01-01 00:00:01",INTERVAL 2 SECOND); 254 select date_add("0199-12-31 23:59:59",INTERVAL 2 SECOND); 255 select date_sub("0200-01-01 00:00:01",INTERVAL 2 SECOND); 256 select date_sub("0200-01-01 00:00:01",INTERVAL 1 SECOND); 257 select date_sub("0200-01-01 00:00:01",INTERVAL 2 SECOND); 258 select date_add("2001-01-01 23:59:59",INTERVAL -2000 YEAR); 259 260 -- @bvt:issue#3319 261 select date_sub("50-01-01 00:00:01",INTERVAL 2 SECOND); 262 select date_sub("90-01-01 00:00:01",INTERVAL 2 SECOND); 263 -- @bvt:issue 264 265 select date_sub("0069-01-01 00:00:01",INTERVAL 2 SECOND); 266 select date_sub("0169-01-01 00:00:01",INTERVAL 2 SECOND); 267 268 select DATE_SUB(NOW(), INTERVAL 9999 YEAR); 269 270 271 CREATE TABLE t1 (a DATETIME(6)); 272 INSERT INTO t1 VALUES ('1000-01-01 01:00:00.000000'); 273 INSERT INTO t1 VALUES ('1000-01-01 01:00:00.000001'); 274 INSERT INTO t1 VALUES ('2001-01-01 00:00:00.000000'); 275 INSERT INTO t1 VALUES ('2001-01-01 00:00:00.000001'); 276 INSERT INTO t1 VALUES ('2001-01-01 01:00:00.000000'); 277 INSERT INTO t1 VALUES ('2001-01-01 01:00:00.000001'); 278 SELECT DATE_SUB(a, INTERVAL 1 MINUTE) FROM t1 ORDER BY a; 279 SELECT a, DATE_SUB(a, INTERVAL 1.1 SECOND) FROM t1 ORDER BY a; 280 SELECT a, DATE_SUB(a, INTERVAL 1.000009 SECOND) FROM t1 ORDER BY a; 281 SELECT a, DATE_SUB(a, INTERVAL -0.1 SECOND) FROM t1 ORDER BY a; 282 SELECT DATE_SUB(CAST(a AS DATETIME), INTERVAL 1 MINUTE) FROM t1 ORDER BY a; 283 SELECT DATE_SUB(CAST(a AS DATETIME), INTERVAL 1 MICROSECOND) FROM t1 ORDER BY a; 284 drop table t1; 285 286 287 CREATE TABLE `t2` ( 288 `DATE_ADD(a, INTERVAL 1 SECOND)` datetime(6) DEFAULT NULL, 289 `DATE_SUB(a, INTERVAL 1 SECOND)` datetime(6) DEFAULT NULL, 290 `DATE_ADD(CAST(a AS DATETIME), INTERVAL 1 SECOND)` datetime DEFAULT NULL, 291 `DATE_SUB(CAST(a AS DATETIME), INTERVAL 1 SECOND)` datetime DEFAULT NULL, 292 `DATE_ADD(CAST(a AS DATETIME), INTERVAL 1 MICROSECOND)` datetime(6) DEFAULT NULL, 293 `DATE_SUB(CAST(a AS DATETIME), INTERVAL 1 MICROSECOND)` datetime(6) DEFAULT NULL 294 ); 295 DROP TABLE t2; 296 297