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