github.com/matrixorigin/matrixone@v1.2.0/test/distributed/cases/expression/temporal_interval.sql (about) 1 -- @suite 2 3 -- @case 4 -- @desc:test for temporal interval unit with data_add 5 -- @label:bvt 6 select date_add("1997-12-31 23:59:59.000002",INTERVAL "10000 99:99:99.999999" DAY_MICROSECOND); 7 select date_add("1997-12-31 23:59:59.000002",INTERVAL "10000:99:99.999999" HOUR_MICROSECOND); 8 select date_add("1997-12-31 23:59:59.000002",INTERVAL "10000:99.999999" MINUTE_MICROSECOND); 9 select date_add("1997-12-31 23:59:59.000002",INTERVAL "10000.999999" SECOND_MICROSECOND); 10 select date_add("1997-12-31 23:59:59.000002",INTERVAL "999999" MICROSECOND); 11 select date_add("1997-12-31 23:59:59",INTERVAL 1 SECOND); 12 select date_add("1997-12-31 23:59:59",INTERVAL 1 MINUTE); 13 select date_add("1997-12-31 23:59:59",INTERVAL 1 HOUR); 14 select date_add("1997-12-31 23:59:59",INTERVAL 1 DAY); 15 select date_add("1997-12-31 23:59:59",INTERVAL 0 DAY); 16 select date_add("1997-12-31 23:59:59",INTERVAL 1 MONTH); 17 select date_add("1997-12-31 23:59:59",INTERVAL 1 QUARTER); 18 select date_add("1997-12-31 23:59:59",INTERVAL 1 YEAR); 19 select date_add("1997-12-31 23:59:59",INTERVAL "1:1" MINUTE_SECOND); 20 select date_add("1997-12-31 23:59:59",INTERVAL "1:1" HOUR_MINUTE); 21 select date_add("1997-12-31 23:59:59",INTERVAL "1:1" DAY_HOUR); 22 select date_add("1997-12-31 23:59:59",INTERVAL "1 1" YEAR_MONTH); 23 select date_add("1997-12-31 23:59:59",INTERVAL "1:1:1" HOUR_SECOND); 24 select date_add("1997-12-31 23:59:59",INTERVAL "1 1:1" DAY_MINUTE); 25 select date_add("1997-12-31 23:59:59",INTERVAL "1 1:1:1" DAY_SECOND); 26 select date_add("1997-12-31 23:59:59",INTERVAL 100000 SECOND); 27 select date_add("1997-12-31 23:59:59",INTERVAL -100000 MINUTE); 28 select date_add("1997-12-31 23:59:59",INTERVAL 100000 HOUR); 29 select date_add("1997-12-31 23:59:59",INTERVAL -100000 DAY); 30 select date_add("1997-12-31 23:59:59",INTERVAL 100000 MONTH); 31 select date_add("1997-12-31 23:59:59",INTERVAL 100000 QUARTER); 32 select date_add("1997-12-31 23:59:59",INTERVAL -100000 YEAR); 33 select date_add("1997-12-31 23:59:59",INTERVAL "10000:1" MINUTE_SECOND); 34 select date_add("1997-12-31 23:59:59",INTERVAL "-10000:1" HOUR_MINUTE); 35 select date_add("1997-12-31 23:59:59",INTERVAL "10000:1" DAY_HOUR); 36 select date_add("1997-12-31 23:59:59",INTERVAL "-100 1" YEAR_MONTH); 37 select date_add("1997-12-31 23:59:59",INTERVAL "10000:99:99" HOUR_SECOND); 38 select date_add("1997-12-31 23:59:59",INTERVAL " -10000 99:99" DAY_MINUTE); 39 select date_add("1997-12-31 23:59:59",INTERVAL "10000 99:99:99" DAY_SECOND); 40 select date_add("1997-12-31",INTERVAL 1 SECOND); 41 select date_add("1997-12-31",INTERVAL 1 DAY); 42 select date_add(NULL,INTERVAL 100000 SECOND); 43 select date_add("1997-12-31 23:59:59",INTERVAL NULL SECOND); 44 select date_add("1997-12-31 23:59:59",INTERVAL NULL MINUTE_SECOND); 45 select date_add("9999-12-31 23:59:59",INTERVAL 1 SECOND); 46 select date_sub("0000-00-00 00:00:00",INTERVAL 1 SECOND); 47 select date_add('1998-01-30',Interval 1 month); 48 select date_add('1998-01-30',Interval '2:1' year_month); 49 select date_add('1996-02-29',Interval '1' year); 50 select date_add('1996-02-29',Interval q year); 51 select date_add("1997-12-31 23:59:59",INTERVAL 1.5 SECOND); 52 select date_add("1997-12-31 23:59:59",INTERVAL 1.5 MINUTE); 53 select date_add("1997-12-31 23:59:59",INTERVAL 1.5 HOUR); 54 select date_add("1997-12-31 23:59:59",INTERVAL 1.5 DAY); 55 select date_add("1997-12-31 23:59:59",INTERVAL 1.5 ABC); 56 57 -- @case 58 -- @desc:test for temporal interval unit with data_sub 59 -- @label:bvt 60 61 select date_sub("1998-01-01 00:00:00",INTERVAL 1 SECOND); 62 select date_sub("1998-01-01 00:00:00",INTERVAL 1 MINUTE); 63 select date_sub("1998-01-01 00:00:00",INTERVAL 1 HOUR); 64 select date_sub("1998-01-01 00:00:00",INTERVAL 1 DAY); 65 select date_sub("1998-01-01 00:00:00",INTERVAL 1 MONTH); 66 select date_sub("1998-01-01 00:00:00",INTERVAL 1 QUARTER); 67 select date_sub("1998-01-01 00:00:00",INTERVAL 1 YEAR); 68 select date_sub("1998-01-01 00:00:00",INTERVAL 100000 SECOND); 69 select date_sub("1998-01-01 00:00:009",INTERVAL -100000 MINUTE); 70 select date_sub("1998-01-01 00:00:00",INTERVAL 100000 HOUR); 71 select date_sub("1998-01-01 00:00:00",INTERVAL 0 HOUR); 72 select date_sub("1998-01-01 00:00:00",INTERVAL -100000 DAY); 73 select date_sub("1998-01-01 00:00:00",INTERVAL 100000 MONTH); 74 select date_sub("1998-01-01 00:00:00",INTERVAL 100000 QUARTER); 75 select date_sub("1998-01-01 00:00:00",INTERVAL -100000 YEAR); 76 select date_sub("1998-01-01 00:00:00",INTERVAL "1:1" MINUTE_SECOND); 77 select date_sub("1998-01-01 00:00:00",INTERVAL "1:1" HOUR_MINUTE); 78 select date_sub("1998-01-01 00:00:00",INTERVAL "1:1" DAY_HOUR); 79 select date_sub("1998-01-01 00:00:00",INTERVAL "1 1" YEAR_MONTH); 80 select date_sub("1998-01-01 00:00:00",INTERVAL "1:1:1" HOUR_SECOND); 81 select date_sub("1998-01-01 00:00:00",INTERVAL "1 1:1" DAY_MINUTE); 82 select date_sub("1998-01-01 00:00:00",INTERVAL "1 1:1:1" DAY_SECOND); 83 select date_sub("1998-01-01 00:00:00",INTERVAL "10000:1" MINUTE_SECOND); 84 select date_sub("1998-01-01 00:00:00",INTERVAL "-10000:1" HOUR_MINUTE); 85 select date_sub("1998-01-01 00:00:00",INTERVAL "10000:1" DAY_HOUR); 86 select date_sub("1998-01-01 00:00:00",INTERVAL "-100 1" YEAR_MONTH); 87 select date_sub("1998-01-01 00:00:00",INTERVAL "10000:99:99" HOUR_SECOND); 88 select date_sub("1998-01-01 00:00:00",INTERVAL " -10000 99:99" DAY_MINUTE); 89 select date_sub("1998-01-01 00:00:00",INTERVAL "10000 99:99:99" DAY_SECOND); 90 select date_sub("1998-01-01 00:00:00.000001",INTERVAL "1 1:1:1.000002" DAY_MICROSECOND); 91 select date_sub("1998-01-01 00:00:00.000001",INTERVAL "1:1:1.000002" HOUR_MICROSECOND); 92 select date_sub("1998-01-01 00:00:00.000001",INTERVAL "1:1.000002" MINUTE_MICROSECOND); 93 select date_sub("1998-01-01 00:00:00.000001",INTERVAL "1.000002" SECOND_MICROSECOND); 94 select date_sub("1998-01-01 00:00:00.000001",INTERVAL "000002" MICROSECOND); 95 select date_sub("1998-01-01",INTERVAL 1 SECOND); 96 select date_sub("1998-01-01",INTERVAL 1 DAY); 97 select date_sub(NULL,INTERVAL 100000 SECOND); 98 select date_sub("1998-01-01 00:00:00",INTERVAL NULL SECOND); 99 select date_sub("1998-01-01 00:00:00",INTERVAL NULL MINUTE_SECOND); 100 select date_sub("1998-01-01 00:00:00",INTERVAL 1 SECOND); 101 select date_sub("0000-00-00 00:00:00",INTERVAL 1 SECOND); 102 select date_add('1998-01-30',Interval 1 month); 103 select date_sub('1998-02-01',Interval '2:1' year_month); 104 select date_sub('1996-02-29',Interval '1' year); 105 select date_add('1996-02-29',Interval a year); 106 select date_sub("1998-01-01 00:00:00",INTERVAL 1.5 SECOND); 107 select date_sub("1998-01-01 00:00:00",INTERVAL 1.5 MINUTE); 108 select date_sub("1998-01-01 00:00:00",INTERVAL 1.5 HOUR); 109 select date_sub("1998-01-01 00:00:00",INTERVAL 1.5 DAY); 110 select date_sub("1998-01-01 00:00:00",INTERVAL 1.5 MONTH); 111 select date_sub("1998-01-01 00:00:00",INTERVAL 1.5 QUARTER); 112 select date_sub("1998-01-01 00:00:00",INTERVAL 1 ABC); 113 114 select date_sub(NULL,INTERVAL 100000 SECOND); 115 select date_sub("1998-01-02",INTERVAL 31 DAY); 116 117 -- @case 118 -- @desc:test for temporal interval unit with timestamp_sub 119 -- @label:bvt 120 -- select TIMESTAMPADD(MINUTE,8820,'2012-08-24 09:00:00'); 121 122 -- @case 123 -- @desc:test for temporal interval unit with expression+- 124 -- @label:bvt 125 select "1997-12-31 23:59:59" + INTERVAL 1 SECOND; 126 select INTERVAL 1 DAY + "1997-12-31"; 127 select "1998-01-01 00:00:00" - INTERVAL 1 SECOND; 128 SELECT "1900-01-01 00:00:00" + INTERVAL 2147483648 SECOND; 129 SELECT "1900-01-01 00:00:00" + INTERVAL "1:2147483647" MINUTE_SECOND; 130 SELECT "1900-01-01 00:00:00" + INTERVAL "100000000:214748364700" MINUTE_SECOND; 131 132 SELECT "1900-01-01 00:00:00" + INTERVAL 1<<37 SECOND; 133 SELECT "1900-01-01 00:00:00" + INTERVAL 1<<31 MINUTE; 134 SELECT "1900-01-01 00:00:00" + INTERVAL 1<<20 HOUR; 135 SELECT "1900-01-01 00:00:00" + INTERVAL 1<<38 SECOND; 136 SELECT "1900-01-01 00:00:00" + INTERVAL 1<<33 MINUTE; 137 SELECT "1900-01-01 00:00:00" + INTERVAL 1<<30 HOUR; 138 139 SELECT "1900-01-01 00:00:00" + INTERVAL "1000000000:214748364700" MINUTE_SECOND; 140 141 SELECT "1997-12-31 23:59:59" + INTERVAL 1 SECOND; 142 143 -- @case 144 -- @desc:test for temporal interval with date,datetime,char,varchar column 145 -- @label:bvt 146 147 create table t1(i int,a date,b date,c datetime,d char(20),e varchar(50)); 148 insert into t1 values(1,"1997-12-31","1997-12-31","1997-12-31 23:59:59.000002","1997-12-31 23:59:59","1997-12-31 23:59:59.000002"); 149 insert into t1 values(2,"1998-01-01","1998-01-01","1998-01-01 00:00:00.000001","1998-01-01 00:00:00","1997-12-31 23:59:59.000002"); 150 insert into t1 values(3,NULL,NULL,NULL,NULL,NULL); 151 select date_add(a,INTERVAL 1 SECOND), date_add(b,INTERVAL 1 MINUTE), date_add(c,INTERVAL 1 HOUR), date_add(d,INTERVAL 1 MONTH), date_add(e,INTERVAL 1 QUARTER) from t1; 152 select date_add(a,INTERVAL 1 YEAR), date_add(b,"1:1" MINUTE_SECOND), date_add(c,INTERVAL "1:1" HOUR_MINUTE), date_add(d,INTERVAL "1:1" DAY_HOUR), date_add(e,INTERVAL "1 1" YEAR_MONTH) from t1; 153 select date_add(a,"1:1:1" HOUR_SECOND), date_add(b,"1:1:1" HOUR_SECOND), date_add(c,INTERVAL "1 1:1" DAY_MINUTE), date_add(d,INTERVAL "1 1:1:1" DAY_SECOND), date_add(e,INTERVAL "1 1" YEAR_MONTH) from t1; 154 select date_sub(a,INTERVAL 1 SECOND), date_sub(b,INTERVAL 1 MINUTE), date_sub(c,INTERVAL 1 HOUR), date_sub(d,INTERVAL 1 MONTH), date_sub(e,INTERVAL 1 QUARTER) from t1; 155 select date_sub(a,INTERVAL 1 YEAR), date_sub(b,INTERVAL "1:1" MINUTE_SECOND), date_sub(c,INTERVAL "1:1" HOUR_MINUTE), date_sub(d,INTERVAL "1:1" DAY_HOUR), date_sub(e,INTERVAL "1 1" YEAR_MONTH) from t1; 156 select date_sub(a,INTERVAL "1:1:1" HOUR_SECOND), date_sub(b,INTERVAL "1:1:1" HOUR_SECOND), date_sub(c,INTERVAL "1 1:1" DAY_MINUTE), date_sub(d,INTERVAL "1 1:1:1" DAY_SECOND), date_sub(e,INTERVAL "1 1" YEAR_MONTH) from t1; 157 select a + INTERVAL 1 SECOND,b + INTERVAL 1 MINUTE,c + INTERVAL 1 HOUR from t1; 158 select a - INTERVAL 1 SECOND,b - INTERVAL 1 MINUTE,c - INTERVAL 1 HOUR from t1; 159 select i + INTERVAL 1 SECOND from t1; 160 161 -- @case 162 -- @desc:test for temporal interval with month,weekday,week,date,dayofyear,hour,minute,second,cast 163 -- @label:bvt 164 select month(date_sub("1998-01-01 00:00:00",INTERVAL 1 SECOND)); 165 select weekday(date_sub("1998-01-01 00:00:00",INTERVAL 1 MINUTE)); 166 select date(date_sub("1998-01-01 00:00:00",INTERVAL 1 DAY)); 167 select dayofyear(date_sub("1998-01-01 00:00:00",INTERVAL 1 MONTH)); 168 169 select month(date_add("1997-12-31 23:59:59",INTERVAL "1:1" MINUTE_SECOND)); 170 171 select weekday(date_add("1997-12-31 23:59:59",INTERVAL "1:1" HOUR_MINUTE)); 172 select date(date_add("1997-12-31 23:59:59",INTERVAL "1 1" YEAR_MONTH)); 173 select dayofyear(date_add("1997-12-31 23:59:59",INTERVAL "1:1:1" HOUR_SECOND)); 174 175 select date("1997-12-31 23:59:59" + INTERVAL 1 SECOND) + INTERVAL "1:1:1" HOUR_SECOND; 176 177 178 SELECT CAST(CAST('2006-08-10 10:11:12' AS DATETIME) AS DECIMAL(20,6)); 179 SELECT CAST(CAST('2006-08-10 10:11:12' AS DATETIME) + INTERVAL 14 MICROSECOND AS DECIMAL(20,6)); 180 181 -- @case 182 -- @desc:test for temporal interval with insert,update 183 -- @label:bvt 184 drop table if exists t1; 185 drop table if exists t2; 186 create table t1(i int,a datetime,b datetime,c datetime,d char(200),e varchar(50)); 187 create table t2(i int,a datetime,b datetime,c datetime,d char(200),e varchar(50)); 188 insert into t1 select 1,"1997-12-30" + INTERVAL 1 SECOND ,"1997-12-31 23:59:59" + INTERVAL 1 MINUTE,"1997-12-31 23:59:59.000002" + INTERVAL 1 HOUR,"1997-12-31 23:59:59" + INTERVAL 1 DAY,"1997-12-31 23:59:59.000002" + INTERVAL 1 MONTH; 189 insert into t1 select 2,"1997-12-30" + INTERVAL 1 YEAR,"1997-12-31 23:59:59" + INTERVAL "1:1" MINUTE_SECOND,"1997-12-31 23:59:59.000002" + INTERVAL "1:1" DAY_HOUR,"1997-12-31 23:59:59" + INTERVAL "1 1" YEAR_MONTH,"1997-12-31 23:59:59.000002" + INTERVAL "1:1:1" HOUR_SECOND; 190 insert into t1 select 3,"1997-12-30" + INTERVAL "1:1:1" HOUR_SECOND,"1997-12-31 23:59:59" + INTERVAL "1 1:1" DAY_MINUTE,"1997-12-31 23:59:59.000002" + INTERVAL "1 1:1:1" DAY_SECOND,"1997-12-31 23:59:59","1997-12-31 23:59:59.000002"; 191 insert into t1 select 4,"1997-12-30" + INTERVAL 1 SECOND ,"1997-12-31 23:59:59" + INTERVAL 1 MINUTE,"1997-12-31 23:59:59.000002" + INTERVAL 1 HOUR,"1997-12-31 23:59:59" + INTERVAL 1 DAY,"1997-12-31 23:59:59.000002" + INTERVAL 1 MONTH; 192 insert into t1 select 5,"1997-12-30" + INTERVAL 1 YEAR,"1997-12-31 23:59:59" + INTERVAL "1:1" MINUTE_SECOND,"1997-12-31 23:59:59.000002" + INTERVAL "1:1" DAY_HOUR,"1997-12-31 23:59:59" + INTERVAL "1 1" YEAR_MONTH,"1997-12-31 23:59:59.000002" + INTERVAL "1:1:1" HOUR_SECOND; 193 insert into t1 select 6,"1997-12-30" + INTERVAL "1:1:1" HOUR_SECOND,"1997-12-31 23:59:59" + INTERVAL "1 1:1" DAY_MINUTE,"1997-12-31 23:59:59.000002" + INTERVAL "1 1:1:1" DAY_SECOND,"1997-12-31 23:59:59","1997-12-31 23:59:59.000002"; 194 195 insert into t1 values(7,"1998-01-01","1998-01-01","1998-01-01 00:00:00.000001","1998-01-01 00:00:00","1997-12-31 23:59:59.000002"); 196 insert into t1 values(8,"1998-01-01","1998-01-01","1998-01-01 00:00:00.000001","1998-01-01 00:00:00","1997-12-31 23:59:59.000002"); 197 insert into t1 values(9,"1998-01-01","1998-01-01","1998-01-01 00:00:00.000001","1998-01-01 00:00:00","1997-12-31 23:59:59.000002"); 198 insert into t1 select 10,"2010-11-12" + interval 14 microsecond,"1998-01-01 00:00:00","1998-01-01 00:00:00.000001","1998-01-01 00:00:00","1997-12-31 23:59:59.000002"; 199 insert into t1 values(11,NULL,NULL,NULL,NULL,NULL); 200 201 insert into t2 select 1,"1997-12-30" + INTERVAL 1 SECOND ,"1997-12-31 23:59:59" + INTERVAL 1 MINUTE,"1997-12-31 23:59:59.000002" + INTERVAL 1 HOUR,"1997-12-31 23:59:59" + INTERVAL 1 DAY,"1997-12-31 23:59:59.000002" + INTERVAL 1 MONTH; 202 insert into t2 select 2,"1997-12-30" + INTERVAL 1 YEAR,"1997-12-31 23:59:59" + INTERVAL "1:1" MINUTE_SECOND,"1997-12-31 23:59:59.000002" + INTERVAL "1:1" DAY_HOUR,"1997-12-31 23:59:59" + INTERVAL "1 1" YEAR_MONTH,"1997-12-31 23:59:59.000002" + INTERVAL "1:1:1" HOUR_SECOND; 203 insert into t2 select 3,"1997-12-30" + INTERVAL "1:1:1" HOUR_SECOND,"1997-12-31 23:59:59" + INTERVAL "1 1:1" DAY_MINUTE,"1997-12-31 23:59:59.000002" + INTERVAL "1 1:1:1" DAY_SECOND,"1997-12-31 23:59:59","1997-12-31 23:59:59.000002"; 204 insert into t2 select 4,"1997-12-30" + INTERVAL 1 SECOND ,"1997-12-31 23:59:59" + INTERVAL 1 MINUTE,"1997-12-31 23:59:59.000002" + INTERVAL 1 HOUR,"1997-12-31 23:59:59" + INTERVAL 1 DAY,"1997-12-31 23:59:59.000002" + INTERVAL 1 MONTH; 205 insert into t2 select 5,"1997-12-30" + INTERVAL 1 YEAR,"1997-12-31 23:59:59" + INTERVAL "1:1" MINUTE_SECOND,"1997-12-31 23:59:59.000002" + INTERVAL "1:1" DAY_HOUR,"1997-12-31 23:59:59" + INTERVAL "1 1" YEAR_MONTH,"1997-12-31 23:59:59.000002" + INTERVAL "1:1:1" HOUR_SECOND; 206 insert into t2 select 6,"1997-12-30" + INTERVAL "1:1:1" HOUR_SECOND,"1997-12-31 23:59:59" + INTERVAL "1 1:1" DAY_MINUTE,"1997-12-31 23:59:59.000002" + INTERVAL "1 1:1:1" DAY_SECOND,"1997-12-31 23:59:59","1997-12-31 23:59:59.000002"; 207 208 insert into t2 values(7,"1998-01-01","1998-01-01 00:00:00","1998-01-01 00:00:00.000001","1998-01-01 00:00:00","1997-12-31 23:59:59.000002"); 209 insert into t2 values(8,"1998-01-01","1998-01-01 00:00:00","1998-01-01 00:00:00.000001","1998-01-01 00:00:00","1997-12-31 23:59:59.000002"); 210 insert into t2 values(9,"1998-01-01","1998-01-01 00:00:00","1998-01-01 00:00:00.000001","1998-01-01 00:00:00","1997-12-31 23:59:59.000002"); 211 insert into t2 values(10,NULL,NULL,NULL,NULL,NULL); 212 213 -- @bvt:issue#3254 214 select * from t1 where a = "1997-12-29" + INTERVAL 1 DAY; 215 select * from t1 where a > "1997-12-29" + INTERVAL 1 DAY; 216 select * from t1 where (a + INTERVAL 1 DAY) > "1997-12-31"; 217 select * from t1 where (a + INTERVAL 1 DAY) <> ("1997-12-30" + INTERVAL 1 DAY); 218 -- @bvt:issue 219 220 select date_add(b,INTERVAL 1 DAY),date_add(c,INTERVAL 1 SECOND) from t1; 221 222 -- @bvt:issue#3254 223 select distinct(a) from t1 where c > "1998-01-01 00:59:59"; 224 -- @bvt:issue 225 226 select count(a),c + INTERVAL 1 DAY as c1 from t1 group by (c + INTERVAL 1 DAY) having c1 > "1998-01-01 00:59:59"; 227 228 -- @bvt:issue#3254 229 select i,c + INTERVAL 1 MINUTE from t1 where a - INTERVAL 1 SECOND > "1997-01-01 00:00:00.000001" order by c + INTERVAL 1 MINUTE DESC; 230 select i,c + INTERVAL 1 MINUTE from t1 where a - INTERVAL 1 SECOND > "1997-01-01 00:00:00.000001" order by c + INTERVAL 1 MINUTE ASC; 231 -- @bvt:issue 232 233 select t1.i,t2.i,t1.c + INTERVAL 1 MINUTE,t2.b + INTERVAL 1 YEAR from t1 join t2 where (t1.a + INTERVAL 1 DAY) = (t2.c -INTERVAL 1 DAY ); 234 235 236 select '2007-01-01' + interval i day from t2; 237 select b + interval i day from t2; 238 239 update t1 set c = c + INTERVAL 1 DAY where i > 6; 240 -- @bvt:issue#10895 241 select * from t1 where i > 6; 242 -- @bvt:issue 243 drop table if exists t1; 244 drop table if exists t2; 245 246 -- @case 247 -- @desc:test for temporal interval with between and 248 -- @label:bvt 249 drop table if exists t1; 250 CREATE TABLE t1 ( datum DATE ); 251 252 INSERT INTO t1 VALUES ( "2000-1-1" ); 253 INSERT INTO t1 VALUES ( "2000-1-2" ); 254 INSERT INTO t1 VALUES ( "2000-1-3" ); 255 INSERT INTO t1 VALUES ( "2000-1-4" ); 256 INSERT INTO t1 VALUES ( "2000-1-5" ); 257 SELECT * FROM t1 WHERE datum BETWEEN cast("2000-1-2" as date) AND cast("2000-1-4" as date); 258 SELECT * FROM t1 WHERE datum BETWEEN cast("2000-1-2" as date) AND datum - INTERVAL 100 DAY; 259 260 261 -- @case 262 -- @desc:test for temporal interval with cast 263 -- @label:bvt 264 SELECT CAST('2006-09-26' AS DATE) + INTERVAL 1 DAY; 265 SELECT CAST('2006-09-26' AS DATE) + INTERVAL 1 MONTH; 266 SELECT CAST('2006-09-26' AS DATE) + INTERVAL 1 YEAR; 267 SELECT CAST('2006-09-26' AS DATE) + INTERVAL 1 WEEK; 268 269 -- @case 270 -- @desc:test for temporal interval with invalid date turned to NULL from date_sub/date_add 271 -- @label:bvt 272 drop table if exists t1; 273 create table t1 (d date); 274 insert into t1 (d) select date_sub('2000-01-01', INTERVAL 2001 YEAR); 275 insert into t1 (d) select date_add('2000-01-01',interval 8000 year); 276 insert into t1 select date_add(NULL, INTERVAL 1 DAY); 277 insert into t1 select date_add('2000-01-04', INTERVAL NULL DAY); 278 insert into t1 (d) select date_sub('2000-01-01', INTERVAL 2001 YEAR); 279 insert into t1 (d) select date_add('2000-01-01',interval 8000 year); 280 insert into t1 select date_add(NULL, INTERVAL 1 DAY); 281 insert into t1 select date_add('2000-01-04', INTERVAL NULL DAY); 282 select * from t1; 283 drop table t1; 284 285 286 set @tt=now(); 287 -- @ignore:0 288 select @tt; 289 -- @ignore:0 290 select date_add(@tt, Interval 30 SECOND); 291 -- @ignore:0 292 select date_sub(@tt, Interval 30 SECOND);