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