github.com/matrixorigin/matrixone@v0.7.0/test/distributed/cases/function/func_datetime_date_add.result (about) 1 SELECT CAST('2006-09-26' AS DATE) + INTERVAL 1 DAY; 2 CAST('2006-09-26' AS DATE) + INTERVAL 1 DAY 3 2006-09-27 4 SELECT CAST('2006-09-26' AS DATE) + INTERVAL 1 MONTH; 5 CAST('2006-09-26' AS DATE) + INTERVAL 1 MONTH 6 2006-10-26 7 SELECT CAST('2006-09-26' AS DATE) + INTERVAL 1 YEAR; 8 CAST('2006-09-26' AS DATE) + INTERVAL 1 YEAR 9 2007-09-26 10 SELECT CAST('2006-09-26' AS DATE) + INTERVAL 1 WEEK; 11 CAST('2006-09-26' AS DATE) + INTERVAL 1 WEEK 12 2006-10-03 13 create table t1 (a int, b varchar(10)); 14 insert into t1 values (1, '2001-01-01'),(2, '2002-02-02'); 15 select '2007-01-01' + interval a day from t1; 16 '2007-01-01' + interval a day 17 2007-01-02 00:00:00 18 2007-01-03 00:00:00 19 select b + interval a day from t1; 20 b + interval a day 21 2001-01-02 00:00:00 22 2002-02-04 00:00:00 23 drop table t1; 24 SELECT ADDDATE(DATE'2021-01-01', INTERVAL 1 DAY); 25 ADDDATE(DATE'2021-01-01', INTERVAL 1 DAY) 26 2021-01-02 27 SELECT ADDDATE(DATE'2021-01-01', INTERVAL 1 HOUR); 28 ADDDATE(DATE'2021-01-01', INTERVAL 1 HOUR) 29 2021-01-01 01:00:00 30 SELECT ADDDATE(TIMESTAMP'2021-01-01 00:00:00', INTERVAL 1 DAY); 31 ADDDATE(TIMESTAMP'2021-01-01 00:00:00', INTERVAL 1 DAY) 32 2021-01-02 00:00:00 33 SELECT ADDDATE(TIMESTAMP'2021-01-01 00:00:00', INTERVAL 1 HOUR); 34 ADDDATE(TIMESTAMP'2021-01-01 00:00:00', INTERVAL 1 HOUR) 35 2021-01-01 01:00:00 36 SELECT '2021-01-01 00:00:00' + INTERVAL '1' DAY AS is_tomorrow, DATE(ts) 37 FROM (SELECT ADDDATE(DATE'2000-01-01', INTERVAL 1 DAY) AS ts) AS dt; 38 is_tomorrow DATE(ts) 39 2021-01-02 00:00:00 2000-01-02 40 SELECT ADDDATE(TIME'00:00:00', INTERVAL 1 HOUR); 41 adddate(time(00:00:00), interval(1, hour)) 42 01:00:00 43 SELECT ADDDATE('2021-01-01', INTERVAL 1 DAY); 44 ADDDATE('2021-01-01', INTERVAL 1 DAY) 45 2021-01-02 00:00:00 46 SELECT ADDDATE('2021-01-01', INTERVAL 1 HOUR); 47 ADDDATE('2021-01-01', INTERVAL 1 HOUR) 48 2021-01-01 01:00:00 49 SELECT ADDDATE('2021-01-01 00:00:00', INTERVAL 1 DAY); 50 ADDDATE('2021-01-01 00:00:00', INTERVAL 1 DAY) 51 2021-01-02 00:00:00 52 SELECT ADDDATE('2021-01-01 00:00:00', INTERVAL 1 HOUR); 53 ADDDATE('2021-01-01 00:00:00', INTERVAL 1 HOUR) 54 2021-01-01 01:00:00 55 SELECT ADDDATE('00:00:00', INTERVAL 1 DAY); 56 invalid input: invalid datatime value 00:00:00 57 SELECT ADDDATE('00:00:00', INTERVAL 1 HOUR); 58 invalid input: invalid datatime value 00:00:00 59 select date_add("1997-12-31 23:59:59",INTERVAL 1 SECOND); 60 date_add("1997-12-31 23:59:59",INTERVAL 1 SECOND) 61 1998-01-01 00:00:00 62 select date_add("1997-12-31 23:59:59",INTERVAL 1 MINUTE); 63 date_add("1997-12-31 23:59:59",INTERVAL 1 MINUTE) 64 1998-01-01 00:00:59 65 select date_add("1997-12-31 23:59:59",INTERVAL 1 HOUR); 66 date_add("1997-12-31 23:59:59",INTERVAL 1 HOUR) 67 1998-01-01 00:59:59 68 select date_add("1997-12-31 23:59:59",INTERVAL 1 DAY); 69 date_add("1997-12-31 23:59:59",INTERVAL 1 DAY) 70 1998-01-01 23:59:59 71 select date_add("1997-12-31 23:59:59",INTERVAL 1 MONTH); 72 date_add("1997-12-31 23:59:59",INTERVAL 1 MONTH) 73 1998-01-31 23:59:59 74 select date_add("1997-12-31 23:59:59",INTERVAL 1 YEAR); 75 date_add("1997-12-31 23:59:59",INTERVAL 1 YEAR) 76 1998-12-31 23:59:59 77 select date_add("1997-12-31 23:59:59",INTERVAL "1:1" MINUTE_SECOND); 78 date_add("1997-12-31 23:59:59",INTERVAL "1:1" MINUTE_SECOND) 79 1998-01-01 00:01:00 80 select date_add("1997-12-31 23:59:59",INTERVAL "1:1" HOUR_MINUTE); 81 date_add("1997-12-31 23:59:59",INTERVAL "1:1" HOUR_MINUTE) 82 1998-01-01 01:00:59 83 select date_add("1997-12-31 23:59:59",INTERVAL "1:1" DAY_HOUR); 84 date_add("1997-12-31 23:59:59",INTERVAL "1:1" DAY_HOUR) 85 1998-01-02 00:59:59 86 select date_add("1997-12-31 23:59:59",INTERVAL "1 1" YEAR_MONTH); 87 date_add("1997-12-31 23:59:59",INTERVAL "1 1" YEAR_MONTH) 88 1999-01-31 23:59:59 89 select date_add("1997-12-31 23:59:59",INTERVAL "1:1:1" HOUR_SECOND); 90 date_add("1997-12-31 23:59:59",INTERVAL "1:1:1" HOUR_SECOND) 91 1998-01-01 01:01:00 92 select date_add("1997-12-31 23:59:59",INTERVAL "1 1:1" DAY_MINUTE); 93 date_add("1997-12-31 23:59:59",INTERVAL "1 1:1" DAY_MINUTE) 94 1998-01-02 01:00:59 95 select date_add("1997-12-31 23:59:59",INTERVAL "1 1:1:1" DAY_SECOND); 96 date_add("1997-12-31 23:59:59",INTERVAL "1 1:1:1" DAY_SECOND) 97 1998-01-02 01:01:00 98 select date_add("1997-12-31 23:59:59",INTERVAL 100000 SECOND); 99 date_add("1997-12-31 23:59:59",INTERVAL 100000 SECOND) 100 1998-01-02 03:46:39 101 select date_add("1997-12-31 23:59:59",INTERVAL -100000 MINUTE); 102 date_add("1997-12-31 23:59:59",INTERVAL -100000 MINUTE) 103 1997-10-23 13:19:59 104 select date_add("1997-12-31 23:59:59",INTERVAL 100000 HOUR); 105 date_add("1997-12-31 23:59:59",INTERVAL 100000 HOUR) 106 2009-05-29 15:59:59 107 select date_add("1997-12-31 23:59:59",INTERVAL -100000 DAY); 108 date_add("1997-12-31 23:59:59",INTERVAL -100000 DAY) 109 1724-03-17 23:59:59 110 select date_add("1997-12-31 23:59:59",INTERVAL 100000 MONTH); 111 Data truncation: data out of range: data type datetime, 112 select date_add("1997-12-31 23:59:59",INTERVAL -100000 YEAR); 113 Data truncation: data out of range: data type datetime, 114 select date_add("1997-12-31 23:59:59",INTERVAL "10000:1" MINUTE_SECOND); 115 date_add("1997-12-31 23:59:59",INTERVAL "10000:1" MINUTE_SECOND) 116 1998-01-07 22:40:00 117 select date_add("1997-12-31 23:59:59",INTERVAL "-10000:1" HOUR_MINUTE); 118 date_add("1997-12-31 23:59:59",INTERVAL "-10000:1" HOUR_MINUTE) 119 1996-11-10 07:58:59 120 select date_add("1997-12-31 23:59:59",INTERVAL "10000:1" DAY_HOUR); 121 date_add("1997-12-31 23:59:59",INTERVAL "10000:1" DAY_HOUR) 122 2025-05-19 00:59:59 123 select date_add("1997-12-31 23:59:59",INTERVAL "-100 1" YEAR_MONTH); 124 date_add("1997-12-31 23:59:59",INTERVAL "-100 1" YEAR_MONTH) 125 1897-11-30 23:59:59 126 select date_add("1997-12-31 23:59:59",INTERVAL "10000:99:99" HOUR_SECOND); 127 date_add("1997-12-31 23:59:59",INTERVAL "10000:99:99" HOUR_SECOND) 128 1999-02-21 17:40:38 129 select date_add("1997-12-31 23:59:59",INTERVAL " -10000 99:99" DAY_MINUTE); 130 date_add("1997-12-31 23:59:59",INTERVAL " -10000 99:99" DAY_MINUTE) 131 1970-08-11 19:20:59 132 select date_add("1997-12-31 23:59:59",INTERVAL "10000 99:99:99" DAY_SECOND); 133 date_add("1997-12-31 23:59:59",INTERVAL "10000 99:99:99" DAY_SECOND) 134 2025-05-23 04:40:38 135 select date_add("1997-12-31",INTERVAL 1 SECOND); 136 date_add("1997-12-31",INTERVAL 1 SECOND) 137 1997-12-31 00:00:01 138 select date_add("1997-12-31",INTERVAL 1 DAY); 139 date_add("1997-12-31",INTERVAL 1 DAY) 140 1998-01-01 00:00:00 141 select date_add(NULL,INTERVAL 100000 SECOND); 142 date_add(NULL,INTERVAL 100000 SECOND) 143 null 144 select date_add("1997-12-31 23:59:59",INTERVAL NULL SECOND); 145 date_add("1997-12-31 23:59:59",INTERVAL NULL SECOND) 146 null 147 select date_add("1997-12-31 23:59:59",INTERVAL NULL MINUTE_SECOND); 148 date_add("1997-12-31 23:59:59",INTERVAL NULL MINUTE_SECOND) 149 null 150 select date_add("9999-12-31 23:59:59",INTERVAL 1 SECOND); 151 Data truncation: data out of range: data type datetime, 152 select date_add('1998-01-30',Interval 1 month); 153 date_add('1998-01-30',Interval 1 month) 154 1998-02-28 00:00:00 155 select date_add('1998-01-30',Interval '2:1' year_month); 156 date_add('1998-01-30',Interval '2:1' year_month) 157 2000-02-29 00:00:00 158 select date_add('1996-02-29',Interval '1' year); 159 date_add('1996-02-29',Interval '1' year) 160 1997-02-28 00:00:00 161 select date_add("1997-12-31",INTERVAL 1 SECOND); 162 date_add("1997-12-31",INTERVAL 1 SECOND) 163 1997-12-31 00:00:01 164 select date_add("1997-12-31",INTERVAL "1 1" YEAR_MONTH); 165 date_add("1997-12-31",INTERVAL "1 1" YEAR_MONTH) 166 1999-01-31 00:00:00 167 SELECT DATE_ADD(to_date('9999-12-30 23:59:00','%Y-%m-%d %H:%i:%s'), INTERVAL 1 MINUTE); 168 FUNCTION func_datetime_date_add.to_date does not exist 169 SELECT extract(week from DATE_ADD('2020-12-30 23:59:00', INTERVAL 1 year)); 170 extract(week from DATE_ADD('2020-12-30 23:59:00', INTERVAL 1 year)) 171 52 172 CREATE TABLE t1 (datetime datetime, timestamp timestamp, date date); 173 INSERT INTO t1 values ("2001-01-02 03:04:05", "2002-01-02 03:04:05", "2003-01-02"); 174 select date_add(datetime, INTERVAL 1 SECOND) from t1; 175 date_add(datetime, INTERVAL 1 SECOND) 176 2001-01-02 03:04:06 177 select date_add(datetime, INTERVAL 1 YEAR) from t1; 178 date_add(datetime, INTERVAL 1 YEAR) 179 2002-01-02 03:04:05 180 select date_add(date,INTERVAL 1 SECOND) from t1; 181 date_add(date,INTERVAL 1 SECOND) 182 2003-01-02 00:00:01 183 select date_add(date,INTERVAL 1 MINUTE) from t1; 184 date_add(date,INTERVAL 1 MINUTE) 185 2003-01-02 00:01:00 186 select date_add(date,INTERVAL 1 HOUR) from t1; 187 date_add(date,INTERVAL 1 HOUR) 188 2003-01-02 01:00:00 189 select date_add(date,INTERVAL 1 DAY) from t1; 190 date_add(date,INTERVAL 1 DAY) 191 2003-01-03 192 select date_add(date,INTERVAL 1 MONTH) from t1; 193 date_add(date,INTERVAL 1 MONTH) 194 2003-02-02 195 select date_add(date,INTERVAL 1 YEAR) from t1; 196 date_add(date,INTERVAL 1 YEAR) 197 2004-01-02 198 select date_add(date,INTERVAL "1:1" MINUTE_SECOND) from t1; 199 date_add(date,INTERVAL "1:1" MINUTE_SECOND) 200 2003-01-02 00:01:01 201 select date_add(date,INTERVAL "1:1" HOUR_MINUTE) from t1; 202 date_add(date,INTERVAL "1:1" HOUR_MINUTE) 203 2003-01-02 01:01:00 204 select date_add(date,INTERVAL "1:1" DAY_HOUR) from t1; 205 date_add(date,INTERVAL "1:1" DAY_HOUR) 206 2003-01-03 01:00:00 207 select date_add(date,INTERVAL "1 1" YEAR_MONTH) from t1; 208 date_add(date,INTERVAL "1 1" YEAR_MONTH) 209 2004-02-02 210 select date_add(date,INTERVAL "1:1:1" HOUR_SECOND) from t1; 211 date_add(date,INTERVAL "1:1:1" HOUR_SECOND) 212 2003-01-02 01:01:01 213 select date_add(date,INTERVAL "1 1:1" DAY_MINUTE) from t1; 214 date_add(date,INTERVAL "1 1:1" DAY_MINUTE) 215 2003-01-03 01:01:00 216 select date_add(date,INTERVAL "1 1:1:1" DAY_SECOND) from t1; 217 date_add(date,INTERVAL "1 1:1:1" DAY_SECOND) 218 2003-01-03 01:01:01 219 select date_add(date,INTERVAL "1" WEEK) from t1; 220 date_add(date,INTERVAL "1" WEEK) 221 2003-01-09 222 select date_add(date,INTERVAL "1" QUARTER) from t1; 223 date_add(date,INTERVAL "1" QUARTER) 224 2003-04-02 225 drop table t1; 226 select DATE_ADD('20071108181000', INTERVAL 1 DAY); 227 DATE_ADD('20071108181000', INTERVAL 1 DAY) 228 2007-11-09 18:10:00 229 select DATE_ADD(20071108181000, INTERVAL 1 DAY); 230 DATE_ADD(20071108181000, INTERVAL 1 DAY) 231 2007-11-09 18:10:00 232 select DATE_ADD('20071108', INTERVAL 1 DAY); 233 DATE_ADD('20071108', INTERVAL 1 DAY) 234 2007-11-09 00:00:00 235 select DATE_ADD(20071108, INTERVAL 1 DAY); 236 DATE_ADD(20071108, INTERVAL 1 DAY) 237 2007-11-09 00:00:00 238 select date_add('1000-01-01 00:00:00', interval '1.03:02:01.05' day_microsecond); 239 date_add('1000-01-01 00:00:00', interval '1.03:02:01.05' day_microsecond) 240 1000-01-02 03:02:01.050000000 241 select date_add('1000-01-01 00:00:00', interval '1.02' day_microsecond); 242 internal error: conv intervaltype has jagged array input 243 select date_add("0199-12-31 23:59:59",INTERVAL 2 SECOND); 244 date_add("0199-12-31 23:59:59",INTERVAL 2 SECOND) 245 0200-01-01 00:00:01 246 select date_add("2001-01-01 23:59:59",INTERVAL -2000 YEAR); 247 date_add("2001-01-01 23:59:59",INTERVAL -2000 YEAR) 248 0001-01-01 23:59:59 249 SELECT 250 date_add('1995-01-05', INTERVAL '9223372036854775807-02' YEAR_MONTH) as result; 251 invalid input: interval type, bad value '-10' 252 SELECT 253 date_add('1995-01-05', INTERVAL '9223372036854775808-02' YEAR_MONTH) as result; 254 invalid input: invalid time interval value '9223372036854775808-02' 255 SELECT 256 date_add('1995-01-05', INTERVAL '9223372036854775808-02' DAY) as result; 257 invalid input: invalid time interval value '9223372036854775808-02' 258 SELECT 259 date_add('1995-01-05', INTERVAL '9223372036854775808-02' WEEK) as result; 260 invalid input: invalid time interval value '9223372036854775808-02' 261 SELECT 262 date_add('1995-01-05', INTERVAL '9223372036854775808-02' SECOND) as result; 263 invalid input: invalid time interval value '9223372036854775808-02' 264 SELECT 265 date_add('1995-01-05', INTERVAL '9223372036854775700-02' YEAR_MONTH) as result; 266 invalid input: interval type, bad value '-1294' 267 SELECT 268 date_add('1995-01-05', INTERVAL 9223372036854775806 SECOND) as result; 269 invalid argument interval, bad value 9223372036854775806 270 SELECT 271 date_add('1995-01-05', INTERVAL 9223372036854775806 MINUTE) as result; 272 invalid argument interval, bad value 9223372036854775806 273 SELECT 274 date_add('1995-01-05', INTERVAL 9223372036854775806 HOUR) as result; 275 invalid argument interval, bad value 9223372036854775806 276 SELECT 277 date_add('1995-01-05', INTERVAL -9223372036854775806 SECOND) as result; 278 invalid argument interval, bad value -9223372036854775806 279 SELECT 280 date_add('1995-01-05', INTERVAL -9223372036854775806 MINUTE) as result; 281 invalid argument interval, bad value -9223372036854775806 282 SELECT 283 date_add('1995-01-05', INTERVAL -9223372036854775806 HOUR) as result; 284 invalid argument interval, bad value -9223372036854775806 285 select date_add("2001-01-01 23:59:59",null); 286 date_add(2001-01-01 23:59:59, null) 287 NULL 288 select date_add(null, null); 289 date_add(null, null) 290 NULL 291 drop table if exists t1; 292 create table t1 (a int, b date); 293 insert into t1 values(1, "2010-10-30"), (2, NULL); 294 select * from t1; 295 a b 296 1 2010-10-30 297 2 null 298 select date_add(b, interval 1 day) from t1; 299 date_add(b, interval 1 day) 300 2010-10-31 301 null 302 drop table t1; 303 create table t1 (a date); 304 insert into t1 select (DATE_ADD('20071108', INTERVAL 1 DAY)); 305 insert into t1 select (date_add('1998-01-30',Interval 1 month)); 306 select distinct a from t1; 307 a 308 2007-11-09 309 1998-02-28 310 drop table t1; 311 drop table if exists t1; 312 create table t1(a INT, b date); 313 insert into t1 values(1, "2012-10-12"),(2, "2004-04-24"),(3, "2008-12-04"),(4, "2012-03-23"); 314 select * from t1 where date_add(b, INTERVAL 1 day)="2012-10-13"; 315 a b 316 1 2012-10-12 317 drop table t1; 318 drop table if exists t1; 319 drop table if exists t2; 320 create table t1(a INT, b date); 321 create table t2(a INT, b date); 322 insert into t1 values(1, "2012-10-11"),(2, "2004-04-24"),(3, "2008-12-04"),(4, "2012-03-23"); 323 insert into t2 values(1, "2011-10-12"),(2, "1994-10-04"),(3, "2018-06-04"),(4, "2012-10-12"); 324 SELECT t1.a, t2.a FROM t1 JOIN t2 ON (date_add(t1.b, INTERVAL 1 day) = date_add(t2.b, INTERVAL 1 year)); 325 a a 326 1 1 327 drop table t1; 328 drop table t2; 329 drop table if exists t1; 330 create table t1(a INT, b datetime); 331 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"); 332 select b from t1 group by b having (date_add(t1.b, INTERVAL 1 day)-date_add(t1.b, INTERVAL 1 hour))>10; 333 b 334 2017-06-15 09:34:21 335 2019-06-25 10:12:21 336 2019-06-25 18:20:49 337 drop table t1; 338 SELECT 339 DATE_SUB('2007-08-03', INTERVAL 1 MINUTE) AS field_str1, 340 DATE_SUB('2007-08-03 17:33:00', INTERVAL 1 MINUTE) AS field1_str2, 341 DATE_SUB(DATE('2007-08-03'), INTERVAL 1 DAY) AS field_date, 342 DATE_SUB(CAST('2007-08-03 17:33:00' AS DATETIME), INTERVAL 1 MINUTE) AS field_datetime; 343 field_str1 field1_str2 field_date field_datetime 344 2007-08-02 23:59:00 2007-08-03 17:32:00 2007-08-02 2007-08-03 17:32:00 345 SELECT 346 OCT(DATE_SUB('2007-08-03', INTERVAL 1 MINUTE)) AS field_str1, 347 OCT(DATE_SUB('2007-08-03 17:33:00', INTERVAL 1 MINUTE)) AS field1_str2, 348 OCT(DATE_SUB(DATE('2007-08-03'), INTERVAL 1 DAY)) AS field_date, 349 OCT(DATE_SUB(CAST('2007-08-03 17:33:00' AS DATETIME), INTERVAL 1 MINUTE)) AS field_datetime; 350 invalid argument function oct, bad value [DATETIME] 351 select date_sub("1998-01-01 00:00:00.000001",INTERVAL "1 1:1:1.000002" DAY_MICROSECOND); 352 date_sub("1998-01-01 00:00:00.000001",INTERVAL "1 1:1:1.000002" DAY_MICROSECOND) 353 1997-12-30 22:58:58.999999000 354 select date_sub("1998-01-01 00:00:00.000001",INTERVAL "1:1:1.000002" HOUR_MICROSECOND); 355 date_sub("1998-01-01 00:00:00.000001",INTERVAL "1:1:1.000002" HOUR_MICROSECOND) 356 1997-12-31 22:58:58.999999000 357 select date_sub("1998-01-01 00:00:00.000001",INTERVAL "1:1.000002" MINUTE_MICROSECOND); 358 date_sub("1998-01-01 00:00:00.000001",INTERVAL "1:1.000002" MINUTE_MICROSECOND) 359 1997-12-31 23:58:58.999999000 360 select date_sub("1998-01-01 00:00:00.000001",INTERVAL "1.000002" SECOND_MICROSECOND); 361 date_sub("1998-01-01 00:00:00.000001",INTERVAL "1.000002" SECOND_MICROSECOND) 362 1997-12-31 23:59:58.999999000 363 select date_sub("1998-01-01 00:00:00.000001",INTERVAL "000002" MICROSECOND); 364 date_sub("1998-01-01 00:00:00.000001",INTERVAL "000002" MICROSECOND) 365 1997-12-31 23:59:59.999999000 366 select date_sub("1998-01-01 00:00:00",INTERVAL 1 SECOND); 367 date_sub("1998-01-01 00:00:00",INTERVAL 1 SECOND) 368 1997-12-31 23:59:59 369 select date_sub("1998-01-01 00:00:00",INTERVAL 1 MINUTE); 370 date_sub("1998-01-01 00:00:00",INTERVAL 1 MINUTE) 371 1997-12-31 23:59:00 372 select date_sub("1998-01-01 00:00:00",INTERVAL 1 HOUR); 373 date_sub("1998-01-01 00:00:00",INTERVAL 1 HOUR) 374 1997-12-31 23:00:00 375 select date_sub("1998-01-01 00:00:00",INTERVAL 1 DAY); 376 date_sub("1998-01-01 00:00:00",INTERVAL 1 DAY) 377 1997-12-31 00:00:00 378 select date_sub("1998-01-01 00:00:00",INTERVAL 1 MONTH); 379 date_sub("1998-01-01 00:00:00",INTERVAL 1 MONTH) 380 1997-12-01 00:00:00 381 select date_sub("1998-01-01 00:00:00",INTERVAL 1 YEAR); 382 date_sub("1998-01-01 00:00:00",INTERVAL 1 YEAR) 383 1997-01-01 00:00:00 384 select date_sub("1998-01-01 00:00:00",INTERVAL "1:1" MINUTE_SECOND); 385 date_sub("1998-01-01 00:00:00",INTERVAL "1:1" MINUTE_SECOND) 386 1997-12-31 23:58:59 387 select date_sub("1998-01-01 00:00:00",INTERVAL "1:1" HOUR_MINUTE); 388 date_sub("1998-01-01 00:00:00",INTERVAL "1:1" HOUR_MINUTE) 389 1997-12-31 22:59:00 390 select date_sub("1998-01-01 00:00:00",INTERVAL "1:1" DAY_HOUR); 391 date_sub("1998-01-01 00:00:00",INTERVAL "1:1" DAY_HOUR) 392 1997-12-30 23:00:00 393 select date_sub("1998-01-01 00:00:00",INTERVAL "1 1" YEAR_MONTH); 394 date_sub("1998-01-01 00:00:00",INTERVAL "1 1" YEAR_MONTH) 395 1996-12-01 00:00:00 396 select date_sub("1998-01-01 00:00:00",INTERVAL "1:1:1" HOUR_SECOND); 397 date_sub("1998-01-01 00:00:00",INTERVAL "1:1:1" HOUR_SECOND) 398 1997-12-31 22:58:59 399 select date_sub("1998-01-01 00:00:00",INTERVAL "1 1:1" DAY_MINUTE); 400 date_sub("1998-01-01 00:00:00",INTERVAL "1 1:1" DAY_MINUTE) 401 1997-12-30 22:59:00 402 select date_sub("1998-01-01 00:00:00",INTERVAL "1 1:1:1" DAY_SECOND); 403 date_sub("1998-01-01 00:00:00",INTERVAL "1 1:1:1" DAY_SECOND) 404 1997-12-30 22:58:59 405 select date_sub("0050-01-01 00:00:01",INTERVAL 2 SECOND); 406 date_sub("0050-01-01 00:00:01",INTERVAL 2 SECOND) 407 0049-12-31 23:59:59 408 select date_sub("0199-01-01 00:00:01",INTERVAL 2 SECOND); 409 date_sub("0199-01-01 00:00:01",INTERVAL 2 SECOND) 410 0198-12-31 23:59:59 411 select date_add("0199-12-31 23:59:59",INTERVAL 2 SECOND); 412 date_add("0199-12-31 23:59:59",INTERVAL 2 SECOND) 413 0200-01-01 00:00:01 414 select date_sub("0200-01-01 00:00:01",INTERVAL 2 SECOND); 415 date_sub("0200-01-01 00:00:01",INTERVAL 2 SECOND) 416 0199-12-31 23:59:59 417 select date_sub("0200-01-01 00:00:01",INTERVAL 1 SECOND); 418 date_sub("0200-01-01 00:00:01",INTERVAL 1 SECOND) 419 0200-01-01 00:00:00 420 select date_sub("0200-01-01 00:00:01",INTERVAL 2 SECOND); 421 date_sub("0200-01-01 00:00:01",INTERVAL 2 SECOND) 422 0199-12-31 23:59:59 423 select date_add("2001-01-01 23:59:59",INTERVAL -2000 YEAR); 424 date_add("2001-01-01 23:59:59",INTERVAL -2000 YEAR) 425 0001-01-01 23:59:59 426 select date_sub("50-01-01 00:00:01",INTERVAL 2 SECOND); 427 date_sub("50-01-01 00:00:01",INTERVAL 2 SECOND) 428 2049-12-31 23:59:59 429 select date_sub("90-01-01 00:00:01",INTERVAL 2 SECOND); 430 date_sub("90-01-01 00:00:01",INTERVAL 2 SECOND) 431 1989-12-31 23:59:59 432 select date_sub("0069-01-01 00:00:01",INTERVAL 2 SECOND); 433 date_sub("0069-01-01 00:00:01",INTERVAL 2 SECOND) 434 0068-12-31 23:59:59 435 select date_sub("0169-01-01 00:00:01",INTERVAL 2 SECOND); 436 date_sub("0169-01-01 00:00:01",INTERVAL 2 SECOND) 437 0168-12-31 23:59:59 438 select DATE_SUB(NOW(), INTERVAL 9999 YEAR); 439 Data truncation: data out of range: data type timestamp, 440 CREATE TABLE t1 (a DATETIME(6)); 441 INSERT INTO t1 VALUES ('1000-01-01 01:00:00.000000'); 442 INSERT INTO t1 VALUES ('1000-01-01 01:00:00.000001'); 443 INSERT INTO t1 VALUES ('2001-01-01 00:00:00.000000'); 444 INSERT INTO t1 VALUES ('2001-01-01 00:00:00.000001'); 445 INSERT INTO t1 VALUES ('2001-01-01 01:00:00.000000'); 446 INSERT INTO t1 VALUES ('2001-01-01 01:00:00.000001'); 447 SELECT DATE_SUB(a, INTERVAL 1 MINUTE) FROM t1 ORDER BY a; 448 DATE_SUB(a, INTERVAL 1 MINUTE) 449 1000-01-01 00:59:00 450 1000-01-01 00:59:00.000001000 451 2000-12-31 23:59:00 452 2000-12-31 23:59:00.000001000 453 2001-01-01 00:59:00 454 2001-01-01 00:59:00.000001000 455 SELECT a, DATE_SUB(a, INTERVAL 1.1 SECOND) FROM t1 ORDER BY a; 456 a DATE_SUB(a, INTERVAL 1.1 SECOND) 457 1000-01-01 01:00:00 1000-01-01 00:59:59 458 1000-01-01 01:00:00.000001000 1000-01-01 00:59:59.000001000 459 2001-01-01 00:00:00 2000-12-31 23:59:59 460 2001-01-01 00:00:00.000001000 2000-12-31 23:59:59.000001000 461 2001-01-01 01:00:00 2001-01-01 00:59:59 462 2001-01-01 01:00:00.000001000 2001-01-01 00:59:59.000001000 463 SELECT a, DATE_SUB(a, INTERVAL 1.000009 SECOND) FROM t1 ORDER BY a; 464 a DATE_SUB(a, INTERVAL 1.000009 SECOND) 465 1000-01-01 01:00:00 1000-01-01 00:59:59 466 1000-01-01 01:00:00.000001000 1000-01-01 00:59:59.000001000 467 2001-01-01 00:00:00 2000-12-31 23:59:59 468 2001-01-01 00:00:00.000001000 2000-12-31 23:59:59.000001000 469 2001-01-01 01:00:00 2001-01-01 00:59:59 470 2001-01-01 01:00:00.000001000 2001-01-01 00:59:59.000001000 471 SELECT a, DATE_SUB(a, INTERVAL -0.1 SECOND) FROM t1 ORDER BY a; 472 a DATE_SUB(a, INTERVAL -0.1 SECOND) 473 1000-01-01 01:00:00 1000-01-01 01:00:00 474 1000-01-01 01:00:00.000001000 1000-01-01 01:00:00.000001000 475 2001-01-01 00:00:00 2001-01-01 00:00:00 476 2001-01-01 00:00:00.000001000 2001-01-01 00:00:00.000001000 477 2001-01-01 01:00:00 2001-01-01 01:00:00 478 2001-01-01 01:00:00.000001000 2001-01-01 01:00:00.000001000 479 SELECT DATE_SUB(CAST(a AS DATETIME), INTERVAL 1 MINUTE) FROM t1 ORDER BY a; 480 DATE_SUB(CAST(a AS DATETIME), INTERVAL 1 MINUTE) 481 1000-01-01 00:59:00 482 1000-01-01 00:59:00 483 2000-12-31 23:59:00 484 2000-12-31 23:59:00 485 2001-01-01 00:59:00 486 2001-01-01 00:59:00 487 SELECT DATE_SUB(CAST(a AS DATETIME), INTERVAL 1 MICROSECOND) FROM t1 ORDER BY a; 488 DATE_SUB(CAST(a AS DATETIME), INTERVAL 1 MICROSECOND) 489 1000-01-01 00:59:59.999999000 490 1000-01-01 01:00:00 491 2000-12-31 23:59:59.999999000 492 2001-01-01 00:00:00 493 2001-01-01 00:59:59.999999000 494 2001-01-01 01:00:00 495 drop table t1; 496 CREATE TABLE `t2` ( 497 `DATE_ADD(a, INTERVAL 1 SECOND)` datetime(6) DEFAULT NULL, 498 `DATE_SUB(a, INTERVAL 1 SECOND)` datetime(6) DEFAULT NULL, 499 `DATE_ADD(CAST(a AS DATETIME), INTERVAL 1 SECOND)` datetime DEFAULT NULL, 500 `DATE_SUB(CAST(a AS DATETIME), INTERVAL 1 SECOND)` datetime DEFAULT NULL, 501 `DATE_ADD(CAST(a AS DATETIME), INTERVAL 1 MICROSECOND)` datetime(6) DEFAULT NULL, 502 `DATE_SUB(CAST(a AS DATETIME), INTERVAL 1 MICROSECOND)` datetime(6) DEFAULT NULL 503 ); 504 DROP TABLE t2;