1drop table if exists t1; 2CREATE TABLE t1 ( 3visitor_id int(10) unsigned DEFAULT '0' NOT NULL, 4group_id int(10) unsigned DEFAULT '0' NOT NULL, 5hits int(10) unsigned DEFAULT '0' NOT NULL, 6sessions int(10) unsigned DEFAULT '0' NOT NULL, 7ts timestamp, 8PRIMARY KEY (visitor_id,group_id) 9)/*! engine=MyISAM */; 10INSERT INTO t1 VALUES (465931136,7,2,2,20000318160952); 11INSERT INTO t1 VALUES (173865424,2,2,2,20000318233615); 12INSERT INTO t1 VALUES (173865424,8,2,2,20000318233615); 13INSERT INTO t1 VALUES (173865424,39,2,2,20000318233615); 14INSERT INTO t1 VALUES (173865424,7,2,2,20000318233615); 15INSERT INTO t1 VALUES (173865424,3,2,2,20000318233615); 16INSERT INTO t1 VALUES (173865424,6,2,2,20000318233615); 17INSERT INTO t1 VALUES (173865424,60,2,2,20000318233615); 18INSERT INTO t1 VALUES (173865424,1502,2,2,20000318233615); 19INSERT INTO t1 VALUES (48985536,2,2,2,20000319013932); 20INSERT INTO t1 VALUES (48985536,8,2,2,20000319013932); 21INSERT INTO t1 VALUES (48985536,39,2,2,20000319013932); 22INSERT INTO t1 VALUES (48985536,7,2,2,20000319013932); 23INSERT INTO t1 VALUES (465931136,3,2,2,20000318160951); 24INSERT INTO t1 VALUES (465931136,119,1,1,20000318160953); 25INSERT INTO t1 VALUES (465931136,2,1,1,20000318160950); 26INSERT INTO t1 VALUES (465931136,8,1,1,20000318160950); 27INSERT INTO t1 VALUES (465931136,39,1,1,20000318160950); 28INSERT INTO t1 VALUES (1092858576,14,1,1,20000319013445); 29INSERT INTO t1 VALUES (357917728,3,2,2,20000319145026); 30INSERT INTO t1 VALUES (357917728,7,2,2,20000319145027); 31select visitor_id,max(ts) as mts from t1 group by visitor_id 32having mts < DATE_SUB(NOW(),INTERVAL 3 MONTH); 33visitor_id mts 3448985536 2000-03-19 01:39:32 35173865424 2000-03-18 23:36:15 36357917728 2000-03-19 14:50:27 37465931136 2000-03-18 16:09:53 381092858576 2000-03-19 01:34:45 39select visitor_id,max(ts) as mts from t1 group by visitor_id 40having DATE_ADD(mts,INTERVAL 3 MONTH) < NOW(); 41visitor_id mts 4248985536 2000-03-19 01:39:32 43173865424 2000-03-18 23:36:15 44357917728 2000-03-19 14:50:27 45465931136 2000-03-18 16:09:53 461092858576 2000-03-19 01:34:45 47drop table t1; 48set sql_mode='traditional'; 49create table t1 (d date); 50insert into t1 (d) select date_sub('2000-01-01', INTERVAL 2001 YEAR); 51ERROR 22008: Datetime function: datetime field overflow 52insert into t1 (d) select date_add('2000-01-01',interval 8000 year); 53ERROR 22008: Datetime function: datetime field overflow 54insert into t1 values (date_add(NULL, INTERVAL 1 DAY)); 55insert into t1 values (date_add('2000-01-04', INTERVAL NULL DAY)); 56set sql_mode=''; 57Warnings: 58Warning 3090 Changing sql mode 'NO_AUTO_CREATE_USER' is deprecated. It will be removed in a future release. 59insert into t1 (d) select date_sub('2000-01-01', INTERVAL 2001 YEAR); 60Warnings: 61Warning 1441 Datetime function: datetime field overflow 62insert into t1 (d) select date_add('2000-01-01',interval 8000 year); 63Warnings: 64Warning 1441 Datetime function: datetime field overflow 65insert into t1 values (date_add(NULL, INTERVAL 1 DAY)); 66insert into t1 values (date_add('2000-01-04', INTERVAL NULL DAY)); 67select * from t1; 68d 69NULL 70NULL 71NULL 72NULL 73NULL 74NULL 75drop table t1; 76End of 4.1 tests 77SELECT CAST('2006-09-26' AS DATE) + INTERVAL 1 DAY; 78CAST('2006-09-26' AS DATE) + INTERVAL 1 DAY 792006-09-27 80SELECT CAST('2006-09-26' AS DATE) + INTERVAL 1 MONTH; 81CAST('2006-09-26' AS DATE) + INTERVAL 1 MONTH 822006-10-26 83SELECT CAST('2006-09-26' AS DATE) + INTERVAL 1 YEAR; 84CAST('2006-09-26' AS DATE) + INTERVAL 1 YEAR 852007-09-26 86SELECT CAST('2006-09-26' AS DATE) + INTERVAL 1 WEEK; 87CAST('2006-09-26' AS DATE) + INTERVAL 1 WEEK 882006-10-03 89create table t1 (a int, b varchar(10)); 90insert into t1 values (1, '2001-01-01'),(2, '2002-02-02'); 91select '2007-01-01' + interval a day from t1; 92'2007-01-01' + interval a day 932007-01-02 942007-01-03 95select b + interval a day from t1; 96b + interval a day 972001-01-02 982002-02-04 99drop table t1; 100End of 5.0 tests 101