Edit File: func_time.result
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 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 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 54742 00:04:57.4235 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 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 0 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") 5811 select extract(DAY_SECOND FROM "225 10:11:12"); extract(DAY_SECOND FROM "225 10:11:12") 8385959 Warnings: Warning 1292 Truncated incorrect time value: '225 10:11:12' 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(-0.000001); FROM_UNIXTIME(-0.000001) NULL select from_unixtime(2147483647); from_unixtime(2147483647) 2038-01-19 06:14:07 select from_unixtime(2147483648); from_unixtime(2147483648) NULL select from_unixtime(0); from_unixtime(0) 1970-01-01 03:00:00 select unix_timestamp(from_unixtime(2147483647)); unix_timestamp(from_unixtime(2147483647)) 2147483647 select unix_timestamp(from_unixtime(2147483648)); unix_timestamp(from_unixtime(2147483648)) NULL select unix_timestamp('2039-01-20 01:00:00'); unix_timestamp('2039-01-20 01:00:00') 0 select unix_timestamp('1968-01-20 01:00:00'); unix_timestamp('1968-01-20 01:00:00') 0 select unix_timestamp('2038-02-10 01:00:00'); unix_timestamp('2038-02-10 01:00:00') 0 select unix_timestamp('1969-11-20 01:00:00'); unix_timestamp('1969-11-20 01:00:00') 0 select unix_timestamp('2038-01-20 01:00:00'); unix_timestamp('2038-01-20 01:00:00') 0 select unix_timestamp('1969-12-30 01:00:00'); unix_timestamp('1969-12-30 01:00:00') 0 select unix_timestamp('2038-01-17 12:00:00'); unix_timestamp('2038-01-17 12:00:00') 2147331600 select unix_timestamp('1970-01-01 03:00:01'); unix_timestamp('1970-01-01 03:00:01') 1 select unix_timestamp('2038-01-19 07:14:07'); unix_timestamp('2038-01-19 07:14:07') 0 SELECT CHARSET(DAYNAME(19700101)); CHARSET(DAYNAME(19700101)) latin1 SELECT CHARSET(MONTHNAME(19700101)); CHARSET(MONTHNAME(19700101)) latin1 SELECT LOWER(DAYNAME(19700101)); LOWER(DAYNAME(19700101)) thursday SELECT LOWER(MONTHNAME(19700101)); LOWER(MONTHNAME(19700101)) january SELECT COERCIBILITY(MONTHNAME('1970-01-01')),COERCIBILITY(DAYNAME('1970-01-01')); COERCIBILITY(MONTHNAME('1970-01-01')) COERCIBILITY(DAYNAME('1970-01-01')) 4 4 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 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 select timestampadd(SQL_TSI_SECOND, 1, date) from t1; timestampadd(SQL_TSI_SECOND, 1, date) 2003-01-02 00:00:01 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_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) 06:07:09 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 YES NULL 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#1 */ select period_add('9602',-(12)) AS `period_add("9602",-12)`,period_diff(199505,'9404') AS `period_diff(199505,"9404")`,from_days(to_days('960101')) AS `from_days(to_days("960101"))`,dayofmonth('1997-01-02') AS `dayofmonth("1997-01-02")`,month('1997-01-02') AS `month("1997-01-02")`,monthname('1972-03-04') AS `monthname("1972-03-04")`,dayofyear('0000-00-00') AS `dayofyear("0000-00-00")`,hour('1997-03-03 23:03:22') AS `HOUR("1997-03-03 23:03:22")`,minute('23:03:22') AS `MINUTE("23:03:22")`,second(230322) AS `SECOND(230322)`,quarter(980303) AS `QUARTER(980303)`,week('1998-03-03',0) AS `WEEK("1998-03-03")`,yearweek('2000-01-01',1) AS `yearweek("2000-01-01",1)`,week(19950101,1) AS `week(19950101,1)`,year('98-02-03') AS `year("98-02-03")`,(weekday(curdate()) - weekday(now())) AS `weekday(curdate())-weekday(now())`,dayname('1962-03-03') AS `dayname("1962-03-03")`,unix_timestamp() AS `unix_timestamp()`,sec_to_time((time_to_sec('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('1997-01-02 03:04:05','%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('1994-03-02 10:11:12')) AS `from_unixtime(unix_timestamp("1994-03-02 10:11:12"))`,('1997-12-31 23:59:59' + interval 1 second) AS `"1997-12-31 23:59:59" + INTERVAL 1 SECOND`,('1998-01-01 00:00:00' - interval 1 second) AS `"1998-01-01 00:00:00" - INTERVAL 1 SECOND`,('1997-12-31' + interval 1 day) AS `INTERVAL 1 DAY + "1997-12-31"`,extract(year from '1999-01-02 10:11:12') AS `extract(YEAR FROM "1999-01-02 10:11:12")`,('1997-12-31 23:59:59' + interval 1 second) AS `date_add("1997-12-31 23:59:59",INTERVAL 1 SECOND)` SET @TMP='2007-08-01 12:22:49'; CREATE TABLE t1 (d DATETIME); INSERT INTO t1 VALUES ('2007-08-01 12:22:59'); INSERT INTO t1 VALUES ('2007-08-01 12:23:01'); INSERT INTO t1 VALUES ('2007-08-01 12:23:20'); 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') 2005-01-31 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 CAST("2006-1-1" as date) and CAST(20060101 as date); f1 2006-01-01 select f1 from t1 where f1 between cast("2006-1-1" as date) and cast("2006.1.1" as date); f1 2006-01-01 select f1 from t1 where date(f1) between cast("2006-1-1" as date) and cast("2006.1.1" as date); f1 2006-01-01 select f2 from t1 where f2 between cast("12:1:2" as time) and cast("12:2:2" as time); f2 12:01:02 select f2 from t1 where time(f2) between cast("12:1:2" as time) and cast("12:2:2" as time); f2 12:01:02 select f3 from t1 where f3 between cast("2006-1-1 12:1:1" as datetime) and cast("2006-1-1 12:1:2" as datetime); f3 2006-01-01 12:01:01 select f3 from t1 where timestamp(f3) between cast("2006-1-1 12:1:1" as datetime) and cast("2006-1-1 12:1:2" as datetime); f3 2006-01-01 12:01:01 select f1 from t1 where cast("2006-1-1" as date) between f1 and f3; f1 2006-01-01 select f1 from t1 where cast("2006-1-1" as date) between date(f1) and date(f3); f1 2006-01-01 select f1 from t1 where cast("2006-1-1" as date) between f1 and cast('zzz' as date); f1 Warnings: Warning 1292 Incorrect datetime 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()` bigint(16) NOT NULL DEFAULT '0', `curtime() - curtime()` int(9) NOT NULL DEFAULT '0', `sec_to_time(1) + 0` int(9) DEFAULT NULL, `from_unixtime(1) + 0` bigint(16) DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 drop table t1; SELECT SEC_TO_TIME(3300000); SEC_TO_TIME(3300000) 838:59:59 Warnings: Warning 1292 Truncated incorrect time value: '3300000' SELECT SEC_TO_TIME(3300000)+0; SEC_TO_TIME(3300000)+0 8385959 Warnings: Warning 1292 Truncated incorrect time value: '3300000' SELECT SEC_TO_TIME(3600 * 4294967296); SEC_TO_TIME(3600 * 4294967296) 838:59:59 Warnings: Warning 1292 Truncated incorrect time value: '15461882265600' SELECT TIME_TO_SEC('916:40:00'); TIME_TO_SEC('916:40:00') 3020399 Warnings: Warning 1292 Truncated incorrect time value: '916:40:00' SELECT ADDTIME('500:00:00', '416:40:00'); ADDTIME('500:00:00', '416:40:00') 838:59:59 Warnings: Warning 1292 Truncated incorrect time value: '916:40:00' SELECT ADDTIME('916:40:00', '416:40:00'); ADDTIME('916:40:00', '416:40:00') 838:59:59 Warnings: Warning 1292 Truncated incorrect time value: '916:40:00' Warning 1292 Truncated incorrect time value: '1255:39:59' SELECT SUBTIME('916:40:00', '416:40:00'); SUBTIME('916:40:00', '416:40:00') 422:19:59 Warnings: Warning 1292 Truncated incorrect time value: '916:40:00' SELECT SUBTIME('-916:40:00', '416:40:00'); SUBTIME('-916:40:00', '416:40:00') -838:59:59 Warnings: Warning 1292 Truncated incorrect time value: '-916:40:00' Warning 1292 Truncated incorrect time value: '-1255:39:59' SELECT MAKETIME(916,0,0); MAKETIME(916,0,0) 838:59:59 Warnings: Warning 1292 Truncated incorrect time value: '916:00:00' SELECT MAKETIME(4294967296, 0, 0); MAKETIME(4294967296, 0, 0) 838:59:59 Warnings: Warning 1292 Truncated incorrect time value: '4294967296:00:00' SELECT MAKETIME(-4294967296, 0, 0); MAKETIME(-4294967296, 0, 0) -838:59:59 Warnings: Warning 1292 Truncated incorrect time value: '-4294967296:00:00' SELECT MAKETIME(0, 4294967296, 0); MAKETIME(0, 4294967296, 0) NULL SELECT MAKETIME(0, 0, 4294967296); MAKETIME(0, 0, 4294967296) NULL SELECT MAKETIME(CAST(-1 AS UNSIGNED), 0, 0); MAKETIME(CAST(-1 AS UNSIGNED), 0, 0) 838:59:59 Warnings: Warning 1292 Truncated incorrect time value: '18446744073709551615:00:00' SELECT EXTRACT(HOUR FROM '100000:02:03'); EXTRACT(HOUR FROM '100000:02:03') 838 Warnings: Warning 1292 Truncated incorrect time value: '100000:02:03' CREATE TABLE t1(f1 TIME); INSERT INTO t1 VALUES('916:00:00 a'); Warnings: Warning 1265 Data truncated for column 'f1' at row 1 Warning 1264 Out of range value for column 'f1' at row 1 SELECT * FROM t1; f1 838:59:59 DROP TABLE t1; SELECT SEC_TO_TIME(CAST(-1 AS UNSIGNED)); SEC_TO_TIME(CAST(-1 AS UNSIGNED)) 838:59:59 Warnings: Warning 1292 Truncated incorrect time value: '18446744073709551615' 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; CREATE TABLE t1 ( a TIMESTAMP ); INSERT INTO t1 VALUES (now()), (now()); SELECT 1 FROM t1 ORDER BY MAKETIME(1, 1, a); 1 1 1 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 select last_day('0000-00-00'); last_day('0000-00-00') NULL Warnings: Warning 1292 Incorrect datetime value: '0000-00-00' select isnull(week(now() + 0)), isnull(week(now() + 0.2)), week(20061108), week(20061108.01), week(20061108085411.000002); isnull(week(now() + 0)) isnull(week(now() + 0.2)) week(20061108) week(20061108.01) week(20061108085411.000002) 0 0 45 45 45 Warnings: Warning 1292 Truncated incorrect date value: '20061108.01' End of 4.1 tests 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 SET @old_log_bin_trust_function_creators= @@global.log_bin_trust_function_creators; SET GLOBAL log_bin_trust_function_creators = 1; 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; SET GLOBAL log_bin_trust_function_creators = 0; 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; SET @@global.log_bin_trust_function_creators= @old_log_bin_trust_function_creators; 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; select str_to_date('10:00 PM', '%h:%i %p') + INTERVAL 10 MINUTE; str_to_date('10:00 PM', '%h:%i %p') + INTERVAL 10 MINUTE 22:10:00 create table t1 (field DATE); insert into t1 values ('2006-11-06'); select * from t1 where field < '2006-11-06 04:08:36.0'; field 2006-11-06 select * from t1 where field = '2006-11-06 04:08:36.0'; field select * from t1 where field = '2006-11-06'; field 2006-11-06 select * from t1 where CAST(field as DATETIME) < '2006-11-06 04:08:36.0'; field 2006-11-06 select * from t1 where CAST(field as DATE) < '2006-11-06 04:08:36.0'; field 2006-11-06 drop table t1; CREATE TABLE t1 (a int, t1 time, t2 time, d date, PRIMARY KEY (a)); INSERT INTO t1 VALUES (1, '10:00:00', NULL, NULL), (2, '11:00:00', '11:15:00', '1972-02-06'); SELECT t1, t2, SEC_TO_TIME( TIME_TO_SEC( t2 ) - TIME_TO_SEC( t1 ) ), QUARTER(d) FROM t1; t1 t2 SEC_TO_TIME( TIME_TO_SEC( t2 ) - TIME_TO_SEC( t1 ) ) QUARTER(d) 10:00:00 NULL NULL NULL 11:00:00 11:15:00 00:15:00 1 SELECT t1, t2, SEC_TO_TIME( TIME_TO_SEC( t2 ) - TIME_TO_SEC( t1 ) ), QUARTER(d) FROM t1 ORDER BY a DESC; t1 t2 SEC_TO_TIME( TIME_TO_SEC( t2 ) - TIME_TO_SEC( t1 ) ) QUARTER(d) 11:00:00 11:15:00 00:15:00 1 10:00:00 NULL NULL NULL DROP TABLE t1; SELECT TIME_FORMAT(SEC_TO_TIME(a),"%H:%i:%s") FROM (SELECT 3020399 AS a UNION SELECT 3020398 ) x GROUP BY 1; TIME_FORMAT(SEC_TO_TIME(a),"%H:%i:%s") 838:59:58 838:59:59 set names latin1; create table t1 (a varchar(15) character set ascii not null); insert into t1 values ('070514-000000'); select concat(a,ifnull(min(date_format(now(), '%Y-%m-%d')),' ull')) from t1; concat(a,ifnull(min(date_format(now(), '%Y-%m-%d')),' ull')) # set names swe7; select concat(a,ifnull(min(date_format(now(), '%Y-%m-%d')),' ull')) from t1; ERROR HY000: Illegal mix of collations (ascii_general_ci,IMPLICIT) and (swe7_swedish_ci,COERCIBLE) for operation 'concat' set names latin1; set lc_time_names=fr_FR; select concat(a,ifnull(min(date_format(now(), '%Y-%m-%d')),' ull')) from t1; ERROR HY000: Illegal mix of collations (ascii_general_ci,IMPLICIT) and (latin1_swedish_ci,COERCIBLE) for operation 'concat' set lc_time_names=en_US; drop table t1; select DATE_ADD('20071108181000', INTERVAL 1 DAY); DATE_ADD('20071108181000', INTERVAL 1 DAY) 2007-11-09 18:10:00 select DATE_ADD(20071108181000, INTERVAL 1 DAY); DATE_ADD(20071108181000, INTERVAL 1 DAY) 2007-11-09 18:10:00 select DATE_ADD('20071108', INTERVAL 1 DAY); DATE_ADD('20071108', INTERVAL 1 DAY) 2007-11-09 select DATE_ADD(20071108, INTERVAL 1 DAY); DATE_ADD(20071108, INTERVAL 1 DAY) 2007-11-09 select LAST_DAY('2007-12-06 08:59:19.05') - INTERVAL 1 SECOND; LAST_DAY('2007-12-06 08:59:19.05') - INTERVAL 1 SECOND 2007-12-30 23:59:59 select date_add('1000-01-01 00:00:00', interval '1.03:02:01.05' day_microsecond); date_add('1000-01-01 00:00:00', interval '1.03:02:01.05' day_microsecond) 1000-01-02 03:02:01.050000 select date_add('1000-01-01 00:00:00', interval '1.02' day_microsecond); date_add('1000-01-01 00:00:00', interval '1.02' day_microsecond) 1000-01-01 00:00:01.020000 # # Bug #52315 part 2 : utc_date() crashes when system time > year 2037 # SET TIMESTAMP=-147490000; SELECT UTC_TIMESTAMP(); SET TIMESTAMP=2147483648; SELECT UTC_TIMESTAMP(); SET TIMESTAMP=2147483646; SELECT UTC_TIMESTAMP(); SET TIMESTAMP=2147483647; SELECT UTC_TIMESTAMP(); SET TIMESTAMP=0; SELECT UTC_TIMESTAMP(); SET TIMESTAMP=-1; SELECT UTC_TIMESTAMP(); SET TIMESTAMP=1; SELECT UTC_TIMESTAMP(); SET TIMESTAMP=0; 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) 0049-12-31 23:59:59 select date_sub("0199-01-01 00:00:01",INTERVAL 2 SECOND); date_sub("0199-01-01 00:00:01",INTERVAL 2 SECOND) 0198-12-31 23:59:59 select date_add("0199-12-31 23:59:59",INTERVAL 2 SECOND); date_add("0199-12-31 23:59:59",INTERVAL 2 SECOND) 0200-01-01 00:00:01 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) 0068-12-31 23:59:59 select date_sub("0169-01-01 00:00:01",INTERVAL 2 SECOND); date_sub("0169-01-01 00:00:01",INTERVAL 2 SECOND) 0168-12-31 23:59:59 CREATE TABLE t1(a DOUBLE NOT NULL); INSERT INTO t1 VALUES (0),(9.216e-096); # should not crash SELECT 1 FROM t1 ORDER BY @x:=makedate(a,a); 1 1 1 DROP TABLE t1; # # Bug #52160: crash and inconsistent results when grouping # by a function and column # CREATE TABLE t1(a CHAR(10) NOT NULL); INSERT INTO t1 VALUES (''),(''); SELECT COUNT(*) FROM t1 GROUP BY TIME_TO_SEC(a); COUNT(*) 2 Warnings: Warning 1292 Truncated incorrect time value: '' Warning 1292 Truncated incorrect time value: '' Warning 1292 Truncated incorrect time value: '' DROP TABLE t1; # # Bug#11766112 59151:UNINITIALIZED VALUES IN EXTRACT_DATE_TIME WITH STR_TO_DATE(SPACE(..) ... # SELECT STR_TO_DATE(SPACE(2),'1'); STR_TO_DATE(SPACE(2),'1') 0000-00-00 # # Bug#11765216 58154: UNINITIALIZED VARIABLE FORMAT IN STR_TO_DATE FUNCTION # SET GLOBAL SQL_MODE=''; DO STR_TO_DATE((''), FROM_DAYS(@@GLOBAL.SQL_MODE)); SET GLOBAL SQL_MODE=DEFAULT; # # Bug#11766087 59125: VALGRIND UNINITIALISED VALUE WARNING IN ULL2DEC, LONGLONG2DECIMAL # SELECT FORMAT(YEAR(STR_TO_DATE('',GET_FORMAT(TIME,''))),1); FORMAT(YEAR(STR_TO_DATE('',GET_FORMAT(TIME,''))),1) NULL # # Bug#11766126 59166: ANOTHER DATETIME VALGRIND UNINITIALIZED WARNING # SET @@global.sql_mode=''; SELECT CAST((MONTH(FROM_UNIXTIME(@@GLOBAL.SQL_MODE))) AS BINARY(1025)); CAST((MONTH(FROM_UNIXTIME(@@GLOBAL.SQL_MODE))) AS BINARY(1025)) NULL SET @@global.sql_mode=DEFAULT; # # Bug#11766124 59164: VALGRIND: UNINITIALIZED VALUE IN NUMBER_TO_DATETIME # SELECT ADDDATE(MONTH(FROM_UNIXTIME(NULL)),INTERVAL 1 HOUR); ADDDATE(MONTH(FROM_UNIXTIME(NULL)),INTERVAL 1 HOUR) NULL # # Bug#11889186 60503: CRASH IN MAKE_DATE_TIME WITH DATE_FORMAT / STR_TO_DATE COMBINATION # SELECT DATE_FORMAT('0000-00-11', '%W'); DATE_FORMAT('0000-00-11', '%W') NULL SELECT DATE_FORMAT('0000-00-11', '%a'); DATE_FORMAT('0000-00-11', '%a') NULL SELECT DATE_FORMAT('0000-00-11', '%w'); DATE_FORMAT('0000-00-11', '%w') NULL # # Bug#12403504 AFTER FIX FOR #11889186 : ASSERTION FAILED: DELSUM+(INT) Y/4-TEMP > 0 # SELECT MAKEDATE(11111111,1); MAKEDATE(11111111,1) NULL SELECT WEEK(DATE_ADD(FROM_DAYS(1),INTERVAL 1 MONTH), 1); WEEK(DATE_ADD(FROM_DAYS(1),INTERVAL 1 MONTH), 1) NULL # # Bug#12584302 AFTER FIX FOR #12403504: ASSERTION FAILED: DELSUM+(INT) Y/4-TEMP > 0, # DO WEEK((DATE_ADD((CAST(0 AS DATE)), INTERVAL 1 YEAR_MONTH)), 5); Warnings: Warning 1292 Incorrect datetime value: '0' # # Bug #13098726 MY_TIME.C:786: CALC_DAYNR: ASSERTION `DELSUM+(INT) # Y/4-TEMP > 0' FAILED SELECT SUBTIME('0000-01-00 00:00','00:00'); SUBTIME('0000-01-00 00:00','00:00') NULL SELECT LEAST(TIMESTAMP('0000-01-00','0'),'2011-10-24') > 0; LEAST(TIMESTAMP('0000-01-00','0'),'2011-10-24') > 0 NULL # # BUG#13458237 INCONSISTENT HANDLING OF INVALIDE DATES WITH ZERO DAY # SIMILAR TO '2009-10-00' # SELECT DATE('20091000'), STR_TO_DATE('200910','%Y%m'), LAST_DAY('2009-10-00'), LAST_DAY(DATE('2009-10-00')), LAST_DAY(DATE'2009-10-00'), LAST_DAY(STR_TO_DATE('200910','%Y%m')), WEEK('2009-10-00'), WEEK(DATE('2009-10-00')), WEEK(DATE'2009-10-00'), WEEK(STR_TO_DATE('200910','%Y%m')), WEEKOFYEAR('2009-10-00'), WEEKOFYEAR(DATE('2009-10-00')), WEEKOFYEAR(DATE'2009-10-00'), WEEKOFYEAR(STR_TO_DATE('200910','%Y%m')), DAYOFYEAR('2009-10-00'), DAYOFYEAR(DATE('2009-10-00')), DAYOFYEAR(DATE'2009-10-00'), DAYOFYEAR(STR_TO_DATE('200910','%Y%m')), WEEKDAY('2009-10-00'), WEEKDAY(DATE('2009-10-00')), WEEKDAY(DATE'2009-10-00'), WEEKDAY(STR_TO_DATE('200910','%Y%m')), TO_DAYs('2009-10-00'), TO_DAYs(DATE('2009-10-00')), TO_DAYs(DATE'2009-10-00'), TO_DAYs(STR_TO_DATE('200910','%Y%m')); DATE('20091000') 2009-10-00 STR_TO_DATE('200910','%Y%m') 2009-10-00 LAST_DAY('2009-10-00') 2009-10-31 LAST_DAY(DATE('2009-10-00')) 2009-10-31 LAST_DAY(DATE'2009-10-00') 2009-10-31 LAST_DAY(STR_TO_DATE('200910','%Y%m')) 2009-10-31 WEEK('2009-10-00') NULL WEEK(DATE('2009-10-00')) NULL WEEK(DATE'2009-10-00') NULL WEEK(STR_TO_DATE('200910','%Y%m')) NULL WEEKOFYEAR('2009-10-00') NULL WEEKOFYEAR(DATE('2009-10-00')) NULL WEEKOFYEAR(DATE'2009-10-00') NULL WEEKOFYEAR(STR_TO_DATE('200910','%Y%m')) NULL DAYOFYEAR('2009-10-00') NULL DAYOFYEAR(DATE('2009-10-00')) NULL DAYOFYEAR(DATE'2009-10-00') NULL DAYOFYEAR(STR_TO_DATE('200910','%Y%m')) NULL WEEKDAY('2009-10-00') NULL WEEKDAY(DATE('2009-10-00')) NULL WEEKDAY(DATE'2009-10-00') NULL WEEKDAY(STR_TO_DATE('200910','%Y%m')) NULL TO_DAYs('2009-10-00') NULL TO_DAYs(DATE('2009-10-00')) NULL TO_DAYs(DATE'2009-10-00') NULL TO_DAYs(STR_TO_DATE('200910','%Y%m')) NULL Warnings: Level Warning Code 1292 Message Incorrect datetime value: '2009-10-00' Level Warning Code 1292 Message Incorrect datetime value: '2009-10-00' Level Warning Code 1411 Message Incorrect datetime value: '200910' for function str_to_date Level Warning Code 1292 Message Incorrect datetime value: '2009-10-00' Level Warning Code 1292 Message Incorrect datetime value: '2009-10-00' Level Warning Code 1411 Message Incorrect datetime value: '200910' for function str_to_date Level Warning Code 1292 Message Incorrect datetime value: '2009-10-00' Level Warning Code 1292 Message Incorrect datetime value: '2009-10-00' Level Warning Code 1411 Message Incorrect datetime value: '200910' for function str_to_date Level Warning Code 1292 Message Incorrect datetime value: '2009-10-00' Level Warning Code 1292 Message Incorrect datetime value: '2009-10-00' Level Warning Code 1411 Message Incorrect datetime value: '200910' for function str_to_date Level Warning Code 1292 Message Incorrect datetime value: '2009-10-00' Level Warning Code 1292 Message Incorrect datetime value: '2009-10-00' Level Warning Code 1411 Message Incorrect datetime value: '200910' for function str_to_date SELECT DATE('00000100'), STR_TO_DATE('000001','%Y%m'), LAST_DAY('0000-01-00'), LAST_DAY(DATE('0000-01-00')), LAST_DAY(DATE'0000-01-00'), LAST_DAY(STR_TO_DATE('000001','%Y%m')), WEEK('0000-01-00'), WEEK(DATE('0000-01-00')), WEEK(DATE'0000-01-00'), WEEK(STR_TO_DATE('000001','%Y%m')), WEEKOFYEAR('0000-01-00'), WEEKOFYEAR(DATE('0000-01-00')), WEEKOFYEAR(DATE'0000-01-00'), WEEKOFYEAR(STR_TO_DATE('000001','%Y%m')), DAYOFYEAR('0000-01-00'), DAYOFYEAR(DATE('0000-01-00')), DAYOFYEAR(DATE'0000-01-00'), DAYOFYEAR(STR_TO_DATE('000001','%Y%m')), WEEKDAY('0000-01-00'), WEEKDAY(DATE('0000-01-00')), WEEKDAY(DATE'0000-01-00'), WEEKDAY(STR_TO_DATE('000001','%Y%m')), TO_DAYs('0000-01-00'), TO_DAYs(DATE('0000-01-00')), TO_DAYs(DATE'0000-01-00'), TO_DAYs(STR_TO_DATE('000001','%Y%m')); DATE('00000100') 0000-01-00 STR_TO_DATE('000001','%Y%m') 0000-01-00 LAST_DAY('0000-01-00') 0000-01-31 LAST_DAY(DATE('0000-01-00')) 0000-01-31 LAST_DAY(DATE'0000-01-00') 0000-01-31 LAST_DAY(STR_TO_DATE('000001','%Y%m')) 0000-01-31 WEEK('0000-01-00') NULL WEEK(DATE('0000-01-00')) NULL WEEK(DATE'0000-01-00') NULL WEEK(STR_TO_DATE('000001','%Y%m')) NULL WEEKOFYEAR('0000-01-00') NULL WEEKOFYEAR(DATE('0000-01-00')) NULL WEEKOFYEAR(DATE'0000-01-00') NULL WEEKOFYEAR(STR_TO_DATE('000001','%Y%m')) NULL DAYOFYEAR('0000-01-00') NULL DAYOFYEAR(DATE('0000-01-00')) NULL DAYOFYEAR(DATE'0000-01-00') NULL DAYOFYEAR(STR_TO_DATE('000001','%Y%m')) NULL WEEKDAY('0000-01-00') NULL WEEKDAY(DATE('0000-01-00')) NULL WEEKDAY(DATE'0000-01-00') NULL WEEKDAY(STR_TO_DATE('000001','%Y%m')) NULL TO_DAYs('0000-01-00') NULL TO_DAYs(DATE('0000-01-00')) NULL TO_DAYs(DATE'0000-01-00') NULL TO_DAYs(STR_TO_DATE('000001','%Y%m')) NULL Warnings: Level Warning Code 1292 Message Incorrect datetime value: '0000-01-00' Level Warning Code 1292 Message Incorrect datetime value: '0000-01-00' Level Warning Code 1411 Message Incorrect datetime value: '000001' for function str_to_date Level Warning Code 1292 Message Incorrect datetime value: '0000-01-00' Level Warning Code 1292 Message Incorrect datetime value: '0000-01-00' Level Warning Code 1411 Message Incorrect datetime value: '000001' for function str_to_date Level Warning Code 1292 Message Incorrect datetime value: '0000-01-00' Level Warning Code 1292 Message Incorrect datetime value: '0000-01-00' Level Warning Code 1411 Message Incorrect datetime value: '000001' for function str_to_date Level Warning Code 1292 Message Incorrect datetime value: '0000-01-00' Level Warning Code 1292 Message Incorrect datetime value: '0000-01-00' Level Warning Code 1411 Message Incorrect datetime value: '000001' for function str_to_date Level Warning Code 1292 Message Incorrect datetime value: '0000-01-00' Level Warning Code 1292 Message Incorrect datetime value: '0000-01-00' Level Warning Code 1411 Message Incorrect datetime value: '000001' for function str_to_date End of 5.1 tests # # BUG#43578 "MyISAM&Maria gives wrong rows with range access # ORDER BY DESC on date index" # CREATE TABLE t1(c1 DATE NOT NULL PRIMARY KEY, c2 DATE NULL, c3 INT, INDEX idx2(c2)); SET TIMESTAMP=1235553613; INSERT INTO t1 VALUES(NOW(),NOW(),3), (ADDTIME(NOW(),'1 01:01:01'),ADDTIME(NOW(),'1 01:01:01'),4), (ADDTIME(NOW(),'2 01:01:01'),ADDTIME(NOW(),'2 01:01:01'),5), (ADDTIME(NOW(),'3 01:01:01'),ADDTIME(NOW(),'3 01:01:01'),6); Warnings: Note 1265 Data truncated for column 'c1' at row 1 Note 1265 Data truncated for column 'c2' at row 1 Note 1265 Data truncated for column 'c1' at row 2 Note 1265 Data truncated for column 'c2' at row 2 Note 1265 Data truncated for column 'c1' at row 3 Note 1265 Data truncated for column 'c2' at row 3 Note 1265 Data truncated for column 'c1' at row 4 Note 1265 Data truncated for column 'c2' at row 4 SELECT * FROM t1; c1 c2 c3 2009-02-25 2009-02-25 3 2009-02-26 2009-02-26 4 2009-02-27 2009-02-27 5 2009-02-28 2009-02-28 6 SELECT * FROM t1 WHERE c1 > ADDTIME(NOW(),'1 01:01:01') ORDER BY c1 DESC; c1 c2 c3 2009-02-28 2009-02-28 6 2009-02-27 2009-02-27 5 drop table t1; End of 5.5 tests # # Bug#57039: constant subtime expression returns incorrect result. # CREATE TABLE t1 (`date_date` datetime NOT NULL); INSERT INTO t1 VALUES ('2008-01-03 00:00:00'), ('2008-01-03 00:00:00'); SELECT * FROM t1 WHERE date_date >= subtime(now(), "00:30:00"); date_date SELECT * FROM t1 WHERE date_date <= addtime(date_add("2000-1-1", INTERVAL "1:1:1" HOUR_SECOND), "00:20:00"); date_date DROP TABLE t1; # # Bug#57512 str_to_date crash... # SELECT WEEK(STR_TO_DATE(NULL,0)); WEEK(STR_TO_DATE(NULL,0)) NULL SELECT SUBDATE(STR_TO_DATE(NULL,0), INTERVAL 1 HOUR); SUBDATE(STR_TO_DATE(NULL,0), INTERVAL 1 HOUR) NULL # # BUG#59895 - setting storage engine to null segfaults mysqld # SELECT MONTHNAME(0), MONTHNAME(0) IS NULL, MONTHNAME(0) + 1; MONTHNAME(0) MONTHNAME(0) IS NULL MONTHNAME(0) + 1 NULL 1 NULL SET storage_engine=NULL; ERROR 42000: Variable 'storage_engine' can't be set to the value of 'NULL' # # BUG#13354387 - CRASH IN IN MY_DECIMAL::OPERATOR FOR VIEW AND FUNCTION UNIX_TIMESTAMP # Part1 (5.5) SET time_zone='+03:00'; CREATE TABLE t1 (a DATETIME NOT NULL); INSERT INTO t1 VALUES ('2009-09-20 07:32:39.06'); INSERT INTO t1 VALUES ('0000-00-00 00:00:00.00'); CREATE VIEW v1 AS SELECT * FROM t1; SELECT CAST(UNIX_TIMESTAMP(a) AS DECIMAL(25,3)) AS c1 FROM v1 ORDER BY 1; c1 0.000 1253421159.000 DROP VIEW v1; DROP TABLE t1; SET time_zone=DEFAULT; # # Bug #59686 crash in String::copy() with time data type # SELECT min(timestampadd(month, 1>'', from_days('%Z'))); min(timestampadd(month, 1>'', from_days('%Z'))) NULL Warnings: Warning 1292 Truncated incorrect INTEGER value: '%Z' create table t1(a time); insert into t1 values ('00:00:00'),('00:01:00'); select 1 from t1 where 1 < some (select cast(a as datetime) from t1); 1 1 1 drop table t1; # # End of 5.5 tests # # # Start of 5.6 tests # # # BUG#13545236 - ASSERT IN SEC_SINCE_EPOCH # CREATE TABLE t1 (a TIME NOT NULL); INSERT INTO t1 VALUES ('04:39:24'); INSERT INTO t1 VALUES ('00:20:09'); SELECT a FROM t1 WHERE CONVERT_TZ(TIMESTAMPADD(YEAR , a, TIMESTAMP('0000-00-00')), '+00:00','+00:00'); a SELECT a FROM t1 WHERE CONVERT_TZ(TIMESTAMPADD(YEAR, a, DATE('0000-00-00')), '+00:00','+00:00'); a DROP TABLE t1; CREATE TABLE t1 (a TIME); INSERT INTO t1 VALUES ('00:00:00'); SELECT * FROM t1 WHERE TIMESTAMPDIFF(MONTH, a, TIMESTAMP('0000-00-01')) IS NULL; a 00:00:00 Warnings: Warning 1292 Incorrect datetime value: '0000-00-01' SELECT * FROM t1 WHERE TIMESTAMPDIFF(MONTH, a, TIMESTAMP'0000-00-01 00:00:00') IS NULL; a 00:00:00 SELECT * FROM t1 WHERE TIMESTAMPDIFF(MONTH, a, DATE('0000-00-01')) IS NULL; a 00:00:00 Warnings: Warning 1292 Incorrect datetime value: '0000-00-01' SELECT * FROM t1 WHERE TIMESTAMPDIFF(MONTH, a, DATE'0000-00-01') IS NULL; a 00:00:00 DROP TABLE t1; # # BUG#13354387 - CRASH IN IN MY_DECIMAL::OPERATOR FOR VIEW AND FUNCTION UNIX_TIMESTAMP # Part2 (5.6) CREATE TABLE t1 (a VARCHAR(32) NOT NULL); INSERT INTO t1 VALUES ('a'); SELECT 1 FROM t1 GROUP BY @a:=UNIX_TIMESTAMP(a); 1 1 Warnings: Warning 1292 Incorrect datetime value: 'a' SELECT a, UNIX_TIMESTAMP(a), UNIX_TIMESTAMP('a') FROM t1; a UNIX_TIMESTAMP(a) UNIX_TIMESTAMP('a') a 0.000000 0.000000 Warnings: Warning 1292 Incorrect datetime value: 'a' Warning 1292 Incorrect datetime value: 'a' DELETE FROM t1; INSERT INTO t1 VALUES ('5000-01-01 00:00:00'); SELECT a, UNIX_TIMESTAMP(a), UNIX_TIMESTAMP('5000-01-01 00:00:00') FROM t1; a UNIX_TIMESTAMP(a) UNIX_TIMESTAMP('5000-01-01 00:00:00') 5000-01-01 00:00:00 0.000000 0 DROP TABLE t1; # # Bug#13988413 CRASH/ASSERTION AFTER INVALID MEMORY READ # IN MY_USECONDS_TO_STR # DO contains(1, if(0, coalesce(NULL), now())); CREATE TABLE t1 AS SELECT IF(0, coalesce(NULL), now(0)) + 0; SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( `IF(0, coalesce(NULL), now(0)) + 0` decimal(21,6) DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 DROP TABLE t1; # # Bug#13982125 BUFFER OVERFLOW OF VARIABLE BUF IN # ITEM_FUNC_MAKETIME::GET_TIME # DO maketime(~0, 49, 0.123456789); Warnings: Warning 1292 Truncated incorrect time value: '18446744073709551615:49:00.123456789' # # Bug#14042545 ASSERTION FAILED: DEC <= 6 IN MY_USECONDS_TO_STR() # DO is_used_lock(ifnull(now(), CASE 1 WHEN 1 THEN NULL END));