# # time functions # --disable_warnings drop table if exists t1,t2,t3; --enable_warnings select from_days(to_days("960101")),to_days(960201)-to_days("19960101"),to_days(date_add(curdate(), interval 1 day))-to_days(curdate()),weekday("1997-11-29"); select period_add("9602",-12),period_diff(199505,"9404") ; select now()-now(),weekday(curdate())-weekday(now()),unix_timestamp()-unix_timestamp(now()); select from_unixtime(unix_timestamp("1994-03-02 10:11:12")),from_unixtime(unix_timestamp("1994-03-02 10:11:12"),"%Y-%m-%d %h:%i:%s"),from_unixtime(unix_timestamp("1994-03-02 10:11:12"))+0; select sec_to_time(9001),sec_to_time(9001)+0,time_to_sec("15:12:22"), sec_to_time(time_to_sec("0:30:47")/6.21); select sec_to_time(time_to_sec('-838:59:59')); select now()-curdate()*1000000-curtime(); select strcmp(current_timestamp(),concat(current_date()," ",current_time())); select strcmp(localtime(),concat(current_date()," ",current_time())); select strcmp(localtimestamp(),concat(current_date()," ",current_time())); select date_format("1997-01-02 03:04:05", "%M %W %D %Y %y %m %d %h %i %s %w"); select date_format("1997-01-02", concat("%M %W %D ","%Y %y %m %d %h %i %s %w")); select dayofmonth("1997-01-02"),dayofmonth(19970323); select month("1997-01-02"),year("98-02-03"),dayofyear("1997-12-31"); select month("2001-02-00"),year("2001-00-00"); select DAYOFYEAR("1997-03-03"), WEEK("1998-03-03"), QUARTER(980303); select HOUR("1997-03-03 23:03:22"), MINUTE("23:03:22"), SECOND(230322); # Test of week and yearweek select week(19980101),week(19970101),week(19980101,1),week(19970101,1); select week(19981231),week(19971231),week(19981231,1),week(19971231,1); select week(19950101),week(19950101,1); select yearweek('1981-12-31',1),yearweek('1982-01-01',1),yearweek('1982-12-31',1),yearweek('1983-01-01',1); select yearweek('1987-01-01',1),yearweek('1987-01-01'); select week("2000-01-01",0) as '2000', week("2001-01-01",0) as '2001', week("2002-01-01",0) as '2002',week("2003-01-01",0) as '2003', week("2004-01-01",0) as '2004', week("2005-01-01",0) as '2005', week("2006-01-01",0) as '2006'; select week("2000-01-06",0) as '2000', week("2001-01-06",0) as '2001', week("2002-01-06",0) as '2002',week("2003-01-06",0) as '2003', week("2004-01-06",0) as '2004', week("2005-01-06",0) as '2005', week("2006-01-06",0) as '2006'; select week("2000-01-01",1) as '2000', week("2001-01-01",1) as '2001', week("2002-01-01",1) as '2002',week("2003-01-01",1) as '2003', week("2004-01-01",1) as '2004', week("2005-01-01",1) as '2005', week("2006-01-01",1) as '2006'; select week("2000-01-06",1) as '2000', week("2001-01-06",1) as '2001', week("2002-01-06",1) as '2002',week("2003-01-06",1) as '2003', week("2004-01-06",1) as '2004', week("2005-01-06",1) as '2005', week("2006-01-06",1) as '2006'; select yearweek("2000-01-01",0) as '2000', yearweek("2001-01-01",0) as '2001', yearweek("2002-01-01",0) as '2002',yearweek("2003-01-01",0) as '2003', yearweek("2004-01-01",0) as '2004', yearweek("2005-01-01",0) as '2005', yearweek("2006-01-01",0) as '2006'; select yearweek("2000-01-06",0) as '2000', yearweek("2001-01-06",0) as '2001', yearweek("2002-01-06",0) as '2002',yearweek("2003-01-06",0) as '2003', yearweek("2004-01-06",0) as '2004', yearweek("2005-01-06",0) as '2005', yearweek("2006-01-06",0) as '2006'; select yearweek("2000-01-01",1) as '2000', yearweek("2001-01-01",1) as '2001', yearweek("2002-01-01",1) as '2002',yearweek("2003-01-01",1) as '2003', yearweek("2004-01-01",1) as '2004', yearweek("2005-01-01",1) as '2005', yearweek("2006-01-01",1) as '2006'; select yearweek("2000-01-06",1) as '2000', yearweek("2001-01-06",1) as '2001', yearweek("2002-01-06",1) as '2002',yearweek("2003-01-06",1) as '2003', yearweek("2004-01-06",1) as '2004', yearweek("2005-01-06",1) as '2005', yearweek("2006-01-06",1) as '2006'; select week(19981231,2), week(19981231,3), week(20000101,2), week(20000101,3); select week(20001231,2),week(20001231,3); set default_week_format = 2; select week(20001231),week(20001231,2),week(20001231,0); set default_week_format = 0; select date_format('1998-12-31','%x-%v'),date_format('1999-01-01','%x-%v'); select date_format('1999-12-31','%x-%v'),date_format('2000-01-01','%x-%v'); select dayname("1962-03-03"),dayname("1962-03-03")+0; select monthname("1972-03-04"),monthname("1972-03-04")+0; select time_format(19980131000000,'%H|%I|%k|%l|%i|%p|%r|%S|%T'); select time_format(19980131010203,'%H|%I|%k|%l|%i|%p|%r|%S|%T'); select time_format(19980131131415,'%H|%I|%k|%l|%i|%p|%r|%S|%T'); select time_format(19980131010015,'%H|%I|%k|%l|%i|%p|%r|%S|%T'); select date_format(concat('19980131',131415),'%H|%I|%k|%l|%i|%p|%r|%S|%T| %M|%W|%D|%Y|%y|%a|%b|%j|%m|%d|%h|%s|%w'); select date_format(19980021000000,'%H|%I|%k|%l|%i|%p|%r|%S|%T| %M|%W|%D|%Y|%y|%a|%b|%j|%m|%d|%h|%s|%w'); select date_add("1997-12-31 23:59:59",INTERVAL 1 SECOND); select date_add("1997-12-31 23:59:59",INTERVAL 1 MINUTE); select date_add("1997-12-31 23:59:59",INTERVAL 1 HOUR); select date_add("1997-12-31 23:59:59",INTERVAL 1 DAY); select date_add("1997-12-31 23:59:59",INTERVAL 1 MONTH); select date_add("1997-12-31 23:59:59",INTERVAL 1 YEAR); select date_add("1997-12-31 23:59:59",INTERVAL "1:1" MINUTE_SECOND); select date_add("1997-12-31 23:59:59",INTERVAL "1:1" HOUR_MINUTE); select date_add("1997-12-31 23:59:59",INTERVAL "1:1" DAY_HOUR); select date_add("1997-12-31 23:59:59",INTERVAL "1 1" YEAR_MONTH); select date_add("1997-12-31 23:59:59",INTERVAL "1:1:1" HOUR_SECOND); select date_add("1997-12-31 23:59:59",INTERVAL "1 1:1" DAY_MINUTE); select date_add("1997-12-31 23:59:59",INTERVAL "1 1:1:1" DAY_SECOND); select date_sub("1998-01-01 00:00:00",INTERVAL 1 SECOND); select date_sub("1998-01-01 00:00:00",INTERVAL 1 MINUTE); select date_sub("1998-01-01 00:00:00",INTERVAL 1 HOUR); select date_sub("1998-01-01 00:00:00",INTERVAL 1 DAY); select date_sub("1998-01-01 00:00:00",INTERVAL 1 MONTH); select date_sub("1998-01-01 00:00:00",INTERVAL 1 YEAR); select date_sub("1998-01-01 00:00:00",INTERVAL "1:1" MINUTE_SECOND); select date_sub("1998-01-01 00:00:00",INTERVAL "1:1" HOUR_MINUTE); select date_sub("1998-01-01 00:00:00",INTERVAL "1:1" DAY_HOUR); select date_sub("1998-01-01 00:00:00",INTERVAL "1 1" YEAR_MONTH); select date_sub("1998-01-01 00:00:00",INTERVAL "1:1:1" HOUR_SECOND); select date_sub("1998-01-01 00:00:00",INTERVAL "1 1:1" DAY_MINUTE); select date_sub("1998-01-01 00:00:00",INTERVAL "1 1:1:1" DAY_SECOND); select date_add("1997-12-31 23:59:59",INTERVAL 100000 SECOND); select date_add("1997-12-31 23:59:59",INTERVAL -100000 MINUTE); select date_add("1997-12-31 23:59:59",INTERVAL 100000 HOUR); select date_add("1997-12-31 23:59:59",INTERVAL -100000 DAY); select date_add("1997-12-31 23:59:59",INTERVAL 100000 MONTH); select date_add("1997-12-31 23:59:59",INTERVAL -100000 YEAR); select date_add("1997-12-31 23:59:59",INTERVAL "10000:1" MINUTE_SECOND); select date_add("1997-12-31 23:59:59",INTERVAL "-10000:1" HOUR_MINUTE); select date_add("1997-12-31 23:59:59",INTERVAL "10000:1" DAY_HOUR); select date_add("1997-12-31 23:59:59",INTERVAL "-100 1" YEAR_MONTH); select date_add("1997-12-31 23:59:59",INTERVAL "10000:99:99" HOUR_SECOND); select date_add("1997-12-31 23:59:59",INTERVAL " -10000 99:99" DAY_MINUTE); select date_add("1997-12-31 23:59:59",INTERVAL "10000 99:99:99" DAY_SECOND); select "1997-12-31 23:59:59" + INTERVAL 1 SECOND; select INTERVAL 1 DAY + "1997-12-31"; select "1998-01-01 00:00:00" - INTERVAL 1 SECOND; select date_sub("1998-01-02",INTERVAL 31 DAY); select date_add("1997-12-31",INTERVAL 1 SECOND); select date_add("1997-12-31",INTERVAL 1 DAY); select date_add(NULL,INTERVAL 100000 SECOND); select date_add("1997-12-31 23:59:59",INTERVAL NULL SECOND); select date_add("1997-12-31 23:59:59",INTERVAL NULL MINUTE_SECOND); select date_add("9999-12-31 23:59:59",INTERVAL 1 SECOND); select date_sub("0000-00-00 00:00:00",INTERVAL 1 SECOND); select date_add('1998-01-30',Interval 1 month); select date_add('1998-01-30',Interval '2:1' year_month); select date_add('1996-02-29',Interval '1' year); select extract(YEAR FROM "1999-01-02 10:11:12"); select extract(YEAR_MONTH FROM "1999-01-02"); select extract(DAY FROM "1999-01-02"); select extract(DAY_HOUR FROM "1999-01-02 10:11:12"); select extract(DAY_MINUTE FROM "02 10:11:12"); select extract(DAY_SECOND FROM "225 10:11:12"); select extract(HOUR FROM "1999-01-02 10:11:12"); select extract(HOUR_MINUTE FROM "10:11:12"); select extract(HOUR_SECOND FROM "10:11:12"); select extract(MINUTE FROM "10:11:12"); select extract(MINUTE_SECOND FROM "10:11:12"); select extract(SECOND FROM "1999-01-02 10:11:12"); select extract(MONTH FROM "2001-02-00"); create table t1 (ctime varchar(20)); insert into t1 values ('2001-01-12 12:23:40'); select ctime, hour(ctime) from t1; # test bug 614 (multiple extracts in where) select ctime from t1 where extract(MONTH FROM ctime) = 1 AND extract(YEAR FROM ctime) = 2001; drop table t1; # # Test bug with monthname() and NULL # create table t1 (id int); create table t2 (id int, date date); insert into t1 values (1); insert into t2 values (1, "0000-00-00"); insert into t1 values (2); insert into t2 values (2, "2000-01-01"); select monthname(date) from t1 inner join t2 on t1.id = t2.id; select monthname(date) from t1 inner join t2 on t1.id = t2.id order by t1.id; drop table t1,t2; # # Test bug with month() and year() on text fields with wrong information CREATE TABLE t1 (updated text) TYPE=MyISAM; INSERT INTO t1 VALUES (''); SELECT month(updated) from t1; SELECT year(updated) from t1; drop table t1; # # Check that functions work identically on 0000-00-00 as a constant and on a # column # create table t1 (d date, dt datetime, t timestamp, c char(10)); insert into t1 values ("0000-00-00", "0000-00-00", "0000-00-00", "0000-00-00"); select dayofyear("0000-00-00"),dayofyear(d),dayofyear(dt),dayofyear(t),dayofyear(c) from t1; select dayofmonth("0000-00-00"),dayofmonth(d),dayofmonth(dt),dayofmonth(t),dayofmonth(c) from t1; select month("0000-00-00"),month(d),month(dt),month(t),month(c) from t1; select quarter("0000-00-00"),quarter(d),quarter(dt),quarter(t),quarter(c) from t1; select week("0000-00-00"),week(d),week(dt),week(t),week(c) from t1; select year("0000-00-00"),year(d),year(dt),year(t),year(c) from t1; select yearweek("0000-00-00"),yearweek(d),yearweek(dt),yearweek(t),yearweek(c) from t1; select to_days("0000-00-00"),to_days(d),to_days(dt),to_days(t),to_days(c) from t1; select extract(MONTH FROM "0000-00-00"),extract(MONTH FROM d),extract(MONTH FROM dt),extract(MONTH FROM t),extract(MONTH FROM c) from t1; drop table t1; # # Test problem with TIMESTAMP and BETWEEN # CREATE TABLE t1 ( start datetime default NULL); INSERT INTO t1 VALUES ('2002-10-21 00:00:00'),('2002-10-28 00:00:00'),('2002-11-04 00:00:00'); CREATE TABLE t2 ( ctime1 timestamp(14) NOT NULL, ctime2 timestamp(14) NOT NULL); INSERT INTO t2 VALUES (20021029165106,20021105164731); CREATE TABLE t3 (ctime1 char(19) NOT NULL, ctime2 char(19) NOT NULL); INSERT INTO t3 VALUES ("2002-10-29 16:51:06","2002-11-05 16:47:31"); # The following statement should be fixed to return a row in 4.1 select * from t1, t2 where t1.start between t2.ctime1 and t2.ctime2; select * from t1, t2 where t1.start >= t2.ctime1 and t1.start <= t2.ctime2; select * from t1, t3 where t1.start between t3.ctime1 and t3.ctime2; drop table t1,t2,t3; # # Test types from + INTERVAL # CREATE TABLE t1 (datetime datetime, timestamp timestamp, date date, time time); INSERT INTO t1 values ("2001-01-02 03:04:05", "2002-01-02 03:04:05", "2003-01-02", "06:07:08"); SELECT * from t1; select date_add("1997-12-31",INTERVAL 1 SECOND); select date_add("1997-12-31",INTERVAL "1 1" YEAR_MONTH); select date_add(datetime, INTERVAL 1 SECOND) from t1; select date_add(datetime, INTERVAL 1 YEAR) from t1; select date_add(date,INTERVAL 1 SECOND) from t1; select date_add(date,INTERVAL 1 MINUTE) from t1; select date_add(date,INTERVAL 1 HOUR) from t1; select date_add(date,INTERVAL 1 DAY) from t1; select date_add(date,INTERVAL 1 MONTH) from t1; select date_add(date,INTERVAL 1 YEAR) from t1; select date_add(date,INTERVAL "1:1" MINUTE_SECOND) from t1; select date_add(date,INTERVAL "1:1" HOUR_MINUTE) from t1; select date_add(date,INTERVAL "1:1" DAY_HOUR) from t1; select date_add(date,INTERVAL "1 1" YEAR_MONTH) from t1; select date_add(date,INTERVAL "1:1:1" HOUR_SECOND) from t1; select date_add(date,INTERVAL "1 1:1" DAY_MINUTE) from t1; select date_add(date,INTERVAL "1 1:1:1" DAY_SECOND) from t1; select date_add(date,INTERVAL "1" WEEK) from t1; select date_add(date,INTERVAL "1" QUARTER) from t1; select timestampadd(MINUTE, 1, date) from t1; select timestampadd(WEEK, 1, date) from t1; select timestampadd(SQL_TSI_SECOND, 1, date) from t1; select timestampadd(SQL_TSI_FRAC_SECOND, 1, date) from t1; select timestampdiff(MONTH, '2001-02-01', '2001-05-01') as a; select timestampdiff(YEAR, '2002-05-01', '2001-01-01') as a; select timestampdiff(QUARTER, '2002-05-01', '2001-01-01') as a; select timestampdiff(MONTH, '2000-03-28', '2000-02-29') as a; select timestampdiff(MONTH, '1991-03-28', '2000-02-29') as a; select timestampdiff(SQL_TSI_WEEK, '2001-02-01', '2001-05-01') as a; select timestampdiff(SQL_TSI_HOUR, '2001-02-01', '2001-05-01') as a; select timestampdiff(SQL_TSI_DAY, '2001-02-01', '2001-05-01') as a; select timestampdiff(SQL_TSI_MINUTE, '2001-02-01 12:59:59', '2001-05-01 12:58:59') as a; select timestampdiff(SQL_TSI_SECOND, '2001-02-01 12:59:59', '2001-05-01 12:58:58') as a; select timestampdiff(SQL_TSI_FRAC_SECOND, '2001-02-01 12:59:59.120000', '2001-05-01 12:58:58.119999') as a; select timestampdiff(SQL_TSI_DAY, '1986-02-01', '1986-03-01') as a1, timestampdiff(SQL_TSI_DAY, '1900-02-01', '1900-03-01') as a2, timestampdiff(SQL_TSI_DAY, '1996-02-01', '1996-03-01') as a3, timestampdiff(SQL_TSI_DAY, '2000-02-01', '2000-03-01') as a4; # The following is not as one would expect... select date_add(time,INTERVAL 1 SECOND) from t1; drop table t1; # Test SAPDB UTC_% functions. This part is TZ dependant (It is supposed that # TZ variable set to GMT-3 select strcmp(date_sub(localtimestamp(), interval 3 hour), utc_timestamp())=0; select strcmp(date_format(date_sub(localtimestamp(), interval 3 hour),"%T"), utc_time())=0; select strcmp(date_format(date_sub(localtimestamp(), interval 3 hour),"%Y-%m-%d"), utc_date())=0; select strcmp(date_format(utc_timestamp(),"%T"), utc_time())=0; select strcmp(date_format(utc_timestamp(),"%Y-%m-%d"), utc_date())=0; select strcmp(concat(utc_date(),' ',utc_time()),utc_timestamp())=0;