drop table if exists t1,t2,t3; set time_zone="+03:00"; 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"); 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") 1996-01-01 31 1 5 select period_add("9602",-12),period_diff(199505,"9404") ; period_add("9602",-12) period_diff(199505,"9404") 199502 13 select now()-now(),weekday(curdate())-weekday(now()),unix_timestamp()-unix_timestamp(now()); now()-now() weekday(curdate())-weekday(now()) unix_timestamp()-unix_timestamp(now()) 0.000000 0 0 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; 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 1994-03-02 10:11:12 1994-03-02 10:11:12 19940302101112.000000 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); 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) 02:30:01 23001.000000 54742 00:04:57 select sec_to_time(time_to_sec('-838:59:59')); sec_to_time(time_to_sec('-838:59:59')) -838:59:59 select now()-curdate()*1000000-curtime(); now()-curdate()*1000000-curtime() 0.000000 select strcmp(current_timestamp(),concat(current_date()," ",current_time())); strcmp(current_timestamp(),concat(current_date()," ",current_time())) 0 select strcmp(localtime(),concat(current_date()," ",current_time())); strcmp(localtime(),concat(current_date()," ",current_time())) 0 select strcmp(localtimestamp(),concat(current_date()," ",current_time())); strcmp(localtimestamp(),concat(current_date()," ",current_time())) 0 select date_format("1997-01-02 03:04:05", "%M %W %D %Y %y %m %d %h %i %s %w"); date_format("1997-01-02 03:04:05", "%M %W %D %Y %y %m %d %h %i %s %w") January Thursday 2nd 1997 97 01 02 03 04 05 4 select date_format("1997-01-02", concat("%M %W %D ","%Y %y %m %d %h %i %s %w")); date_format("1997-01-02", concat("%M %W %D ","%Y %y %m %d %h %i %s %w")) January Thursday 2nd 1997 97 01 02 12 00 00 4 select dayofmonth("1997-01-02"),dayofmonth(19970323); dayofmonth("1997-01-02") dayofmonth(19970323) 2 23 select month("1997-01-02"),year("98-02-03"),dayofyear("1997-12-31"); month("1997-01-02") year("98-02-03") dayofyear("1997-12-31") 1 1998 365 select month("2001-02-00"),year("2001-00-00"); month("2001-02-00") year("2001-00-00") 2 2001 select DAYOFYEAR("1997-03-03"), WEEK("1998-03-03"), QUARTER(980303); DAYOFYEAR("1997-03-03") WEEK("1998-03-03") QUARTER(980303) 62 9 1 select HOUR("1997-03-03 23:03:22"), MINUTE("23:03:22"), SECOND(230322); HOUR("1997-03-03 23:03:22") MINUTE("23:03:22") SECOND(230322) 23 3 22 select week(19980101),week(19970101),week(19980101,1),week(19970101,1); week(19980101) week(19970101) week(19980101,1) week(19970101,1) 0 0 1 1 select week(19981231),week(19971231),week(19981231,1),week(19971231,1); week(19981231) week(19971231) week(19981231,1) week(19971231,1) 52 52 53 53 select week(19950101),week(19950101,1); week(19950101) week(19950101,1) 1 0 select yearweek('1981-12-31',1),yearweek('1982-01-01',1),yearweek('1982-12-31',1),yearweek('1983-01-01',1); yearweek('1981-12-31',1) yearweek('1982-01-01',1) yearweek('1982-12-31',1) yearweek('1983-01-01',1) 198153 198153 198252 198252 select yearweek('1987-01-01',1),yearweek('1987-01-01'); yearweek('1987-01-01',1) yearweek('1987-01-01') 198701 198652 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'; 2000 2001 2002 2003 2004 2005 2006 0 0 0 0 0 0 1 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'; 2000 2001 2002 2003 2004 2005 2006 1 0 1 1 1 1 1 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'; 2000 2001 2002 2003 2004 2005 2006 0 1 1 1 1 0 0 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'; 2000 2001 2002 2003 2004 2005 2006 1 1 1 2 2 1 1 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'; 2000 2001 2002 2003 2004 2005 2006 199952 200053 200152 200252 200352 200452 200601 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'; 2000 2001 2002 2003 2004 2005 2006 200001 200053 200201 200301 200401 200501 200601 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'; 2000 2001 2002 2003 2004 2005 2006 199952 200101 200201 200301 200401 200453 200552 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'; 2000 2001 2002 2003 2004 2005 2006 200001 200101 200201 200302 200402 200501 200601 select week(19981231,2), week(19981231,3), week(20000101,2), week(20000101,3); week(19981231,2) week(19981231,3) week(20000101,2) week(20000101,3) 52 53 52 52 select week(20001231,2),week(20001231,3); week(20001231,2) week(20001231,3) 53 52 select week(19981231,0) as '0', week(19981231,1) as '1', week(19981231,2) as '2', week(19981231,3) as '3', week(19981231,4) as '4', week(19981231,5) as '5', week(19981231,6) as '6', week(19981231,7) as '7'; 0 1 2 3 4 5 6 7 52 53 52 53 52 52 52 52 select week(20000101,0) as '0', week(20000101,1) as '1', week(20000101,2) as '2', week(20000101,3) as '3', week(20000101,4) as '4', week(20000101,5) as '5', week(20000101,6) as '6', week(20000101,7) as '7'; 0 1 2 3 4 5 6 7 0 0 52 52 0 0 52 52 select week(20000106,0) as '0', week(20000106,1) as '1', week(20000106,2) as '2', week(20000106,3) as '3', week(20000106,4) as '4', week(20000106,5) as '5', week(20000106,6) as '6', week(20000106,7) as '7'; 0 1 2 3 4 5 6 7 1 1 1 1 1 1 1 1 select week(20001231,0) as '0', week(20001231,1) as '1', week(20001231,2) as '2', week(20001231,3) as '3', week(20001231,4) as '4', week(20001231,5) as '5', week(20001231,6) as '6', week(20001231,7) as '7'; 0 1 2 3 4 5 6 7 53 52 53 52 53 52 1 52 select week(20010101,0) as '0', week(20010101,1) as '1', week(20010101,2) as '2', week(20010101,3) as '3', week(20010101,4) as '4', week(20010101,5) as '5', week(20010101,6) as '6', week(20010101,7) as '7'; 0 1 2 3 4 5 6 7 0 1 53 1 1 1 1 1 select yearweek(20001231,0), yearweek(20001231,1), yearweek(20001231,2), yearweek(20001231,3), yearweek(20001231,4), yearweek(20001231,5), yearweek(20001231,6), yearweek(20001231,7); yearweek(20001231,0) yearweek(20001231,1) yearweek(20001231,2) yearweek(20001231,3) yearweek(20001231,4) yearweek(20001231,5) yearweek(20001231,6) yearweek(20001231,7) 200053 200052 200053 200052 200101 200052 200101 200052 set default_week_format = 6; select week(20001231), week(20001231,6); week(20001231) week(20001231,6) 1 1 set default_week_format = 0; set default_week_format = 2; select week(20001231),week(20001231,2),week(20001231,0); week(20001231) week(20001231,2) week(20001231,0) 53 53 53 set default_week_format = 0; select date_format('1998-12-31','%x-%v'),date_format('1999-01-01','%x-%v'); date_format('1998-12-31','%x-%v') date_format('1999-01-01','%x-%v') 1998-53 1998-53 select date_format('1999-12-31','%x-%v'),date_format('2000-01-01','%x-%v'); date_format('1999-12-31','%x-%v') date_format('2000-01-01','%x-%v') 1999-52 1999-52 select dayname("1962-03-03"),dayname("1962-03-03")+0; dayname("1962-03-03") dayname("1962-03-03")+0 Saturday 5 select monthname("1972-03-04"),monthname("1972-03-04")+0; monthname("1972-03-04") monthname("1972-03-04")+0 March 3 select time_format(19980131000000,'%H|%I|%k|%l|%i|%p|%r|%S|%T'); time_format(19980131000000,'%H|%I|%k|%l|%i|%p|%r|%S|%T') 00|12|0|12|00|AM|12:00:00 AM|00|00:00:00 select time_format(19980131010203,'%H|%I|%k|%l|%i|%p|%r|%S|%T'); time_format(19980131010203,'%H|%I|%k|%l|%i|%p|%r|%S|%T') 01|01|1|1|02|AM|01:02:03 AM|03|01:02:03 select time_format(19980131131415,'%H|%I|%k|%l|%i|%p|%r|%S|%T'); time_format(19980131131415,'%H|%I|%k|%l|%i|%p|%r|%S|%T') 13|01|13|1|14|PM|01:14:15 PM|15|13:14:15 select time_format(19980131010015,'%H|%I|%k|%l|%i|%p|%r|%S|%T'); time_format(19980131010015,'%H|%I|%k|%l|%i|%p|%r|%S|%T') 01|01|1|1|00|AM|01:00:15 AM|15|01:00:15 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'); 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') 13|01|13|1|14|PM|01:14:15 PM|15|13:14:15| January|Saturday|31st|1998|98|Sat|Jan|031|01|31|01|15|6 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'); 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') NULL select date_add("1997-12-31 23:59:59",INTERVAL 1 SECOND); date_add("1997-12-31 23:59:59",INTERVAL 1 SECOND) 1998-01-01 00:00:00 select date_add("1997-12-31 23:59:59",INTERVAL 1 MINUTE); date_add("1997-12-31 23:59:59",INTERVAL 1 MINUTE) 1998-01-01 00:00:59 select date_add("1997-12-31 23:59:59",INTERVAL 1 HOUR); date_add("1997-12-31 23:59:59",INTERVAL 1 HOUR) 1998-01-01 00:59:59 select date_add("1997-12-31 23:59:59",INTERVAL 1 DAY); date_add("1997-12-31 23:59:59",INTERVAL 1 DAY) 1998-01-01 23:59:59 select date_add("1997-12-31 23:59:59",INTERVAL 1 MONTH); date_add("1997-12-31 23:59:59",INTERVAL 1 MONTH) 1998-01-31 23:59:59 select date_add("1997-12-31 23:59:59",INTERVAL 1 YEAR); date_add("1997-12-31 23:59:59",INTERVAL 1 YEAR) 1998-12-31 23:59:59 select date_add("1997-12-31 23:59:59",INTERVAL "1:1" MINUTE_SECOND); date_add("1997-12-31 23:59:59",INTERVAL "1:1" MINUTE_SECOND) 1998-01-01 00:01:00 select date_add("1997-12-31 23:59:59",INTERVAL "1:1" HOUR_MINUTE); date_add("1997-12-31 23:59:59",INTERVAL "1:1" HOUR_MINUTE) 1998-01-01 01:00:59 select date_add("1997-12-31 23:59:59",INTERVAL "1:1" DAY_HOUR); date_add("1997-12-31 23:59:59",INTERVAL "1:1" DAY_HOUR) 1998-01-02 00:59:59 select date_add("1997-12-31 23:59:59",INTERVAL "1 1" YEAR_MONTH); date_add("1997-12-31 23:59:59",INTERVAL "1 1" YEAR_MONTH) 1999-01-31 23:59:59 select date_add("1997-12-31 23:59:59",INTERVAL "1:1:1" HOUR_SECOND); date_add("1997-12-31 23:59:59",INTERVAL "1:1:1" HOUR_SECOND) 1998-01-01 01:01:00 select date_add("1997-12-31 23:59:59",INTERVAL "1 1:1" DAY_MINUTE); date_add("1997-12-31 23:59:59",INTERVAL "1 1:1" DAY_MINUTE) 1998-01-02 01:00:59 select date_add("1997-12-31 23:59:59",INTERVAL "1 1:1:1" DAY_SECOND); date_add("1997-12-31 23:59:59",INTERVAL "1 1:1:1" DAY_SECOND) 1998-01-02 01:01:00 select date_sub("1998-01-01 00:00:00",INTERVAL 1 SECOND); date_sub("1998-01-01 00:00:00",INTERVAL 1 SECOND) 1997-12-31 23:59:59 select date_sub("1998-01-01 00:00:00",INTERVAL 1 MINUTE); date_sub("1998-01-01 00:00:00",INTERVAL 1 MINUTE) 1997-12-31 23:59:00 select date_sub("1998-01-01 00:00:00",INTERVAL 1 HOUR); date_sub("1998-01-01 00:00:00",INTERVAL 1 HOUR) 1997-12-31 23:00:00 select date_sub("1998-01-01 00:00:00",INTERVAL 1 DAY); date_sub("1998-01-01 00:00:00",INTERVAL 1 DAY) 1997-12-31 00:00:00 select date_sub("1998-01-01 00:00:00",INTERVAL 1 MONTH); date_sub("1998-01-01 00:00:00",INTERVAL 1 MONTH) 1997-12-01 00:00:00 select date_sub("1998-01-01 00:00:00",INTERVAL 1 YEAR); date_sub("1998-01-01 00:00:00",INTERVAL 1 YEAR) 1997-01-01 00:00:00 select date_sub("1998-01-01 00:00:00",INTERVAL "1:1" MINUTE_SECOND); date_sub("1998-01-01 00:00:00",INTERVAL "1:1" MINUTE_SECOND) 1997-12-31 23:58:59 select date_sub("1998-01-01 00:00:00",INTERVAL "1:1" HOUR_MINUTE); date_sub("1998-01-01 00:00:00",INTERVAL "1:1" HOUR_MINUTE) 1997-12-31 22:59:00 select date_sub("1998-01-01 00:00:00",INTERVAL "1:1" DAY_HOUR); date_sub("1998-01-01 00:00:00",INTERVAL "1:1" DAY_HOUR) 1997-12-30 23:00:00 select date_sub("1998-01-01 00:00:00",INTERVAL "1 1" YEAR_MONTH); date_sub("1998-01-01 00:00:00",INTERVAL "1 1" YEAR_MONTH) 1996-12-01 00:00:00 select date_sub("1998-01-01 00:00:00",INTERVAL "1:1:1" HOUR_SECOND); date_sub("1998-01-01 00:00:00",INTERVAL "1:1:1" HOUR_SECOND) 1997-12-31 22:58:59 select date_sub("1998-01-01 00:00:00",INTERVAL "1 1:1" DAY_MINUTE); date_sub("1998-01-01 00:00:00",INTERVAL "1 1:1" DAY_MINUTE) 1997-12-30 22:59:00 select date_sub("1998-01-01 00:00:00",INTERVAL "1 1:1:1" DAY_SECOND); date_sub("1998-01-01 00:00:00",INTERVAL "1 1:1:1" DAY_SECOND) 1997-12-30 22:58:59 select date_add("1997-12-31 23:59:59",INTERVAL 100000 SECOND); date_add("1997-12-31 23:59:59",INTERVAL 100000 SECOND) 1998-01-02 03:46:39 select date_add("1997-12-31 23:59:59",INTERVAL -100000 MINUTE); date_add("1997-12-31 23:59:59",INTERVAL -100000 MINUTE) 1997-10-23 13:19:59 select date_add("1997-12-31 23:59:59",INTERVAL 100000 HOUR); date_add("1997-12-31 23:59:59",INTERVAL 100000 HOUR) 2009-05-29 15:59:59 select date_add("1997-12-31 23:59:59",INTERVAL -100000 DAY); date_add("1997-12-31 23:59:59",INTERVAL -100000 DAY) 1724-03-17 23:59:59 select date_add("1997-12-31 23:59:59",INTERVAL 100000 MONTH); date_add("1997-12-31 23:59:59",INTERVAL 100000 MONTH) NULL Warnings: Warning 1441 Datetime function: datetime field overflow select date_add("1997-12-31 23:59:59",INTERVAL -100000 YEAR); date_add("1997-12-31 23:59:59",INTERVAL -100000 YEAR) NULL Warnings: Warning 1441 Datetime function: datetime field overflow select date_add("1997-12-31 23:59:59",INTERVAL "10000:1" MINUTE_SECOND); date_add("1997-12-31 23:59:59",INTERVAL "10000:1" MINUTE_SECOND) 1998-01-07 22:40:00 select date_add("1997-12-31 23:59:59",INTERVAL "-10000:1" HOUR_MINUTE); date_add("1997-12-31 23:59:59",INTERVAL "-10000:1" HOUR_MINUTE) 1996-11-10 07:58:59 select date_add("1997-12-31 23:59:59",INTERVAL "10000:1" DAY_HOUR); date_add("1997-12-31 23:59:59",INTERVAL "10000:1" DAY_HOUR) 2025-05-19 00:59:59 select date_add("1997-12-31 23:59:59",INTERVAL "-100 1" YEAR_MONTH); date_add("1997-12-31 23:59:59",INTERVAL "-100 1" YEAR_MONTH) 1897-11-30 23:59:59 select date_add("1997-12-31 23:59:59",INTERVAL "10000:99:99" HOUR_SECOND); date_add("1997-12-31 23:59:59",INTERVAL "10000:99:99" HOUR_SECOND) 1999-02-21 17:40:38 select date_add("1997-12-31 23:59:59",INTERVAL " -10000 99:99" DAY_MINUTE); date_add("1997-12-31 23:59:59",INTERVAL " -10000 99:99" DAY_MINUTE) 1970-08-11 19:20:59 select date_add("1997-12-31 23:59:59",INTERVAL "10000 99:99:99" DAY_SECOND); date_add("1997-12-31 23:59:59",INTERVAL "10000 99:99:99" DAY_SECOND) 2025-05-23 04:40:38 select "1997-12-31 23:59:59" + INTERVAL 1 SECOND; "1997-12-31 23:59:59" + INTERVAL 1 SECOND 1998-01-01 00:00:00 select INTERVAL 1 DAY + "1997-12-31"; INTERVAL 1 DAY + "1997-12-31" 1998-01-01 select "1998-01-01 00:00:00" - INTERVAL 1 SECOND; "1998-01-01 00:00:00" - INTERVAL 1 SECOND 1997-12-31 23:59:59 select date_sub("1998-01-02",INTERVAL 31 DAY); date_sub("1998-01-02",INTERVAL 31 DAY) 1997-12-02 select date_add("1997-12-31",INTERVAL 1 SECOND); date_add("1997-12-31",INTERVAL 1 SECOND) 1997-12-31 00:00:01 select date_add("1997-12-31",INTERVAL 1 DAY); date_add("1997-12-31",INTERVAL 1 DAY) 1998-01-01 select date_add(NULL,INTERVAL 100000 SECOND); date_add(NULL,INTERVAL 100000 SECOND) NULL select date_add("1997-12-31 23:59:59",INTERVAL NULL SECOND); date_add("1997-12-31 23:59:59",INTERVAL NULL SECOND) NULL select date_add("1997-12-31 23:59:59",INTERVAL NULL MINUTE_SECOND); date_add("1997-12-31 23:59:59",INTERVAL NULL MINUTE_SECOND) NULL select date_add("9999-12-31 23:59:59",INTERVAL 1 SECOND); date_add("9999-12-31 23:59:59",INTERVAL 1 SECOND) NULL Warnings: Warning 1441 Datetime function: datetime field overflow select date_sub("0000-00-00 00:00:00",INTERVAL 1 SECOND); date_sub("0000-00-00 00:00:00",INTERVAL 1 SECOND) NULL Warnings: Warning 1292 Incorrect datetime value: '0000-00-00 00:00:00' select date_add('1998-01-30',Interval 1 month); date_add('1998-01-30',Interval 1 month) 1998-02-28 select date_add('1998-01-30',Interval '2:1' year_month); date_add('1998-01-30',Interval '2:1' year_month) 2000-02-29 select date_add('1996-02-29',Interval '1' year); date_add('1996-02-29',Interval '1' year) 1997-02-28 select extract(YEAR FROM "1999-01-02 10:11:12"); extract(YEAR FROM "1999-01-02 10:11:12") 1999 select extract(YEAR_MONTH FROM "1999-01-02"); extract(YEAR_MONTH FROM "1999-01-02") 199901 select extract(DAY FROM "1999-01-02"); extract(DAY FROM "1999-01-02") 2 select extract(DAY_HOUR FROM "1999-01-02 10:11:12"); extract(DAY_HOUR FROM "1999-01-02 10:11:12") 210 select extract(DAY_MINUTE FROM "02 10:11:12"); extract(DAY_MINUTE FROM "02 10:11:12") 21011 select extract(DAY_SECOND FROM "225 10:11:12"); extract(DAY_SECOND FROM "225 10:11:12") 225101112 select extract(HOUR FROM "1999-01-02 10:11:12"); extract(HOUR FROM "1999-01-02 10:11:12") 10 select extract(HOUR_MINUTE FROM "10:11:12"); extract(HOUR_MINUTE FROM "10:11:12") 1011 select extract(HOUR_SECOND FROM "10:11:12"); extract(HOUR_SECOND FROM "10:11:12") 101112 select extract(MINUTE FROM "10:11:12"); extract(MINUTE FROM "10:11:12") 11 select extract(MINUTE_SECOND FROM "10:11:12"); extract(MINUTE_SECOND FROM "10:11:12") 1112 select extract(SECOND FROM "1999-01-02 10:11:12"); extract(SECOND FROM "1999-01-02 10:11:12") 12 select extract(MONTH FROM "2001-02-00"); extract(MONTH FROM "2001-02-00") 2 SELECT EXTRACT(QUARTER FROM '2004-01-15') AS quarter; quarter 1 SELECT EXTRACT(QUARTER FROM '2004-02-15') AS quarter; quarter 1 SELECT EXTRACT(QUARTER FROM '2004-03-15') AS quarter; quarter 1 SELECT EXTRACT(QUARTER FROM '2004-04-15') AS quarter; quarter 2 SELECT EXTRACT(QUARTER FROM '2004-05-15') AS quarter; quarter 2 SELECT EXTRACT(QUARTER FROM '2004-06-15') AS quarter; quarter 2 SELECT EXTRACT(QUARTER FROM '2004-07-15') AS quarter; quarter 3 SELECT EXTRACT(QUARTER FROM '2004-08-15') AS quarter; quarter 3 SELECT EXTRACT(QUARTER FROM '2004-09-15') AS quarter; quarter 3 SELECT EXTRACT(QUARTER FROM '2004-10-15') AS quarter; quarter 4 SELECT EXTRACT(QUARTER FROM '2004-11-15') AS quarter; quarter 4 SELECT EXTRACT(QUARTER FROM '2004-12-15') AS quarter; quarter 4 SELECT DATE_SUB(str_to_date('9999-12-31 00:01:00','%Y-%m-%d %H:%i:%s'), INTERVAL 1 MINUTE); DATE_SUB(str_to_date('9999-12-31 00:01:00','%Y-%m-%d %H:%i:%s'), INTERVAL 1 MINUTE) 9999-12-31 00:00:00 SELECT DATE_ADD(str_to_date('9999-12-30 23:59:00','%Y-%m-%d %H:%i:%s'), INTERVAL 1 MINUTE); DATE_ADD(str_to_date('9999-12-30 23:59:00','%Y-%m-%d %H:%i:%s'), INTERVAL 1 MINUTE) 9999-12-31 00:00:00 SELECT "1900-01-01 00:00:00" + INTERVAL 2147483648 SECOND; "1900-01-01 00:00:00" + INTERVAL 2147483648 SECOND 1968-01-20 03:14:08 SELECT "1900-01-01 00:00:00" + INTERVAL "1:2147483647" MINUTE_SECOND; "1900-01-01 00:00:00" + INTERVAL "1:2147483647" MINUTE_SECOND 1968-01-20 03:15:07 SELECT "1900-01-01 00:00:00" + INTERVAL "100000000:214748364700" MINUTE_SECOND; "1900-01-01 00:00:00" + INTERVAL "100000000:214748364700" MINUTE_SECOND 8895-03-27 22:11:40 SELECT "1900-01-01 00:00:00" + INTERVAL 1<<37 SECOND; "1900-01-01 00:00:00" + INTERVAL 1<<37 SECOND 6255-04-08 15:04:32 SELECT "1900-01-01 00:00:00" + INTERVAL 1<<31 MINUTE; "1900-01-01 00:00:00" + INTERVAL 1<<31 MINUTE 5983-01-24 02:08:00 SELECT "1900-01-01 00:00:00" + INTERVAL 1<<20 HOUR; "1900-01-01 00:00:00" + INTERVAL 1<<20 HOUR 2019-08-15 16:00:00 SELECT "1900-01-01 00:00:00" + INTERVAL 1<<38 SECOND; "1900-01-01 00:00:00" + INTERVAL 1<<38 SECOND NULL Warnings: Warning 1441 Datetime function: datetime field overflow SELECT "1900-01-01 00:00:00" + INTERVAL 1<<33 MINUTE; "1900-01-01 00:00:00" + INTERVAL 1<<33 MINUTE NULL Warnings: Warning 1441 Datetime function: datetime field overflow SELECT "1900-01-01 00:00:00" + INTERVAL 1<<30 HOUR; "1900-01-01 00:00:00" + INTERVAL 1<<30 HOUR NULL Warnings: Warning 1441 Datetime function: datetime field overflow SELECT "1900-01-01 00:00:00" + INTERVAL "1000000000:214748364700" MINUTE_SECOND; "1900-01-01 00:00:00" + INTERVAL "1000000000:214748364700" MINUTE_SECOND NULL Warnings: Warning 1441 Datetime function: datetime field overflow create table t1 (ctime varchar(20)); insert into t1 values ('2001-01-12 12:23:40'); select ctime, hour(ctime) from t1; ctime hour(ctime) 2001-01-12 12:23:40 12 select ctime from t1 where extract(MONTH FROM ctime) = 1 AND extract(YEAR FROM ctime) = 2001; ctime 2001-01-12 12:23:40 drop table t1; 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; monthname(date) NULL January select monthname(date) from t1 inner join t2 on t1.id = t2.id order by t1.id; monthname(date) NULL January drop table t1,t2; CREATE TABLE t1 (updated text) ENGINE=MyISAM; INSERT INTO t1 VALUES (''); SELECT month(updated) from t1; month(updated) NULL Warnings: Warning 1292 Incorrect datetime value: '' SELECT year(updated) from t1; year(updated) NULL Warnings: Warning 1292 Incorrect datetime value: '' drop table t1; 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; dayofyear("0000-00-00") dayofyear(d) dayofyear(dt) dayofyear(t) dayofyear(c) NULL NULL NULL NULL NULL Warnings: Warning 1292 Incorrect datetime value: '0000-00-00' Warning 1292 Incorrect datetime value: '0000-00-00' select dayofmonth("0000-00-00"),dayofmonth(d),dayofmonth(dt),dayofmonth(t),dayofmonth(c) from t1; dayofmonth("0000-00-00") dayofmonth(d) dayofmonth(dt) dayofmonth(t) dayofmonth(c) 0 0 0 0 0 select month("0000-00-00"),month(d),month(dt),month(t),month(c) from t1; month("0000-00-00") month(d) month(dt) month(t) month(c) 0 0 0 0 0 select quarter("0000-00-00"),quarter(d),quarter(dt),quarter(t),quarter(c) from t1; quarter("0000-00-00") quarter(d) quarter(dt) quarter(t) quarter(c) 0 0 0 0 0 select week("0000-00-00"),week(d),week(dt),week(t),week(c) from t1; week("0000-00-00") week(d) week(dt) week(t) week(c) NULL NULL NULL NULL NULL Warnings: Warning 1292 Incorrect datetime value: '0000-00-00' Warning 1292 Incorrect datetime value: '0000-00-00' select year("0000-00-00"),year(d),year(dt),year(t),year(c) from t1; year("0000-00-00") year(d) year(dt) year(t) year(c) 0 0 0 0 0 select yearweek("0000-00-00"),yearweek(d),yearweek(dt),yearweek(t),yearweek(c) from t1; yearweek("0000-00-00") yearweek(d) yearweek(dt) yearweek(t) yearweek(c) NULL NULL NULL NULL NULL Warnings: Warning 1292 Incorrect datetime value: '0000-00-00' Warning 1292 Incorrect datetime value: '0000-00-00' select to_days("0000-00-00"),to_days(d),to_days(dt),to_days(t),to_days(c) from t1; to_days("0000-00-00") to_days(d) to_days(dt) to_days(t) to_days(c) NULL NULL NULL NULL NULL Warnings: Warning 1292 Incorrect datetime value: '0000-00-00' Warning 1292 Incorrect datetime value: '0000-00-00' 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; extract(MONTH FROM "0000-00-00") extract(MONTH FROM d) extract(MONTH FROM dt) extract(MONTH FROM t) extract(MONTH FROM c) 0 0 0 0 0 drop table t1; 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 NOT NULL, ctime2 timestamp 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"); select * from t1, t2 where t1.start between t2.ctime1 and t2.ctime2; start ctime1 ctime2 2002-11-04 00:00:00 2002-10-29 16:51:06 2002-11-05 16:47:31 select * from t1, t2 where t1.start >= t2.ctime1 and t1.start <= t2.ctime2; start ctime1 ctime2 2002-11-04 00:00:00 2002-10-29 16:51:06 2002-11-05 16:47:31 select * from t1, t3 where t1.start between t3.ctime1 and t3.ctime2; start ctime1 ctime2 2002-11-04 00:00:00 2002-10-29 16:51:06 2002-11-05 16:47:31 drop table t1,t2,t3; select @a:=FROM_UNIXTIME(1); @a:=FROM_UNIXTIME(1) 1970-01-01 03:00:01 select unix_timestamp(@a); unix_timestamp(@a) 1 select unix_timestamp('1969-12-01 19:00:01'); unix_timestamp('1969-12-01 19:00:01') 0 select from_unixtime(-1); from_unixtime(-1) NULL select from_unixtime(2145916800); from_unixtime(2145916800) NULL select from_unixtime(0); from_unixtime(0) 1970-01-01 03:00:00 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; datetime timestamp date time 2001-01-02 03:04:05 2002-01-02 03:04:05 2003-01-02 06:07:08 select date_add("1997-12-31",INTERVAL 1 SECOND); date_add("1997-12-31",INTERVAL 1 SECOND) 1997-12-31 00:00:01 select date_add("1997-12-31",INTERVAL "1 1" YEAR_MONTH); date_add("1997-12-31",INTERVAL "1 1" YEAR_MONTH) 1999-01-31 select date_add(datetime, INTERVAL 1 SECOND) from t1; date_add(datetime, INTERVAL 1 SECOND) 2001-01-02 03:04:06 select date_add(datetime, INTERVAL 1 YEAR) from t1; date_add(datetime, INTERVAL 1 YEAR) 2002-01-02 03:04:05 select date_add(date,INTERVAL 1 SECOND) from t1; date_add(date,INTERVAL 1 SECOND) 2003-01-02 00:00:01 select date_add(date,INTERVAL 1 MINUTE) from t1; date_add(date,INTERVAL 1 MINUTE) 2003-01-02 00:01:00 select date_add(date,INTERVAL 1 HOUR) from t1; date_add(date,INTERVAL 1 HOUR) 2003-01-02 01:00:00 select date_add(date,INTERVAL 1 DAY) from t1; date_add(date,INTERVAL 1 DAY) 2003-01-03 select date_add(date,INTERVAL 1 MONTH) from t1; date_add(date,INTERVAL 1 MONTH) 2003-02-02 select date_add(date,INTERVAL 1 YEAR) from t1; date_add(date,INTERVAL 1 YEAR) 2004-01-02 select date_add(date,INTERVAL "1:1" MINUTE_SECOND) from t1; date_add(date,INTERVAL "1:1" MINUTE_SECOND) 2003-01-02 00:01:01 select date_add(date,INTERVAL "1:1" HOUR_MINUTE) from t1; date_add(date,INTERVAL "1:1" HOUR_MINUTE) 2003-01-02 01:01:00 select date_add(date,INTERVAL "1:1" DAY_HOUR) from t1; date_add(date,INTERVAL "1:1" DAY_HOUR) 2003-01-03 01:00:00 select date_add(date,INTERVAL "1 1" YEAR_MONTH) from t1; date_add(date,INTERVAL "1 1" YEAR_MONTH) 2004-02-02 select date_add(date,INTERVAL "1:1:1" HOUR_SECOND) from t1; date_add(date,INTERVAL "1:1:1" HOUR_SECOND) 2003-01-02 01:01:01 select date_add(date,INTERVAL "1 1:1" DAY_MINUTE) from t1; date_add(date,INTERVAL "1 1:1" DAY_MINUTE) 2003-01-03 01:01:00 select date_add(date,INTERVAL "1 1:1:1" DAY_SECOND) from t1; date_add(date,INTERVAL "1 1:1:1" DAY_SECOND) 2003-01-03 01:01:01 select date_add(date,INTERVAL "1" WEEK) from t1; date_add(date,INTERVAL "1" WEEK) 2003-01-09 00:00:00 select date_add(date,INTERVAL "1" QUARTER) from t1; date_add(date,INTERVAL "1" QUARTER) 2003-04-02 select timestampadd(MINUTE, 1, date) from t1; timestampadd(MINUTE, 1, date) 2003-01-02 00:01:00 select timestampadd(WEEK, 1, date) from t1; timestampadd(WEEK, 1, date) 2003-01-09 00:00:00 select timestampadd(SQL_TSI_SECOND, 1, date) from t1; timestampadd(SQL_TSI_SECOND, 1, date) 2003-01-02 00:00:01 select timestampadd(SQL_TSI_FRAC_SECOND, 1, date) from t1; timestampadd(SQL_TSI_FRAC_SECOND, 1, date) 2003-01-02 00:00:00.000001 select timestampdiff(MONTH, '2001-02-01', '2001-05-01') as a; a 3 select timestampdiff(YEAR, '2002-05-01', '2001-01-01') as a; a -1 select timestampdiff(QUARTER, '2002-05-01', '2001-01-01') as a; a -5 select timestampdiff(MONTH, '2000-03-28', '2000-02-29') as a; a 0 select timestampdiff(MONTH, '1991-03-28', '2000-02-29') as a; a 107 select timestampdiff(SQL_TSI_WEEK, '2001-02-01', '2001-05-01') as a; a 12 select timestampdiff(SQL_TSI_HOUR, '2001-02-01', '2001-05-01') as a; a 2136 select timestampdiff(SQL_TSI_DAY, '2001-02-01', '2001-05-01') as a; a 89 select timestampdiff(SQL_TSI_MINUTE, '2001-02-01 12:59:59', '2001-05-01 12:58:59') as a; a 128159 select timestampdiff(SQL_TSI_SECOND, '2001-02-01 12:59:59', '2001-05-01 12:58:58') as a; a 7689539 select timestampdiff(SQL_TSI_FRAC_SECOND, '2001-02-01 12:59:59.120000', '2001-05-01 12:58:58.119999') as a; a 7689538999999 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; a1 a2 a3 a4 28 28 29 29 SELECT TIMESTAMPDIFF(day,'2006-01-10 14:30:28','2006-01-11 14:30:27'); TIMESTAMPDIFF(day,'2006-01-10 14:30:28','2006-01-11 14:30:27') 0 SELECT TIMESTAMPDIFF(day,'2006-01-10 14:30:28','2006-01-11 14:30:28'); TIMESTAMPDIFF(day,'2006-01-10 14:30:28','2006-01-11 14:30:28') 1 SELECT TIMESTAMPDIFF(day,'2006-01-10 14:30:28','2006-01-11 14:30:29'); TIMESTAMPDIFF(day,'2006-01-10 14:30:28','2006-01-11 14:30:29') 1 SELECT TIMESTAMPDIFF(day,'2006-01-10 14:30:28','2006-01-12 14:30:27'); TIMESTAMPDIFF(day,'2006-01-10 14:30:28','2006-01-12 14:30:27') 1 SELECT TIMESTAMPDIFF(day,'2006-01-10 14:30:28','2006-01-12 14:30:28'); TIMESTAMPDIFF(day,'2006-01-10 14:30:28','2006-01-12 14:30:28') 2 SELECT TIMESTAMPDIFF(day,'2006-01-10 14:30:28','2006-01-12 14:30:29'); TIMESTAMPDIFF(day,'2006-01-10 14:30:28','2006-01-12 14:30:29') 2 SELECT TIMESTAMPDIFF(week,'2006-01-10 14:30:28','2006-01-17 14:30:27'); TIMESTAMPDIFF(week,'2006-01-10 14:30:28','2006-01-17 14:30:27') 0 SELECT TIMESTAMPDIFF(week,'2006-01-10 14:30:28','2006-01-17 14:30:28'); TIMESTAMPDIFF(week,'2006-01-10 14:30:28','2006-01-17 14:30:28') 1 SELECT TIMESTAMPDIFF(week,'2006-01-10 14:30:28','2006-01-17 14:30:29'); TIMESTAMPDIFF(week,'2006-01-10 14:30:28','2006-01-17 14:30:29') 1 SELECT TIMESTAMPDIFF(week,'2006-01-10 14:30:28','2006-01-24 14:30:27'); TIMESTAMPDIFF(week,'2006-01-10 14:30:28','2006-01-24 14:30:27') 1 SELECT TIMESTAMPDIFF(week,'2006-01-10 14:30:28','2006-01-24 14:30:28'); TIMESTAMPDIFF(week,'2006-01-10 14:30:28','2006-01-24 14:30:28') 2 SELECT TIMESTAMPDIFF(week,'2006-01-10 14:30:28','2006-01-24 14:30:29'); TIMESTAMPDIFF(week,'2006-01-10 14:30:28','2006-01-24 14:30:29') 2 SELECT TIMESTAMPDIFF(month,'2006-01-10 14:30:28','2006-02-10 14:30:27'); TIMESTAMPDIFF(month,'2006-01-10 14:30:28','2006-02-10 14:30:27') 0 SELECT TIMESTAMPDIFF(month,'2006-01-10 14:30:28','2006-02-10 14:30:28'); TIMESTAMPDIFF(month,'2006-01-10 14:30:28','2006-02-10 14:30:28') 1 SELECT TIMESTAMPDIFF(month,'2006-01-10 14:30:28','2006-02-10 14:30:29'); TIMESTAMPDIFF(month,'2006-01-10 14:30:28','2006-02-10 14:30:29') 1 SELECT TIMESTAMPDIFF(month,'2006-01-10 14:30:28','2006-03-10 14:30:27'); TIMESTAMPDIFF(month,'2006-01-10 14:30:28','2006-03-10 14:30:27') 1 SELECT TIMESTAMPDIFF(month,'2006-01-10 14:30:28','2006-03-10 14:30:28'); TIMESTAMPDIFF(month,'2006-01-10 14:30:28','2006-03-10 14:30:28') 2 SELECT TIMESTAMPDIFF(month,'2006-01-10 14:30:28','2006-03-10 14:30:29'); TIMESTAMPDIFF(month,'2006-01-10 14:30:28','2006-03-10 14:30:29') 2 SELECT TIMESTAMPDIFF(year,'2006-01-10 14:30:28','2007-01-10 14:30:27'); TIMESTAMPDIFF(year,'2006-01-10 14:30:28','2007-01-10 14:30:27') 0 SELECT TIMESTAMPDIFF(year,'2006-01-10 14:30:28','2007-01-10 14:30:28'); TIMESTAMPDIFF(year,'2006-01-10 14:30:28','2007-01-10 14:30:28') 1 SELECT TIMESTAMPDIFF(year,'2006-01-10 14:30:28','2007-01-10 14:30:29'); TIMESTAMPDIFF(year,'2006-01-10 14:30:28','2007-01-10 14:30:29') 1 SELECT TIMESTAMPDIFF(year,'2006-01-10 14:30:28','2008-01-10 14:30:27'); TIMESTAMPDIFF(year,'2006-01-10 14:30:28','2008-01-10 14:30:27') 1 SELECT TIMESTAMPDIFF(year,'2006-01-10 14:30:28','2008-01-10 14:30:28'); TIMESTAMPDIFF(year,'2006-01-10 14:30:28','2008-01-10 14:30:28') 2 SELECT TIMESTAMPDIFF(year,'2006-01-10 14:30:28','2008-01-10 14:30:29'); TIMESTAMPDIFF(year,'2006-01-10 14:30:28','2008-01-10 14:30:29') 2 select date_add(time,INTERVAL 1 SECOND) from t1; date_add(time,INTERVAL 1 SECOND) NULL Warnings: Warning 1264 Out of range value for column 'time' at row 1 drop table t1; select last_day('2000-02-05') as f1, last_day('2002-12-31') as f2, last_day('2003-03-32') as f3, last_day('2003-04-01') as f4, last_day('2001-01-01 01:01:01') as f5, last_day(NULL), last_day('2001-02-12'); f1 f2 f3 f4 f5 last_day(NULL) last_day('2001-02-12') 2000-02-29 2002-12-31 NULL 2003-04-30 2001-01-31 NULL 2001-02-28 Warnings: Warning 1292 Incorrect datetime value: '2003-03-32' create table t1 select last_day('2000-02-05') as a, from_days(to_days("960101")) as b; describe t1; Field Type Null Key Default Extra a date NO 0000-00-00 b date YES NULL select * from t1; a b 2000-02-29 1996-01-01 drop table t1; select last_day('2000-02-05') as a, from_days(to_days("960101")) as b; a b 2000-02-29 1996-01-01 select date_add(last_day("1997-12-1"), INTERVAL 1 DAY); date_add(last_day("1997-12-1"), INTERVAL 1 DAY) 1998-01-01 select length(last_day("1997-12-1")); length(last_day("1997-12-1")) 10 select last_day("1997-12-1")+0; last_day("1997-12-1")+0 19971231 select last_day("1997-12-1")+0.0; last_day("1997-12-1")+0.0 19971231.0 select strcmp(date_sub(localtimestamp(), interval 3 hour), utc_timestamp())=0; strcmp(date_sub(localtimestamp(), interval 3 hour), utc_timestamp())=0 1 select strcmp(date_format(date_sub(localtimestamp(), interval 3 hour),"%T"), utc_time())=0; strcmp(date_format(date_sub(localtimestamp(), interval 3 hour),"%T"), utc_time())=0 1 select strcmp(date_format(date_sub(localtimestamp(), interval 3 hour),"%Y-%m-%d"), utc_date())=0; strcmp(date_format(date_sub(localtimestamp(), interval 3 hour),"%Y-%m-%d"), utc_date())=0 1 select strcmp(date_format(utc_timestamp(),"%T"), utc_time())=0; strcmp(date_format(utc_timestamp(),"%T"), utc_time())=0 1 select strcmp(date_format(utc_timestamp(),"%Y-%m-%d"), utc_date())=0; strcmp(date_format(utc_timestamp(),"%Y-%m-%d"), utc_date())=0 1 select strcmp(concat(utc_date(),' ',utc_time()),utc_timestamp())=0; strcmp(concat(utc_date(),' ',utc_time()),utc_timestamp())=0 1 explain extended select period_add("9602",-12),period_diff(199505,"9404"),from_days(to_days("960101")),dayofmonth("1997-01-02"), month("1997-01-02"), monthname("1972-03-04"),dayofyear("0000-00-00"),HOUR("1997-03-03 23:03:22"),MINUTE("23:03:22"),SECOND(230322),QUARTER(980303),WEEK("1998-03-03"),yearweek("2000-01-01",1),week(19950101,1),year("98-02-03"),weekday(curdate())-weekday(now()),dayname("1962-03-03"),unix_timestamp(),sec_to_time(time_to_sec("0:30:47")/6.21),curtime(),utc_time(),curdate(),utc_date(),utc_timestamp(),date_format("1997-01-02 03:04:05", "%M %W %D %Y %y %m %d %h %i %s %w"),from_unixtime(unix_timestamp("1994-03-02 10:11:12")),"1997-12-31 23:59:59" + INTERVAL 1 SECOND,"1998-01-01 00:00:00" - INTERVAL 1 SECOND,INTERVAL 1 DAY + "1997-12-31", extract(YEAR FROM "1999-01-02 10:11:12"),date_add("1997-12-31 23:59:59",INTERVAL 1 SECOND); id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL No tables used Warnings: Note 1003 select period_add(_latin1'9602',-(12)) AS `period_add("9602",-12)`,period_diff(199505,_latin1'9404') AS `period_diff(199505,"9404")`,from_days(to_days(_latin1'960101')) AS `from_days(to_days("960101"))`,dayofmonth(_latin1'1997-01-02') AS `dayofmonth("1997-01-02")`,month(_latin1'1997-01-02') AS `month("1997-01-02")`,monthname(_latin1'1972-03-04') AS `monthname("1972-03-04")`,dayofyear(_latin1'0000-00-00') AS `dayofyear("0000-00-00")`,hour(_latin1'1997-03-03 23:03:22') AS `HOUR("1997-03-03 23:03:22")`,minute(_latin1'23:03:22') AS `MINUTE("23:03:22")`,second(230322) AS `SECOND(230322)`,quarter(980303) AS `QUARTER(980303)`,week(_latin1'1998-03-03',0) AS `WEEK("1998-03-03")`,yearweek(_latin1'2000-01-01',1) AS `yearweek("2000-01-01",1)`,week(19950101,1) AS `week(19950101,1)`,year(_latin1'98-02-03') AS `year("98-02-03")`,(weekday(curdate()) - weekday(now())) AS `weekday(curdate())-weekday(now())`,dayname(_latin1'1962-03-03') AS `dayname("1962-03-03")`,unix_timestamp() AS `unix_timestamp()`,sec_to_time((time_to_sec(_latin1'0:30:47') / 6.21)) AS `sec_to_time(time_to_sec("0:30:47")/6.21)`,curtime() AS `curtime()`,utc_time() AS `utc_time()`,curdate() AS `curdate()`,utc_date() AS `utc_date()`,utc_timestamp() AS `utc_timestamp()`,date_format(_latin1'1997-01-02 03:04:05',_latin1'%M %W %D %Y %y %m %d %h %i %s %w') AS `date_format("1997-01-02 03:04:05", "%M %W %D %Y %y %m %d %h %i %s %w")`,from_unixtime(unix_timestamp(_latin1'1994-03-02 10:11:12')) AS `from_unixtime(unix_timestamp("1994-03-02 10:11:12"))`,(_latin1'1997-12-31 23:59:59' + interval 1 second) AS `"1997-12-31 23:59:59" + INTERVAL 1 SECOND`,(_latin1'1998-01-01 00:00:00' - interval 1 second) AS `"1998-01-01 00:00:00" - INTERVAL 1 SECOND`,(_latin1'1997-12-31' + interval 1 day) AS `INTERVAL 1 DAY + "1997-12-31"`,extract(year from _latin1'1999-01-02 10:11:12') AS `extract(YEAR FROM "1999-01-02 10:11:12")`,(_latin1'1997-12-31 23:59:59' + interval 1 second) AS `date_add("1997-12-31 23:59:59",INTERVAL 1 SECOND)` SET @TMP=NOW(); CREATE TABLE t1 (d DATETIME); INSERT INTO t1 VALUES (NOW()); INSERT INTO t1 VALUES (NOW()); INSERT INTO t1 VALUES (NOW()); SELECT count(*) FROM t1 WHERE d>FROM_DAYS(TO_DAYS(@TMP)) AND d<=FROM_DAYS(TO_DAYS(@TMP)+1); count(*) 3 DROP TABLE t1; select last_day('2005-00-00'); last_day('2005-00-00') NULL Warnings: Warning 1292 Incorrect datetime value: '2005-00-00' select last_day('2005-00-01'); last_day('2005-00-01') NULL Warnings: Warning 1292 Incorrect datetime value: '2005-00-01' select last_day('2005-01-00'); last_day('2005-01-00') NULL Warnings: Warning 1292 Incorrect datetime value: '2005-01-00' select monthname(str_to_date(null, '%m')), monthname(str_to_date(null, '%m')), monthname(str_to_date(1, '%m')), monthname(str_to_date(0, '%m')); monthname(str_to_date(null, '%m')) monthname(str_to_date(null, '%m')) monthname(str_to_date(1, '%m')) monthname(str_to_date(0, '%m')) NULL NULL January NULL set time_zone='-6:00'; create table t1(a timestamp); insert into t1 values (19691231190001); select * from t1; a 1969-12-31 19:00:01 drop table t1; create table t1(f1 date, f2 time, f3 datetime); insert into t1 values ("2006-01-01", "12:01:01", "2006-01-01 12:01:01"); insert into t1 values ("2006-01-02", "12:01:02", "2006-01-02 12:01:02"); select f1 from t1 where f1 between "2006-1-1" and 20060101; f1 2006-01-01 select f1 from t1 where f1 between "2006-1-1" and "2006.1.1"; f1 2006-01-01 select f1 from t1 where date(f1) between "2006-1-1" and "2006.1.1"; f1 2006-01-01 select f2 from t1 where f2 between "12:1:2" and "12:2:2"; f2 12:01:02 select f2 from t1 where time(f2) between "12:1:2" and "12:2:2"; f2 12:01:02 select f3 from t1 where f3 between "2006-1-1 12:1:1" and "2006-1-1 12:1:2"; f3 2006-01-01 12:01:01 select f3 from t1 where timestamp(f3) between "2006-1-1 12:1:1" and "2006-1-1 12:1:2"; f3 2006-01-01 12:01:01 select f1 from t1 where "2006-1-1" between f1 and f3; f1 2006-01-01 select f1 from t1 where "2006-1-1" between date(f1) and date(f3); f1 2006-01-01 select f1 from t1 where "2006-1-1" between f1 and 'zzz'; f1 Warnings: Warning 1292 Incorrect date value: 'zzz' for column 'f1' at row 1 Warning 1292 Truncated incorrect DOUBLE value: 'zzz' Warning 1292 Truncated incorrect DOUBLE value: 'zzz' select f1 from t1 where makedate(2006,1) between date(f1) and date(f3); f1 2006-01-01 select f1 from t1 where makedate(2006,2) between date(f1) and date(f3); f1 2006-01-02 drop table t1; create table t1 select now() - now(), curtime() - curtime(), sec_to_time(1) + 0, from_unixtime(1) + 0; show create table t1; Table Create Table t1 CREATE TABLE `t1` ( `now() - now()` double(23,6) NOT NULL DEFAULT '0.000000', `curtime() - curtime()` double(23,6) NOT NULL DEFAULT '0.000000', `sec_to_time(1) + 0` double(23,6) DEFAULT NULL, `from_unixtime(1) + 0` double(23,6) DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 drop table t1; (select time_format(timediff(now(), DATE_SUB(now(),INTERVAL 5 DAY)),'%H') As H) union (select time_format(timediff(now(), DATE_SUB(now(),INTERVAL 5 DAY)),'%H') As H); H 120 (select time_format(timediff(now(), DATE_SUB(now(),INTERVAL 5 DAY)),'%k') As H) union (select time_format(timediff(now(), DATE_SUB(now(),INTERVAL 5 DAY)),'%k') As H); H 120 (select time_format(timediff(now(), DATE_SUB(now(),INTERVAL 5 HOUR)),'%H') As H) union (select time_format(timediff(now(), DATE_SUB(now(),INTERVAL 5 HOUR)),'%H') As H); H 05 (select time_format(timediff(now(), DATE_SUB(now(),INTERVAL 5 HOUR)),'%k') As H) union (select time_format(timediff(now(), DATE_SUB(now(),INTERVAL 5 HOUR)),'%k') As H); H 5 SET NAMES latin1; SET character_set_results = NULL; SHOW VARIABLES LIKE 'character_set_results'; Variable_name Value character_set_results CREATE TABLE testBug8868 (field1 DATE, field2 VARCHAR(32) CHARACTER SET BINARY); INSERT INTO testBug8868 VALUES ('2006-09-04', 'abcd'); SELECT DATE_FORMAT(field1,'%b-%e %l:%i%p') as fmtddate, field2 FROM testBug8868; fmtddate field2 Sep-4 12:00AM abcd DROP TABLE testBug8868; SET NAMES DEFAULT; End of 4.1 tests explain extended select timestampdiff(SQL_TSI_WEEK, '2001-02-01', '2001-05-01') as a1, timestampdiff(SQL_TSI_FRAC_SECOND, '2001-02-01 12:59:59.120000', '2001-05-01 12:58:58.119999') as a2; id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL No tables used Warnings: Note 1003 select timestampdiff(WEEK,_latin1'2001-02-01',_latin1'2001-05-01') AS `a1`,timestampdiff(SECOND_FRAC,_latin1'2001-02-01 12:59:59.120000',_latin1'2001-05-01 12:58:58.119999') AS `a2` select time_format('100:00:00', '%H %k %h %I %l'); time_format('100:00:00', '%H %k %h %I %l') 100 100 04 04 4 create table t1 (a timestamp default '2005-05-05 01:01:01', b timestamp default '2005-05-05 01:01:01'); drop function if exists t_slow_sysdate; create function t_slow_sysdate() returns timestamp begin do sleep(2); return sysdate(); end; // insert into t1 set a = sysdate(), b = t_slow_sysdate();// create trigger t_before before insert on t1 for each row begin set new.b = t_slow_sysdate(); end // insert into t1 set a = sysdate(); select a != b from t1; a != b 1 1 drop trigger t_before; drop function t_slow_sysdate; drop table t1; create table t1 (a datetime, i int, b datetime); insert into t1 select sysdate(), sleep(1), sysdate() from dual; select a != b from t1; a != b 1 drop table t1; create procedure t_sysdate() begin select sysdate() into @a; do sleep(2); select sysdate() into @b; select @a != @b; end; // call t_sysdate(); @a != @b 1 drop procedure t_sysdate; select timestampdiff(month,'2004-09-11','2004-09-11'); timestampdiff(month,'2004-09-11','2004-09-11') 0 select timestampdiff(month,'2004-09-11','2005-09-11'); timestampdiff(month,'2004-09-11','2005-09-11') 12 select timestampdiff(month,'2004-09-11','2006-09-11'); timestampdiff(month,'2004-09-11','2006-09-11') 24 select timestampdiff(month,'2004-09-11','2007-09-11'); timestampdiff(month,'2004-09-11','2007-09-11') 36 select timestampdiff(month,'2005-09-11','2004-09-11'); timestampdiff(month,'2005-09-11','2004-09-11') -12 select timestampdiff(month,'2005-09-11','2003-09-11'); timestampdiff(month,'2005-09-11','2003-09-11') -24 select timestampdiff(month,'2004-02-28','2005-02-28'); timestampdiff(month,'2004-02-28','2005-02-28') 12 select timestampdiff(month,'2004-02-29','2005-02-28'); timestampdiff(month,'2004-02-29','2005-02-28') 11 select timestampdiff(month,'2004-02-28','2005-02-28'); timestampdiff(month,'2004-02-28','2005-02-28') 12 select timestampdiff(month,'2004-03-29','2005-03-28'); timestampdiff(month,'2004-03-29','2005-03-28') 11 select timestampdiff(month,'2003-02-28','2004-02-29'); timestampdiff(month,'2003-02-28','2004-02-29') 12 select timestampdiff(month,'2003-02-28','2005-02-28'); timestampdiff(month,'2003-02-28','2005-02-28') 24 select timestampdiff(month,'1999-09-11','2001-10-10'); timestampdiff(month,'1999-09-11','2001-10-10') 24 select timestampdiff(month,'1999-09-11','2001-9-11'); timestampdiff(month,'1999-09-11','2001-9-11') 24 select timestampdiff(year,'1999-09-11','2001-9-11'); timestampdiff(year,'1999-09-11','2001-9-11') 2 select timestampdiff(year,'2004-02-28','2005-02-28'); timestampdiff(year,'2004-02-28','2005-02-28') 1 select timestampdiff(year,'2004-02-29','2005-02-28'); timestampdiff(year,'2004-02-29','2005-02-28') 0 CREATE TABLE t1 (id int NOT NULL PRIMARY KEY, day date); CREATE TABLE t2 (id int NOT NULL PRIMARY KEY, day date); INSERT INTO t1 VALUES (1, '2005-06-01'), (2, '2005-02-01'), (3, '2005-07-01'); INSERT INTO t2 VALUES (1, '2005-08-01'), (2, '2005-06-15'), (3, '2005-07-15'); SELECT * FROM t1, t2 WHERE t1.day BETWEEN '2005.09.01' - INTERVAL 6 MONTH AND t2.day; id day id day 1 2005-06-01 1 2005-08-01 3 2005-07-01 1 2005-08-01 1 2005-06-01 2 2005-06-15 1 2005-06-01 3 2005-07-15 3 2005-07-01 3 2005-07-15 SELECT * FROM t1, t2 WHERE CAST(t1.day AS DATE) BETWEEN '2005.09.01' - INTERVAL 6 MONTH AND t2.day; id day id day 1 2005-06-01 1 2005-08-01 3 2005-07-01 1 2005-08-01 1 2005-06-01 2 2005-06-15 1 2005-06-01 3 2005-07-15 3 2005-07-01 3 2005-07-15 DROP TABLE t1,t2; set time_zone= @@global.time_zone; End of 5.0 tests select date_sub("0050-01-01 00:00:01",INTERVAL 2 SECOND); date_sub("0050-01-01 00:00:01",INTERVAL 2 SECOND) NULL select date_sub("0199-01-01 00:00:01",INTERVAL 2 SECOND); date_sub("0199-01-01 00:00:01",INTERVAL 2 SECOND) NULL select date_add("0199-12-31 23:59:59",INTERVAL 2 SECOND); date_add("0199-12-31 23:59:59",INTERVAL 2 SECOND) NULL select date_sub("0200-01-01 00:00:01",INTERVAL 2 SECOND); date_sub("0200-01-01 00:00:01",INTERVAL 2 SECOND) 0199-12-31 23:59:59 select date_sub("0200-01-01 00:00:01",INTERVAL 1 SECOND); date_sub("0200-01-01 00:00:01",INTERVAL 1 SECOND) 0200-01-01 00:00:00 select date_sub("0200-01-01 00:00:01",INTERVAL 2 SECOND); date_sub("0200-01-01 00:00:01",INTERVAL 2 SECOND) 0199-12-31 23:59:59 select date_add("2001-01-01 23:59:59",INTERVAL -2000 YEAR); date_add("2001-01-01 23:59:59",INTERVAL -2000 YEAR) 0001-01-01 23:59:59 select date_sub("50-01-01 00:00:01",INTERVAL 2 SECOND); date_sub("50-01-01 00:00:01",INTERVAL 2 SECOND) 2049-12-31 23:59:59 select date_sub("90-01-01 00:00:01",INTERVAL 2 SECOND); date_sub("90-01-01 00:00:01",INTERVAL 2 SECOND) 1989-12-31 23:59:59 select date_sub("0069-01-01 00:00:01",INTERVAL 2 SECOND); date_sub("0069-01-01 00:00:01",INTERVAL 2 SECOND) NULL select date_sub("0169-01-01 00:00:01",INTERVAL 2 SECOND); date_sub("0169-01-01 00:00:01",INTERVAL 2 SECOND) NULL End of 5.1 tests