diff options
Diffstat (limited to 'mysql-test/main/func_time.result')
| -rw-r--r-- | mysql-test/main/func_time.result | 3486 |
1 files changed, 3486 insertions, 0 deletions
diff --git a/mysql-test/main/func_time.result b/mysql-test/main/func_time.result new file mode 100644 index 00000000000..2772f850ce9 --- /dev/null +++ b/mysql-test/main/func_time.result @@ -0,0 +1,3486 @@ +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(9001.1), time_to_sec('15:12:22.123456'), time_to_sec(15.5566778899); +sec_to_time(9001.1) time_to_sec('15:12:22.123456') time_to_sec(15.5566778899) +02:30:01.1 54742.123456 15.556677 +select sec_to_time(time_to_sec('-838:59:59')); +sec_to_time(time_to_sec('-838:59:59')) +-838:59:59 +select sec_to_time('9001.1'), sec_to_time('1234567890123.123'); +sec_to_time('9001.1') sec_to_time('1234567890123.123') +02:30:01.100000 838:59:59.999999 +Warnings: +Warning 1292 Truncated incorrect time value: '1234567890123.123' +select sec_to_time(-9001.1), sec_to_time(-9001.1) / 1, +sec_to_time(-9001.1) / 1e0, sec_to_time(-9001) div 1; +sec_to_time(-9001.1) sec_to_time(-9001.1) / 1 sec_to_time(-9001.1) / 1e0 sec_to_time(-9001) div 1 +-02:30:01.1 -23001.10000 -23001.1 -23001 +select sec_to_time(90011e-1), sec_to_time(1234567890123e30); +sec_to_time(90011e-1) sec_to_time(1234567890123e30) +02:30:01.100000 838:59:59.999999 +Warnings: +Warning 1292 Truncated incorrect time value: '1.234567890123e42' +select sec_to_time(1234567890123), sec_to_time('99999999999999999999999999999'); +sec_to_time(1234567890123) sec_to_time('99999999999999999999999999999') +838:59:59 838:59:59.999999 +Warnings: +Warning 1292 Truncated incorrect time value: '1234567890123' +Warning 1292 Truncated incorrect time value: '99999999999999999999999999999' +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 TIME(230322), TIME(230322.33), TIME("230322.33"); +TIME(230322) TIME(230322.33) TIME("230322.33") +23:03:22 23:03:22.33 23:03:22.33 +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 +Warnings: +Warning 1292 Truncated incorrect DOUBLE value: 'March' +select time_format(000000,'%H|%I|%k|%l|%i|%p|%r|%S|%T'),date_format(19980131000000,'%H|%I|%k|%l|%i|%p|%r|%S|%T'); +time_format(000000,'%H|%I|%k|%l|%i|%p|%r|%S|%T') date_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 00|12|0|12|00|AM|12:00:00 AM|00|00:00:00 +select time_format(010203,'%H|%I|%k|%l|%i|%p|%r|%S|%T'),date_format(19980131010203,'%H|%I|%k|%l|%i|%p|%r|%S|%T'); +time_format(010203,'%H|%I|%k|%l|%i|%p|%r|%S|%T') date_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 01|01|1|1|02|AM|01:02:03 AM|03|01:02:03 +select time_format(131415,'%H|%I|%k|%l|%i|%p|%r|%S|%T'),date_format(19980131131415,'%H|%I|%k|%l|%i|%p|%r|%S|%T'); +time_format(131415,'%H|%I|%k|%l|%i|%p|%r|%S|%T') date_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 13|01|13|1|14|PM|01:14:15 PM|15|13:14:15 +select time_format(010015,'%H|%I|%k|%l|%i|%p|%r|%S|%T'),date_format(19980131010015,'%H|%I|%k|%l|%i|%p|%r|%S|%T'); +time_format(010015,'%H|%I|%k|%l|%i|%p|%r|%S|%T') date_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 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") +34225959 +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') +NULL +select from_unixtime(-1); +from_unixtime(-1) +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') +NULL +select unix_timestamp('1968-01-20 01:00:00'); +unix_timestamp('1968-01-20 01:00:00') +NULL +select unix_timestamp('2038-02-10 01:00:00'); +unix_timestamp('2038-02-10 01:00:00') +NULL +select unix_timestamp('1969-11-20 01:00:00'); +unix_timestamp('1969-11-20 01:00:00') +NULL +select unix_timestamp('2038-01-20 01:00:00'); +unix_timestamp('2038-01-20 01:00:00') +NULL +select unix_timestamp('1969-12-30 01:00:00'); +unix_timestamp('1969-12-30 01:00:00') +NULL +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') +NULL +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 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') 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(current_timestamp()) 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 +select last_day('2005-00-01'); +last_day('2005-00-01') +NULL +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, + `curtime() - curtime()` int(9) NOT NULL, + `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.999999 +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.999999 +Warnings: +Warning 1292 Truncated incorrect time value: '916:40:00' +Warning 1292 Truncated incorrect time value: '1255:39:59.999999' +SELECT SUBTIME('916:40:00', '416:40:00'); +SUBTIME('916:40:00', '416:40:00') +422:19:59.999999 +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.999999 +Warnings: +Warning 1292 Truncated incorrect time value: '-916:40:00' +Warning 1292 Truncated incorrect time value: '-1255:39:59.999999' +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: +Note 1105 Cast to unsigned converted negative integer to it's positive complement +Warning 1292 Truncated incorrect time value: '18446744073709551615:00:00' +SELECT EXTRACT(HOUR FROM '10000:02:03'); +EXTRACT(HOUR FROM '10000:02:03') +22 +Warnings: +Warning 1292 Truncated incorrect time value: '10000:02:03' +CREATE TABLE t1(f1 TIME); +INSERT IGNORE 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: +Note 1105 Cast to unsigned converted negative integer to it's positive complement +Note 1105 Cast to unsigned converted negative integer to it's positive complement +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 +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 datetime 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(2), 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 +select str_to_date("1997-00-04 22:23:00","%Y-%m-%D") + interval 10 minute; +str_to_date("1997-00-04 22:23:00","%Y-%m-%D") + interval 10 minute +NULL +Warnings: +Warning 1292 Truncated incorrect date value: '1997-00-04 22:23:00' +Warning 1292 Incorrect datetime value: '1997-00-04' +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 +# +SELECT CAST((MONTH(FROM_UNIXTIME(@@GLOBAL.SQL_MODE))) AS BINARY(1025)); +# +# 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 +Warnings: +Warning 1292 Incorrect datetime value: '0000-00-00' +# +# 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: '0000-00-00' +# +# 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 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 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 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 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 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 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 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 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 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 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#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 +NULL +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' +Warning 1292 Truncated incorrect DOUBLE value: '' +Warning 1292 Incorrect datetime value: '0000-00-00' +SET timestamp=UNIX_TIMESTAMP('2001-01-01 00:00:00'); +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 +Warnings: +Warning 1292 Incorrect datetime value: '1' +drop table t1; +SET timestamp=DEFAULT; +# +# Bug #21564557: INCONSISTENT OUTPUT FROM 5.5 AND 5.6 +# UNIX_TIMESTAMP(STR_TO_DATE('201506', "%Y%M" +# +SELECT UNIX_TIMESTAMP(STR_TO_DATE('201506', "%Y%m")); +UNIX_TIMESTAMP(STR_TO_DATE('201506', "%Y%m")) +NULL +Warnings: +Warning 1411 Incorrect datetime value: '201506' for function str_to_date +SELECT UNIX_TIMESTAMP('2015-06-00'); +UNIX_TIMESTAMP('2015-06-00') +NULL +Warnings: +Warning 1292 Incorrect datetime value: '2015-06-00' +SELECT UNIX_TIMESTAMP(STR_TO_DATE('0000-00-00 10:30:30', '%Y-%m-%d %h:%i:%s')); +UNIX_TIMESTAMP(STR_TO_DATE('0000-00-00 10:30:30', '%Y-%m-%d %h:%i:%s')) +NULL +Warnings: +Warning 1411 Incorrect datetime value: '0000-00-00 10:30:30' for function str_to_date +set sql_mode= 'TRADITIONAL'; +SELECT @@sql_mode; +@@sql_mode +STRICT_TRANS_TABLES,STRICT_ALL_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,TRADITIONAL,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION +SELECT UNIX_TIMESTAMP(STR_TO_DATE('201506', "%Y%m")); +UNIX_TIMESTAMP(STR_TO_DATE('201506', "%Y%m")) +NULL +Warnings: +Warning 1411 Incorrect datetime value: '201506' for function str_to_date +SELECT UNIX_TIMESTAMP('2015-06-00'); +UNIX_TIMESTAMP('2015-06-00') +NULL +Warnings: +Warning 1292 Incorrect datetime value: '2015-06-00' +SELECT UNIX_TIMESTAMP(STR_TO_DATE('0000-00-00 10:30:30', '%Y-%m-%d %h:%i:%s')); +UNIX_TIMESTAMP(STR_TO_DATE('0000-00-00 10:30:30', '%Y-%m-%d %h:%i:%s')) +NULL +Warnings: +Warning 1411 Incorrect datetime value: '0000-00-00 10:30:30' for function str_to_date +set sql_mode= default; +select time('10:10:10') > 10; +time('10:10:10') > 10 +1 +select time('10:10:10') > 1010; +time('10:10:10') > 1010 +1 +select time('10:10:09') > 101010; +time('10:10:09') > 101010 +0 +select time('10:10:10') > 101010; +time('10:10:10') > 101010 +0 +select time('10:10:11') > 101010; +time('10:10:11') > 101010 +1 +select time(' 1 02:03:04') + interval 9 microsecond; +time(' 1 02:03:04') + interval 9 microsecond +26:03:04.000009 +select time(' 1 02:03:04') - interval 9 microsecond; +time(' 1 02:03:04') - interval 9 microsecond +26:03:03.999991 +select time('-1 02:03:04') + interval 9 microsecond; +time('-1 02:03:04') + interval 9 microsecond +-26:03:03.999991 +select time('-1 02:03:04') - interval 9 microsecond; +time('-1 02:03:04') - interval 9 microsecond +-26:03:04.000009 +select time(' 1 02:03:04') + interval '4:4:4' hour_second; +time(' 1 02:03:04') + interval '4:4:4' hour_second +30:07:08 +select time(' 1 02:03:04') - interval '4:4:4' hour_second; +time(' 1 02:03:04') - interval '4:4:4' hour_second +21:59:00 +select time('-1 02:03:04') + interval '4:4:4' hour_second; +time('-1 02:03:04') + interval '4:4:4' hour_second +-21:59:00 +select time('-1 02:03:04') - interval '4:4:4' hour_second; +time('-1 02:03:04') - interval '4:4:4' hour_second +-30:07:08 +select time(' 1 02:03:04') + interval 2 day; +time(' 1 02:03:04') + interval 2 day +74:03:04 +select time(' 1 02:03:04') - interval 2 day; +time(' 1 02:03:04') - interval 2 day +-21:56:56 +select time('-1 02:03:04') + interval 2 day; +time('-1 02:03:04') + interval 2 day +21:56:56 +select time('-1 02:03:04') - interval 2 day; +time('-1 02:03:04') - interval 2 day +-74:03:04 +select time('10 02:03:04') + interval 30 day; +time('10 02:03:04') + interval 30 day +NULL +Warnings: +Warning 1441 Datetime function: time field overflow +select time('10 02:03:04') + interval 1 year; +time('10 02:03:04') + interval 1 year +NULL +Warnings: +Warning 1441 Datetime function: time field overflow +select cast('131415.123e0' as time); +cast('131415.123e0' as time) +NULL +Warnings: +Warning 1292 Truncated incorrect time value: '131415.123e0' +select cast('2010-01-02 03:04:05' as datetime) between null and '2010-01-02 03:04:04'; +cast('2010-01-02 03:04:05' as datetime) between null and '2010-01-02 03:04:04' +0 +select least(time('1:2:3'), '01:02:04', null) div 1; +least(time('1:2:3'), '01:02:04', null) div 1 +NULL +select truncate(least(time('1:2:3'), '01:02:04', null), 6); +truncate(least(time('1:2:3'), '01:02:04', null), 6) +NULL +select cast(least(time('1:2:3'), '01:02:04', null) as decimal(3,1)); +cast(least(time('1:2:3'), '01:02:04', null) as decimal(3,1)) +NULL +select unix_timestamp(null); +unix_timestamp(null) +NULL +select truncate(date('2010-40-10'), 6); +truncate(date('2010-40-10'), 6) +NULL +Warnings: +Warning 1292 Incorrect datetime value: '2010-40-10' +select extract(month from '2010-40-50'); +extract(month from '2010-40-50') +NULL +Warnings: +Warning 1292 Incorrect datetime value: '2010-40-50' +select subtime('0000-00-10 10:10:10', '30 10:00:00'); +subtime('0000-00-10 10:10:10', '30 10:00:00') +NULL +select cast(str_to_date(NULL, '%H:%i:%s') as time); +cast(str_to_date(NULL, '%H:%i:%s') as time) +NULL +create table t1 (f1 datetime, key (f1)); +insert into t1 values ('2000-09-12 00:00:00'), ('2007-04-25 05:08:49'); +select * from t1 where f1 > time('-23:00:06'); +f1 +drop table t1; +select maketime(20,61,10)+0; +maketime(20,61,10)+0 +NULL +create table t1 (f2 int not null) ; +insert into t1 values (0),(0); +select last_day(f2) from t1; +last_day(f2) +NULL +NULL +select last_day(f2) from t1 where last_day(f2) is null; +last_day(f2) +NULL +NULL +select * from t1 order by last_day (f2); +f2 +0 +0 +drop table t1; +SET timestamp=unix_timestamp('2001-02-03 10:20:30'); +select convert_tz(timediff('0000-00-00 00:00:00', cast('2008-03-26 07:09:06' as datetime)), 'UTC', 'Europe/Moscow'); +convert_tz(timediff('0000-00-00 00:00:00', cast('2008-03-26 07:09:06' as datetime)), 'UTC', 'Europe/Moscow') +NULL +SET timestamp=DEFAULT; +create table t1 (f1 integer, f2 date); +insert into t1 values (1,'2011-05-05'),(2,'2011-05-05'),(3,'2011-05-05'),(4,'2011-05-05'),(5,'2011-05-05'),(6, '2011-05-06'); +select * from t1 where 1 and concat(f2)=MAKEDATE(2011, 125); +f1 f2 +1 2011-05-05 +2 2011-05-05 +3 2011-05-05 +4 2011-05-05 +5 2011-05-05 +drop table t1; +create table t1 (f1 timestamp); +insert into t1 values ('0000-00-00 00:00:00'); +select least(1, f1) from t1; +least(1, f1) +0000-00-00 00:00:00 +Warnings: +Warning 1292 Incorrect datetime value: '1' +drop table t1; +SET timestamp=UNIX_TIMESTAMP('2014-04-14 10:10:10'); +select now() > coalesce(time('21:43:24'), date('2010-05-03')); +now() > coalesce(time('21:43:24'), date('2010-05-03')) +0 +SET timestamp=UNIX_TIMESTAMP('2014-04-14 22:22:22'); +select now() > coalesce(time('21:43:24'), date('2010-05-03')); +now() > coalesce(time('21:43:24'), date('2010-05-03')) +1 +SET timestamp=DEFAULT; +create table t1 (f1 timestamp); +select * from t1 where f1 > f1 and f1 <=> timestampadd(hour, 9 , '2010-01-01 16:55:35'); +f1 +drop table t1; +create table t1 (f1 date); +insert into t1 values ('0000-00-00'); +select timestampadd(week, 1, f1) from t1; +timestampadd(week, 1, f1) +NULL +Warnings: +Warning 1292 Incorrect datetime value: '0000-00-00' +select timestampadd(week, 1, date("0000-00-00")); +timestampadd(week, 1, date("0000-00-00")) +NULL +Warnings: +Warning 1292 Incorrect datetime value: '0000-00-00' +drop table t1; +create table t1 (f2 time not null, f3 datetime, f4 int not null, f5 timestamp); +insert ignore t1 values ('04:38:11','0000-00-00 00:00:00',0,'0000-00-00 00:00:00'); +select least(greatest(f3, f2, f4), f5) from t1; +least(greatest(f3, f2, f4), f5) +0000-00-00 00:00:00 +drop table t1; +select day(coalesce(null)); +day(coalesce(null)) +NULL +select timestamp(greatest('2002-08-20', '0000-00-00 00:00:00')); +timestamp(greatest('2002-08-20', '0000-00-00 00:00:00')) +2002-08-20 00:00:00 +create table t1 (f1 datetime); +insert into t1 values ('0000-00-00 00:00:00'); +select cast(f1 AS time) from t1; +cast(f1 AS time) +00:00:00 +drop table t1; +SET timestamp=UNIX_TIMESTAMP('2014-06-01 10:20:30'); +select greatest(cast("0-0-0" as date), cast("10:20:05" as time)); +greatest(cast("0-0-0" as date), cast("10:20:05" as time)) +2014-06-01 10:20:05 +select greatest(cast("0-0-0" as date), cast("10:20:05" as time)) = '0000-00-00'; +greatest(cast("0-0-0" as date), cast("10:20:05" as time)) = '0000-00-00' +0 +select greatest(cast("0-0-0" as date), cast("10:20:05" as time)) = '2014-06-01'; +greatest(cast("0-0-0" as date), cast("10:20:05" as time)) = '2014-06-01' +0 +select greatest(cast("0-0-0" as date), cast("10:20:05" as time)) = '2014-06-01 10:20:05'; +greatest(cast("0-0-0" as date), cast("10:20:05" as time)) = '2014-06-01 10:20:05' +1 +select cast(greatest(cast("0-0-0" as date), cast("10:20:05" as time)) as datetime(6)); +cast(greatest(cast("0-0-0" as date), cast("10:20:05" as time)) as datetime(6)) +2014-06-01 10:20:05.000000 +SET timestamp=DEFAULT; +select microsecond('12:00:00.123456'), microsecond('2009-12-31 23:59:59.000010'); +microsecond('12:00:00.123456') microsecond('2009-12-31 23:59:59.000010') +123456 10 +select now(258); +ERROR 42000: Too big precision 258 specified for 'current_timestamp'. Maximum is 6 +SELECT 1 FROM DUAL WHERE YEAR(TIMEDIFF(NULL, '12:12:12')); +1 +SELECT 1 FROM DUAL WHERE MONTH(TIMEDIFF(NULL, '12:12:12')); +1 +SELECT 1 FROM DUAL WHERE DAYOFMONTH(TIMEDIFF(NULL, '12:12:12')); +1 +SELECT 1 FROM DUAL WHERE HOUR(TIMEDIFF(NULL, '12:12:12')); +1 +SELECT 1 FROM DUAL WHERE MINUTE(TIMEDIFF(NULL, '12:12:12')); +1 +SELECT 1 FROM DUAL WHERE SECOND(TIMEDIFF(NULL, '12:12:12')); +1 +# +# MDEV-4511 Assertion `scale <= precision' fails on GROUP BY TIMEDIFF with incorrect types +# +CREATE TABLE t1 (a DATE) ENGINE=MyISAM; +INSERT INTO t1 VALUES ('2005-05-04'),('2000-02-23'); +SELECT a FROM t1 GROUP BY TIMEDIFF('2004-06-12',a) * 1; +a +2005-05-04 +Warnings: +Warning 1292 Truncated incorrect time value: '2004-06-12' +Warning 1292 Truncated incorrect time value: '2004-06-12' +DROP TABLE t1; +CREATE TABLE t1 (a DATE) ENGINE=MyISAM; +INSERT INTO t1 VALUES ('2005-05-04'),('2000-02-23'); +SELECT a FROM t1 GROUP BY ADDTIME(a,'10')*1; +a +2000-02-23 +2005-05-04 +DROP TABLE t1; +CREATE TABLE t1 (a DATE) ENGINE=MyISAM; +INSERT INTO t1 VALUES ('2005-05-04'),('2000-02-23'); +SELECT * FROM t1 GROUP BY SEC_TO_TIME(concat(a,'10'))*1; +a +2000-02-23 +2005-05-04 +Warnings: +Warning 1292 Truncated incorrect DECIMAL value: '2005-05-0410' +Warning 1292 Truncated incorrect DECIMAL value: '2000-02-2310' +DROP TABLE t1; +CREATE TABLE t1 (a DATE) ENGINE=MyISAM; +INSERT INTO t1 VALUES ('2005-05-04'),('2000-02-23'); +SELECT * FROM t1 GROUP BY ADDTIME(timestamp('2001-01-01 00:00:00'),CAST(a AS SIGNED)&0xF)*1; +a +2005-05-04 +2000-02-23 +DROP TABLE t1; +CREATE TABLE t1 (a DATE) ENGINE=MyISAM; +INSERT INTO t1 VALUES ('2005-05-04'),('2000-02-23'); +SELECT * FROM t1 GROUP BY STR_TO_DATE(a,concat('%Y-%m-%d.%f',if(rand(),'','')))*1; +a +2000-02-23 +2005-05-04 +DROP TABLE t1; +CREATE TABLE t1 AS SELECT +STR_TO_DATE('2001-01-01', '%Y-%m-%d') AS date_only, +STR_TO_DATE('10:10:10', '%H:%i:%s') AS time_only, +STR_TO_DATE('10:10:10.123', '%H:%i:%s.%f') AS time_microsecond, +STR_TO_DATE('2001-01-01 10:10:10', '%Y-%m-%d %H:%i:%s') AS date_time, +STR_TO_DATE('2001-01-01 10:10:10.123', '%Y-%m-%d %H:%i:%s.%f') AS date_time_microsecond; +SHOW COLUMNS FROM t1; +Field Type Null Key Default Extra +date_only date YES NULL +time_only time YES NULL +time_microsecond time(6) YES NULL +date_time datetime YES NULL +date_time_microsecond datetime(6) YES NULL +DROP TABLE t1; +CREATE TABLE t1 AS SELECT +SEC_TO_TIME(1)+0.1, +SEC_TO_TIME(1.1)+0.1, +SEC_TO_TIME(1.12)+0.1, +SEC_TO_TIME(1.123456)+0.1, +SEC_TO_TIME(1.1234567)+0.1; +SHOW COLUMNS FROM t1; +Field Type Null Key Default Extra +SEC_TO_TIME(1)+0.1 decimal(9,1) YES NULL +SEC_TO_TIME(1.1)+0.1 decimal(9,1) YES NULL +SEC_TO_TIME(1.12)+0.1 decimal(10,2) YES NULL +SEC_TO_TIME(1.123456)+0.1 decimal(14,6) YES NULL +SEC_TO_TIME(1.1234567)+0.1 decimal(14,6) YES NULL +DROP TABLE t1; +CREATE TABLE t1 (a DATE) ENGINE=MyISAM; +INSERT INTO t1 VALUES ('2005-05-04'),('2000-02-23'); +SELECT * FROM t1 GROUP BY FROM_UNIXTIME(concat(a,'10'))*1; +a +2000-02-23 +2005-05-04 +Warnings: +Warning 1292 Truncated incorrect DECIMAL value: '2005-05-0410' +Warning 1292 Truncated incorrect DECIMAL value: '2000-02-2310' +SELECT * FROM t1 GROUP BY (-FROM_UNIXTIME(concat(a,'10')))*1; +a +2005-05-04 +2000-02-23 +Warnings: +Warning 1292 Truncated incorrect DECIMAL value: '2005-05-0410' +Warning 1292 Truncated incorrect DECIMAL value: '2000-02-2310' +SELECT * FROM t1 GROUP BY (-FROM_UNIXTIME(concat(a,'10'))); +a +2005-05-04 +2000-02-23 +Warnings: +Warning 1292 Truncated incorrect DECIMAL value: '2005-05-0410' +Warning 1292 Truncated incorrect DECIMAL value: '2000-02-2310' +SELECT * FROM t1 GROUP BY ABS(FROM_UNIXTIME(concat(a,'10'))); +a +2000-02-23 +2005-05-04 +Warnings: +Warning 1292 Truncated incorrect DECIMAL value: '2005-05-0410' +Warning 1292 Truncated incorrect DECIMAL value: '2000-02-2310' +SELECT * FROM t1 GROUP BY @a:=(FROM_UNIXTIME(concat(a,'10'))*1); +a +2000-02-23 +2005-05-04 +Warnings: +Warning 1292 Truncated incorrect DECIMAL value: '2005-05-0410' +Warning 1292 Truncated incorrect DECIMAL value: '2000-02-2310' +DROP TABLE t1; +SET TIME_ZONE='+02:00'; +# +# MDEV-6302 Wrong result set when using GROUP BY FROM_UNIXTIME(...)+0 +# +CREATE TABLE t1 (a DATE); +INSERT INTO t1 VALUES ('2005-05-04'),('2000-02-23'); +SELECT a, FROM_UNIXTIME(CONCAT(a,'10')) AS f1, FROM_UNIXTIME(CONCAT(a,'10'))+0 AS f2 FROM t1; +a f1 f2 +2005-05-04 1970-01-01 02:33:25 19700101023325.000000 +2000-02-23 1970-01-01 02:33:20 19700101023320.000000 +Warnings: +Warning 1292 Truncated incorrect DECIMAL value: '2005-05-0410' +Warning 1292 Truncated incorrect DECIMAL value: '2005-05-0410' +Warning 1292 Truncated incorrect DECIMAL value: '2000-02-2310' +Warning 1292 Truncated incorrect DECIMAL value: '2000-02-2310' +SELECT * FROM t1 GROUP BY FROM_UNIXTIME(CONCAT(a,'10'))+0; +a +2000-02-23 +2005-05-04 +Warnings: +Warning 1292 Truncated incorrect DECIMAL value: '2005-05-0410' +Warning 1292 Truncated incorrect DECIMAL value: '2000-02-2310' +DROP TABLE t1; +CREATE TABLE t1 (a DATE) ENGINE=MyISAM; +INSERT INTO t1 VALUES ('2005-05-04'),('2000-02-23'); +SELECT * FROM t1 GROUP BY FROM_UNIXTIME(concat(a,'10'))/1; +a +2000-02-23 +2005-05-04 +Warnings: +Warning 1292 Truncated incorrect DECIMAL value: '2005-05-0410' +Warning 1292 Truncated incorrect DECIMAL value: '2000-02-2310' +DROP TABLE t1; +CREATE TABLE t1 (a DATE); +INSERT INTO t1 VALUES ('2005-05-04'); +SELECT CONCAT(FROM_UNIXTIME(CONCAT(a,'10')) MOD FROM_UNIXTIME(CONCAT(a,'10'))) AS f2 FROM t1; +f2 +0.000000 +Warnings: +Warning 1292 Truncated incorrect DECIMAL value: '2005-05-0410' +Warning 1292 Truncated incorrect DECIMAL value: '2005-05-0410' +SELECT CHAR_LENGTH(CONCAT(FROM_UNIXTIME(CONCAT(a,'10')) MOD FROM_UNIXTIME(CONCAT(a,'10')))) AS f2 FROM t1; +f2 +8 +Warnings: +Warning 1292 Truncated incorrect DECIMAL value: '2005-05-0410' +Warning 1292 Truncated incorrect DECIMAL value: '2005-05-0410' +SET STATEMENT sql_mode = 'NO_ENGINE_SUBSTITUTION' FOR +CREATE TABLE t2 AS SELECT CONCAT(FROM_UNIXTIME(CONCAT(a,'10')) MOD FROM_UNIXTIME(CONCAT(a,'10'))) AS f2 FROM t1; +Warnings: +Warning 1292 Truncated incorrect DECIMAL value: '2005-05-0410' +Warning 1292 Truncated incorrect DECIMAL value: '2005-05-0410' +SHOW CREATE TABLE t2; +Table Create Table +t2 CREATE TABLE `t2` ( + `f2` varchar(26) DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +SELECT * FROM t2; +f2 +0.000000 +DROP TABLE t1,t2; +# +# MDEV-4635 Crash in UNIX_TIMESTAMP(STR_TO_DATE('2020','%Y')) +# +SELECT UNIX_TIMESTAMP(STR_TO_DATE('2020','%Y')); +UNIX_TIMESTAMP(STR_TO_DATE('2020','%Y')) +NULL +Warnings: +Warning 1411 Incorrect datetime value: '2020' for function str_to_date +SET TIME_ZONE=DEFAULT; +# +# MDEV-4863 COALESCE(time_or_datetime) returns wrong results in numeric context +# +CREATE TABLE t1 (a TIMESTAMP(3)); +INSERT INTO t1 VALUES ('2001-01-01 10:20:30.999'); +SELECT CAST(COALESCE(a,a) AS SIGNED) AS c1, CAST(COALESCE(a,a) AS DECIMAL(25,3)) AS c2, ROUND(COALESCE(a,a)) AS c2 FROM t1; +c1 c2 c2 +20010101102030 20010101102030.999 20010101102031 +DROP TABLE t1; +CREATE TABLE t1 (a TIME(3)); +INSERT INTO t1 VALUES ('10:20:30.999'); +SELECT CAST(COALESCE(a,a) AS SIGNED) AS c1, CAST(COALESCE(a,a) AS DECIMAL(25,3)) AS c2, ROUND(COALESCE(a,a)) AS c2 FROM t1; +c1 c2 c2 +102030 102030.999 102031 +DROP TABLE t1; +SELECT +CAST(COALESCE(DATE('2001-01-01'),TIMESTAMP('2001-01-02 10:20:30')) AS SIGNED) AS c1, +CAST(COALESCE(DATE('2001-01-01'),TIMESTAMP('2001-01-02 10:20:30')) AS DECIMAL(25,4)) AS c2, +COALESCE(DATE('2001-01-01'),TIMESTAMP('2001-01-02 10:20:30'))+0e0 AS c3, +CONCAT(COALESCE(DATE('2001-01-01'),TIMESTAMP('2001-01-02 10:20:30'))) AS c4, +TIME(COALESCE(DATE('2001-01-01'),TIMESTAMP('2001-01-02 10:20:30'))) AS c5, +DATE(COALESCE(DATE('2001-01-01'),TIMESTAMP('2001-01-02 10:20:30'))) AS c6, +TIMESTAMP(COALESCE(DATE('2001-01-01'),TIMESTAMP('2001-01-02 10:20:30'))) AS c7; +c1 c2 c3 c4 c5 c6 c7 +20010101000000 20010101000000.0000 20010101000000 2001-01-01 00:00:00 00:00:00 2001-01-01 2001-01-01 00:00:00 +SELECT +CAST(COALESCE(TIMESTAMP('2001-01-02 10:20:30'),DATE('2001-01-01')) AS SIGNED) AS c1, +CAST(COALESCE(TIMESTAMP('2001-01-02 10:20:30'),DATE('2001-01-01')) AS DECIMAL(25,4)) AS c2, +COALESCE(TIMESTAMP('2001-01-02 10:20:30'),DATE('2001-01-01'))+0e0 AS c3, +CONCAT(COALESCE(TIMESTAMP('2001-01-02 10:20:30'),DATE('2001-01-01'))) AS c4, +TIME(COALESCE(TIMESTAMP('2001-01-02 10:20:30'),DATE('2001-01-01'))) AS c5, +DATE(COALESCE(TIMESTAMP('2001-01-02 10:20:30'),DATE('2001-01-01'))) AS c6, +TIMESTAMP(COALESCE(TIMESTAMP('2001-01-02 10:20:30'),DATE('2001-01-01'))) AS c7; +c1 c2 c3 c4 c5 c6 c7 +20010102102030 20010102102030.0000 20010102102030 2001-01-02 10:20:30 10:20:30 2001-01-02 2001-01-02 10:20:30 +SELECT +CAST(IFNULL(DATE('2001-01-01'),TIMESTAMP('2001-01-02 10:20:30')) AS SIGNED) AS c1, +CAST(IFNULL(DATE('2001-01-01'),TIMESTAMP('2001-01-02 10:20:30')) AS DECIMAL(25,4)) AS c2, +IFNULL(DATE('2001-01-01'),TIMESTAMP('2001-01-02 10:20:30'))+0e0 AS c3, +CONCAT(IFNULL(DATE('2001-01-01'),TIMESTAMP('2001-01-02 10:20:30'))) AS c4, +TIME(IFNULL(DATE('2001-01-01'),TIMESTAMP('2001-01-02 10:20:30'))) AS c5, +DATE(IFNULL(DATE('2001-01-01'),TIMESTAMP('2001-01-02 10:20:30'))) AS c6, +TIMESTAMP(IFNULL(DATE('2001-01-01'),TIMESTAMP('2001-01-02 10:20:30'))) AS c7; +c1 c2 c3 c4 c5 c6 c7 +20010101000000 20010101000000.0000 20010101000000 2001-01-01 00:00:00 00:00:00 2001-01-01 2001-01-01 00:00:00 +SELECT +CAST(IFNULL(TIMESTAMP('2001-01-02 10:20:30'),DATE('2001-01-01')) AS SIGNED) AS c1, +CAST(IFNULL(TIMESTAMP('2001-01-02 10:20:30'),DATE('2001-01-01')) AS DECIMAL(25,4)) AS c2, +IFNULL(TIMESTAMP('2001-01-02 10:20:30'),DATE('2001-01-01'))+0e0 AS c3, +CONCAT(IFNULL(TIMESTAMP('2001-01-02 10:20:30'),DATE('2001-01-01'))) AS c4, +TIME(IFNULL(TIMESTAMP('2001-01-02 10:20:30'),DATE('2001-01-01'))) AS c5, +DATE(IFNULL(TIMESTAMP('2001-01-02 10:20:30'),DATE('2001-01-01'))) AS c6, +TIMESTAMP(IFNULL(TIMESTAMP('2001-01-02 10:20:30'),DATE('2001-01-01'))) AS c7; +c1 c2 c3 c4 c5 c6 c7 +20010102102030 20010102102030.0000 20010102102030 2001-01-02 10:20:30 10:20:30 2001-01-02 2001-01-02 10:20:30 +SELECT +CAST(IF(1,DATE('2001-01-01'),TIMESTAMP('2001-01-02 10:20:30')) AS SIGNED) AS c1, +CAST(IF(1,DATE('2001-01-01'),TIMESTAMP('2001-01-02 10:20:30')) AS DECIMAL(25,4)) AS c2, +IF(1,DATE('2001-01-01'),TIMESTAMP('2001-01-02 10:20:30'))+0e0 AS c3, +CONCAT(IF(1,DATE('2001-01-01'),TIMESTAMP('2001-01-02 10:20:30'))) AS c4, +TIME(IF(1,DATE('2001-01-01'),TIMESTAMP('2001-01-02 10:20:30'))) AS c5, +DATE(IF(1,DATE('2001-01-01'),TIMESTAMP('2001-01-02 10:20:30'))) AS c6, +TIMESTAMP(IF(1,DATE('2001-01-01'),TIMESTAMP('2001-01-02 10:20:30'))) AS c7; +c1 c2 c3 c4 c5 c6 c7 +20010101000000 20010101000000.0000 20010101000000 2001-01-01 00:00:00 00:00:00 2001-01-01 2001-01-01 00:00:00 +SELECT +CAST(IF(0,DATE('2001-01-01'),TIMESTAMP('2001-01-02 10:20:30')) AS SIGNED) AS c1, +CAST(IF(0,DATE('2001-01-01'),TIMESTAMP('2001-01-02 10:20:30')) AS DECIMAL(25,4)) AS c2, +IF(0,DATE('2001-01-01'),TIMESTAMP('2001-01-02 10:20:30'))+0e0 AS c3, +CONCAT(IF(0,DATE('2001-01-01'),TIMESTAMP('2001-01-02 10:20:30'))) AS c4, +TIME(IF(0,DATE('2001-01-01'),TIMESTAMP('2001-01-02 10:20:30'))) AS c5, +DATE(IF(0,DATE('2001-01-01'),TIMESTAMP('2001-01-02 10:20:30'))) AS c6, +TIMESTAMP(IF(0,DATE('2001-01-01'),TIMESTAMP('2001-01-02 10:20:30'))) AS c7; +c1 c2 c3 c4 c5 c6 c7 +20010102102030 20010102102030.0000 20010102102030 2001-01-02 10:20:30 10:20:30 2001-01-02 2001-01-02 10:20:30 +SELECT +CAST(CASE WHEN 1 THEN DATE('2001-01-01') ELSE TIMESTAMP('2001-01-02 10:20:30') END AS SIGNED) AS c1, +CAST(CASE WHEN 1 THEN DATE('2001-01-01') ELSE TIMESTAMP('2001-01-02 10:20:30') END AS DECIMAL(25,4)) AS c2, +CASE WHEN 1 THEN DATE('2001-01-01') ELSE TIMESTAMP('2001-01-02 10:20:30') END+0e0 AS c3, +CONCAT(CASE WHEN 1 THEN DATE('2001-01-01') ELSE TIMESTAMP('2001-01-02 10:20:30') END) AS c4, +TIME(CASE WHEN 1 THEN DATE('2001-01-01') ELSE TIMESTAMP('2001-01-02 10:20:30') END) AS c5, +DATE(CASE WHEN 1 THEN DATE('2001-01-01') ELSE TIMESTAMP('2001-01-02 10:20:30') END) AS c6, +TIMESTAMP(CASE WHEN 1 THEN DATE('2001-01-01') ELSE TIMESTAMP('2001-01-02 10:20:30') END) AS c7; +c1 c2 c3 c4 c5 c6 c7 +20010101000000 20010101000000.0000 20010101000000 2001-01-01 00:00:00 00:00:00 2001-01-01 2001-01-01 00:00:00 +SELECT +CAST(CASE WHEN 0 THEN DATE('2001-01-01') ELSE TIMESTAMP('2001-01-02 10:20:30') END AS SIGNED) AS c1, +CAST(CASE WHEN 0 THEN DATE('2001-01-01') ELSE TIMESTAMP('2001-01-02 10:20:30') END AS DECIMAL(25,4)) AS c2, +CASE WHEN 0 THEN DATE('2001-01-01') ELSE TIMESTAMP('2001-01-02 10:20:30') END+0e0 AS c3, +CONCAT(CASE WHEN 0 THEN DATE('2001-01-01') ELSE TIMESTAMP('2001-01-02 10:20:30') END) AS c4, +TIME(CASE WHEN 0 THEN DATE('2001-01-01') ELSE TIMESTAMP('2001-01-02 10:20:30') END) AS c5, +DATE(CASE WHEN 0 THEN DATE('2001-01-01') ELSE TIMESTAMP('2001-01-02 10:20:30') END) AS c6, +TIMESTAMP(CASE WHEN 0 THEN DATE('2001-01-01') ELSE TIMESTAMP('2001-01-02 10:20:30') END) AS c7; +c1 c2 c3 c4 c5 c6 c7 +20010102102030 20010102102030.0000 20010102102030 2001-01-02 10:20:30 10:20:30 2001-01-02 2001-01-02 10:20:30 +CREATE TABLE t1 AS SELECT +CONCAT(COALESCE(TIME(101010),TIME(101010))) AS c1, +CONCAT(IF(0,TIME(101010),TIME(101010))) AS c2, +CONCAT(IFNULL(TIME(101010),TIME(101010))) AS c3, +CONCAT(CASE WHEN 1 THEN TIME(101010) ELSE TIME(101010) END) AS c4; +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `c1` varchar(10) DEFAULT NULL, + `c2` varchar(10) DEFAULT NULL, + `c3` varchar(10) DEFAULT NULL, + `c4` varchar(10) DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +DROP TABLE t1; +# +# MDEV-4870 Wrong values of CASE, COALESCE, IFNULL on a combination of different temporal types +# +SET timestamp=UNIX_TIMESTAMP('2001-01-01 10:20:30'); +CREATE TABLE t1 (dt2 DATETIME(2), t3 TIME(3), d DATE); +INSERT INTO t1 VALUES ('2001-01-01 00:00:00.12', '00:00:00.567', '2002-01-01'); +SELECT CASE WHEN 0 THEN dt2 ELSE t3 END FROM t1; +CASE WHEN 0 THEN dt2 ELSE t3 END +2001-01-01 00:00:00.567 +CREATE TABLE t2 AS SELECT CASE WHEN 0 THEN dt2 ELSE t3 END FROM t1; +SELECT * FROM t2; +CASE WHEN 0 THEN dt2 ELSE t3 END +2001-01-01 00:00:00.567 +SHOW COLUMNS FROM t2; +Field Type Null Key Default Extra +CASE WHEN 0 THEN dt2 ELSE t3 END datetime(3) YES NULL +DROP TABLE t2; +SELECT CASE WHEN 1 THEN dt2 ELSE t3 END FROM t1; +CASE WHEN 1 THEN dt2 ELSE t3 END +2001-01-01 00:00:00.120 +SELECT CONCAT(CASE WHEN 1 THEN dt2 ELSE t3 END) FROM t1; +CONCAT(CASE WHEN 1 THEN dt2 ELSE t3 END) +2001-01-01 00:00:00.120 +SELECT CONCAT(CASE WHEN 0 THEN t3 ELSE dt2 END) FROM t1; +CONCAT(CASE WHEN 0 THEN t3 ELSE dt2 END) +2001-01-01 00:00:00.120 +SELECT CONCAT(CASE WHEN 1 THEN d ELSE t3 END) FROM t1; +CONCAT(CASE WHEN 1 THEN d ELSE t3 END) +2002-01-01 00:00:00.000 +SELECT CASE WHEN 1 THEN t3 ELSE d END FROM t1; +CASE WHEN 1 THEN t3 ELSE d END +2001-01-01 00:00:00.567 +SELECT COALESCE(d, t3) FROM t1; +COALESCE(d, t3) +2002-01-01 00:00:00.000 +SELECT CONCAT(COALESCE(d, t3)) FROM t1; +CONCAT(COALESCE(d, t3)) +2002-01-01 00:00:00.000 +SELECT COALESCE(dt2, t3) FROM t1; +COALESCE(dt2, t3) +2001-01-01 00:00:00.120 +SELECT CONCAT(COALESCE(dt2, t3)) FROM t1; +CONCAT(COALESCE(dt2, t3)) +2001-01-01 00:00:00.120 +SELECT IFNULL(dt2, t3), CONCAT(IFNULL(dt2, t3)) FROM t1; +IFNULL(dt2, t3) CONCAT(IFNULL(dt2, t3)) +2001-01-01 00:00:00.120 2001-01-01 00:00:00.120 +SELECT IFNULL(d, t3), CONCAT(IFNULL(d, t3)) FROM t1; +IFNULL(d, t3) CONCAT(IFNULL(d, t3)) +2002-01-01 00:00:00.000 2002-01-01 00:00:00.000 +DROP TABLE t1; +SET timestamp=DEFAULT; +# +# MDEV-4724 Some temporal functions do not preserve microseconds +# +SELECT MAKETIME(10,10,10.231); +MAKETIME(10,10,10.231) +10:10:10.231 +SELECT MAKETIME(0, 0, 59.9); +MAKETIME(0, 0, 59.9) +00:00:59.9 +CREATE TABLE t1 AS SELECT +MAKETIME(10,00,00), +MAKETIME(10,00,00.1), +MAKETIME(10,00,00.12), +MAKETIME(10,00,00.123), +MAKETIME(10,00,00.1234), +MAKETIME(10,00,00.12345), +MAKETIME(10,00,00.123456); +SHOW COLUMNS FROM t1; +Field Type Null Key Default Extra +MAKETIME(10,00,00) time YES NULL +MAKETIME(10,00,00.1) time(1) YES NULL +MAKETIME(10,00,00.12) time(2) YES NULL +MAKETIME(10,00,00.123) time(3) YES NULL +MAKETIME(10,00,00.1234) time(4) YES NULL +MAKETIME(10,00,00.12345) time(5) YES NULL +MAKETIME(10,00,00.123456) time(6) YES NULL +DROP TABLE t1; +CREATE TABLE t1 AS SELECT +TIME('10:00:00'), +TIME('10:00:00.1'), +TIME('10:00:00.12'), +TIME('10:00:00.123'), +TIME('10:00:00.1234'), +TIME('10:00:00.12345'), +TIME('10:00:00.12346'); +SHOW COLUMNS FROM t1; +Field Type Null Key Default Extra +TIME('10:00:00') time YES NULL +TIME('10:00:00.1') time(1) YES NULL +TIME('10:00:00.12') time(2) YES NULL +TIME('10:00:00.123') time(3) YES NULL +TIME('10:00:00.1234') time(4) YES NULL +TIME('10:00:00.12345') time(5) YES NULL +TIME('10:00:00.12346') time(5) YES NULL +DROP TABLE t1; +SET TIME_ZONE='+00:00'; +SET TIMESTAMP=UNIX_TIMESTAMP('2012-10-16 22:46:17'); +SELECT NOW(), UNIX_TIMESTAMP(), UNIX_TIMESTAMP(NOW()),UNIX_TIMESTAMP('2012-10-16 22:46:17'); +NOW() UNIX_TIMESTAMP() UNIX_TIMESTAMP(NOW()) UNIX_TIMESTAMP('2012-10-16 22:46:17') +2012-10-16 22:46:17 1350427577 1350427577 1350427577 +SET TIMESTAMP=UNIX_TIMESTAMP('1970-01-02 03:04:05.123456'); +SELECT @@timestamp, FROM_UNIXTIME(@@timestamp); +@@timestamp FROM_UNIXTIME(@@timestamp) +97445.123456 1970-01-02 03:04:05.123456 +SET TIME_ZONE=DEFAULT; +SET TIMESTAMP=DEFAULT; +SELECT TIME('2012-10-16 15:54:16.12'); +TIME('2012-10-16 15:54:16.12') +15:54:16.12 +SELECT TIMESTAMP('2012-10-16 15:54:16.12'); +TIMESTAMP('2012-10-16 15:54:16.12') +2012-10-16 15:54:16.12 +SELECT TIMEDIFF('10:10:10.1','00:00:00'); +TIMEDIFF('10:10:10.1','00:00:00') +10:10:10.1 +SELECT TIME_TO_SEC('10:10:10'); +TIME_TO_SEC('10:10:10') +36610 +SELECT ADDTIME(TIME('10:10:10.1'),'10:10:10.12'); +ADDTIME(TIME('10:10:10.1'),'10:10:10.12') +20:20:20.22 +SELECT ADDTIME(TIMESTAMP('2001-01-01 10:10:10.1'),'10:10:10.12'); +ADDTIME(TIMESTAMP('2001-01-01 10:10:10.1'),'10:10:10.12') +2001-01-01 20:20:20.22 +SELECT DATE_ADD('2001-01-01 00:00:00', INTERVAL 1 SECOND); +DATE_ADD('2001-01-01 00:00:00', INTERVAL 1 SECOND) +2001-01-01 00:00:01 +SELECT DATE_ADD('2001-01-01 00:00:00.1', INTERVAL 1 SECOND); +DATE_ADD('2001-01-01 00:00:00.1', INTERVAL 1 SECOND) +2001-01-01 00:00:01.1 +SELECT DATE_ADD('2001-01-01 00:00:00.12', INTERVAL 1 SECOND); +DATE_ADD('2001-01-01 00:00:00.12', INTERVAL 1 SECOND) +2001-01-01 00:00:01.12 +SELECT DATE_ADD('2001-01-01 00:00:00.123', INTERVAL 1 SECOND); +DATE_ADD('2001-01-01 00:00:00.123', INTERVAL 1 SECOND) +2001-01-01 00:00:01.123 +SELECT DATE_ADD('2001-01-01 00:00:00.1234', INTERVAL 1 SECOND); +DATE_ADD('2001-01-01 00:00:00.1234', INTERVAL 1 SECOND) +2001-01-01 00:00:01.1234 +SELECT DATE_ADD('2001-01-01 00:00:00.12345', INTERVAL 1 SECOND); +DATE_ADD('2001-01-01 00:00:00.12345', INTERVAL 1 SECOND) +2001-01-01 00:00:01.12345 +SELECT DATE_ADD('2001-01-01 00:00:00.123456', INTERVAL 1 SECOND); +DATE_ADD('2001-01-01 00:00:00.123456', INTERVAL 1 SECOND) +2001-01-01 00:00:01.123456 +SELECT DATE_ADD('2001-01-01 00:00:00', INTERVAL 1 SECOND); +DATE_ADD('2001-01-01 00:00:00', INTERVAL 1 SECOND) +2001-01-01 00:00:01 +SELECT DATE_ADD('2001-01-01 00:00:00', INTERVAL 1.1 SECOND); +DATE_ADD('2001-01-01 00:00:00', INTERVAL 1.1 SECOND) +2001-01-01 00:00:01.1 +SELECT DATE_ADD('2001-01-01 00:00:00', INTERVAL 1.12 SECOND); +DATE_ADD('2001-01-01 00:00:00', INTERVAL 1.12 SECOND) +2001-01-01 00:00:01.12 +SELECT DATE_ADD('2001-01-01 00:00:00', INTERVAL 1.123 SECOND); +DATE_ADD('2001-01-01 00:00:00', INTERVAL 1.123 SECOND) +2001-01-01 00:00:01.123 +SELECT DATE_ADD('2001-01-01 00:00:00', INTERVAL 1.1234 SECOND); +DATE_ADD('2001-01-01 00:00:00', INTERVAL 1.1234 SECOND) +2001-01-01 00:00:01.1234 +SELECT DATE_ADD('2001-01-01 00:00:00', INTERVAL 1.12345 SECOND); +DATE_ADD('2001-01-01 00:00:00', INTERVAL 1.12345 SECOND) +2001-01-01 00:00:01.12345 +SELECT DATE_ADD('2001-01-01 00:00:00', INTERVAL 1.123456 SECOND); +DATE_ADD('2001-01-01 00:00:00', INTERVAL 1.123456 SECOND) +2001-01-01 00:00:01.123456 +SELECT DATE_ADD('2001-01-01 00:00:00', INTERVAL -1 SECOND); +DATE_ADD('2001-01-01 00:00:00', INTERVAL -1 SECOND) +2000-12-31 23:59:59 +SELECT DATE_ADD('2001-01-01 00:00:00', INTERVAL -1.1 SECOND); +DATE_ADD('2001-01-01 00:00:00', INTERVAL -1.1 SECOND) +2000-12-31 23:59:58.9 +SELECT DATE_ADD('2001-01-01 00:00:00', INTERVAL -1.12 SECOND); +DATE_ADD('2001-01-01 00:00:00', INTERVAL -1.12 SECOND) +2000-12-31 23:59:58.88 +SELECT DATE_ADD('2001-01-01 00:00:00', INTERVAL -1.123 SECOND); +DATE_ADD('2001-01-01 00:00:00', INTERVAL -1.123 SECOND) +2000-12-31 23:59:58.877 +SELECT DATE_ADD('2001-01-01 00:00:00', INTERVAL -1.1234 SECOND); +DATE_ADD('2001-01-01 00:00:00', INTERVAL -1.1234 SECOND) +2000-12-31 23:59:58.8766 +SELECT DATE_ADD('2001-01-01 00:00:00', INTERVAL -1.12345 SECOND); +DATE_ADD('2001-01-01 00:00:00', INTERVAL -1.12345 SECOND) +2000-12-31 23:59:58.87655 +SELECT DATE_ADD('2001-01-01 00:00:00', INTERVAL -1.123456 SECOND); +DATE_ADD('2001-01-01 00:00:00', INTERVAL -1.123456 SECOND) +2000-12-31 23:59:58.876544 +SELECT DATE_ADD('2001-01-01 00:00:00', INTERVAL 'xxx' SECOND); +DATE_ADD('2001-01-01 00:00:00', INTERVAL 'xxx' SECOND) +2001-01-01 00:00:00.000000 +Warnings: +Warning 1292 Truncated incorrect DECIMAL value: 'xxx' +SELECT CONVERT_TZ('2001-01-01 10:20:30.12','+00:00','+01:00'); +CONVERT_TZ('2001-01-01 10:20:30.12','+00:00','+01:00') +2001-01-01 11:20:30.12 +# +# MDEV-4861 TIME/DATETIME arithmetics does not preserve INTERVAL precision +# +CREATE TABLE t1 (t0 TIME); +INSERT INTO t1 VALUES ('00:00:00'); +SELECT t0 + INTERVAL 1.1 SECOND FROM t1; +t0 + INTERVAL 1.1 SECOND +00:00:01.1 +CREATE TABLE t2 AS SELECT t0 + INTERVAL 1.1 SECOND FROM t1; +SHOW COLUMNS FROM t2; +Field Type Null Key Default Extra +t0 + INTERVAL 1.1 SECOND time(1) YES NULL +DROP TABLE t1,t2; +CREATE TABLE t1 (t0 DATETIME); +INSERT INTO t1 VALUES ('2001-01-01 00:00:00'); +SELECT t0 + INTERVAL 1.1 SECOND FROM t1; +t0 + INTERVAL 1.1 SECOND +2001-01-01 00:00:01.1 +CREATE TABLE t2 AS SELECT t0 + INTERVAL 1.1 SECOND FROM t1; +SHOW COLUMNS FROM t2; +Field Type Null Key Default Extra +t0 + INTERVAL 1.1 SECOND datetime(1) YES NULL +DROP TABLE t1, t2; +# +# MDEV-4843 Wrong data type for TIMESTAMP('2001-01-01','10:10:10') +# +CREATE TABLE t1 AS SELECT +TIMESTAMP('2001-01-01','10:10:10'), +TIMESTAMP('2001-01-01','10:10:10.1'), +TIMESTAMP('2001-01-01','10:10:10.12'), +TIMESTAMP('2001-01-01','10:10:10.123'), +TIMESTAMP('2001-01-01','10:10:10.1234'), +TIMESTAMP('2001-01-01','10:10:10.12345'), +TIMESTAMP('2001-01-01','10:10:10.123456'), +TIMESTAMP('2001-01-01','10:10:10.1234567'); +Warnings: +Note 1292 Truncated incorrect time value: '10:10:10.1234567' +SHOW COLUMNS FROM t1; +Field Type Null Key Default Extra +TIMESTAMP('2001-01-01','10:10:10') datetime YES NULL +TIMESTAMP('2001-01-01','10:10:10.1') datetime(1) YES NULL +TIMESTAMP('2001-01-01','10:10:10.12') datetime(2) YES NULL +TIMESTAMP('2001-01-01','10:10:10.123') datetime(3) YES NULL +TIMESTAMP('2001-01-01','10:10:10.1234') datetime(4) YES NULL +TIMESTAMP('2001-01-01','10:10:10.12345') datetime(5) YES NULL +TIMESTAMP('2001-01-01','10:10:10.123456') datetime(6) YES NULL +TIMESTAMP('2001-01-01','10:10:10.1234567') datetime(6) YES NULL +SELECT * FROM t1; +TIMESTAMP('2001-01-01','10:10:10') TIMESTAMP('2001-01-01','10:10:10.1') TIMESTAMP('2001-01-01','10:10:10.12') TIMESTAMP('2001-01-01','10:10:10.123') TIMESTAMP('2001-01-01','10:10:10.1234') TIMESTAMP('2001-01-01','10:10:10.12345') TIMESTAMP('2001-01-01','10:10:10.123456') TIMESTAMP('2001-01-01','10:10:10.1234567') +2001-01-01 10:10:10 2001-01-01 10:10:10.1 2001-01-01 10:10:10.12 2001-01-01 10:10:10.123 2001-01-01 10:10:10.1234 2001-01-01 10:10:10.12345 2001-01-01 10:10:10.123456 2001-01-01 10:10:10.123456 +DROP TABLE t1; +CREATE TABLE t1 AS SELECT +TIMESTAMP('2001-01-01 00:00:00','10:10:10'), +TIMESTAMP('2001-01-01 00:00:00.1','10:10:10'), +TIMESTAMP('2001-01-01 00:00:00.12','10:10:10'), +TIMESTAMP('2001-01-01 00:00:00.123','10:10:10'), +TIMESTAMP('2001-01-01 00:00:00.1234','10:10:10'), +TIMESTAMP('2001-01-01 00:00:00.12345','10:10:10'), +TIMESTAMP('2001-01-01 00:00:00.123456','10:10:10'), +TIMESTAMP('2001-01-01 00:00:00.1234567','10:10:10'); +Warnings: +Note 1292 Truncated incorrect datetime value: '2001-01-01 00:00:00.1234567' +SHOW COLUMNS FROM t1; +Field Type Null Key Default Extra +TIMESTAMP('2001-01-01 00:00:00','10:10:10') datetime YES NULL +TIMESTAMP('2001-01-01 00:00:00.1','10:10:10') datetime(1) YES NULL +TIMESTAMP('2001-01-01 00:00:00.12','10:10:10') datetime(2) YES NULL +TIMESTAMP('2001-01-01 00:00:00.123','10:10:10') datetime(3) YES NULL +TIMESTAMP('2001-01-01 00:00:00.1234','10:10:10') datetime(4) YES NULL +TIMESTAMP('2001-01-01 00:00:00.12345','10:10:10') datetime(5) YES NULL +TIMESTAMP('2001-01-01 00:00:00.123456','10:10:10') datetime(6) YES NULL +TIMESTAMP('2001-01-01 00:00:00.1234567','10:10:10') datetime(6) YES NULL +SELECT * FROM t1; +TIMESTAMP('2001-01-01 00:00:00','10:10:10') TIMESTAMP('2001-01-01 00:00:00.1','10:10:10') TIMESTAMP('2001-01-01 00:00:00.12','10:10:10') TIMESTAMP('2001-01-01 00:00:00.123','10:10:10') TIMESTAMP('2001-01-01 00:00:00.1234','10:10:10') TIMESTAMP('2001-01-01 00:00:00.12345','10:10:10') TIMESTAMP('2001-01-01 00:00:00.123456','10:10:10') TIMESTAMP('2001-01-01 00:00:00.1234567','10:10:10') +2001-01-01 10:10:10 2001-01-01 10:10:10.1 2001-01-01 10:10:10.12 2001-01-01 10:10:10.123 2001-01-01 10:10:10.1234 2001-01-01 10:10:10.12345 2001-01-01 10:10:10.123456 2001-01-01 10:10:10.123456 +DROP TABLE t1; +SET timestamp=unix_timestamp('2001-02-03 10:20:30'); +CREATE TABLE t1 AS SELECT +TIMESTAMP('00:00:00','10:10:10'), +TIMESTAMP(TIME('00:00:00'),'10:10:10'); +SHOW COLUMNS FROM t1; +Field Type Null Key Default Extra +TIMESTAMP('00:00:00','10:10:10') datetime YES NULL +TIMESTAMP(TIME('00:00:00'),'10:10:10') datetime YES NULL +SELECT * FROM t1; +TIMESTAMP('00:00:00','10:10:10') TIMESTAMP(TIME('00:00:00'),'10:10:10') +NULL 2001-02-03 10:10:10 +DROP TABLE t1; +SET timestamp=DEFAULT; +# +# MDEV-4869 Wrong result of MAKETIME(0, 0, -0.1) +# +SELECT MAKETIME(0, 0, -0.1); +MAKETIME(0, 0, -0.1) +NULL +# +# MDEV-4857 Wrong result of HOUR('1 00:00:00') +# +SET timestamp=unix_timestamp('2001-02-03 10:20:30'); +SELECT HOUR('1 02:00:00'), HOUR('26:00:00'); +HOUR('1 02:00:00') HOUR('26:00:00') +26 26 +SELECT HOUR(TIME'1 02:00:00'), HOUR(TIME'26:00:00'); +HOUR(TIME'1 02:00:00') HOUR(TIME'26:00:00') +26 26 +SELECT HOUR(TIME('1 02:00:00')), HOUR(TIME('26:00:00')); +HOUR(TIME('1 02:00:00')) HOUR(TIME('26:00:00')) +26 26 +SELECT DAY(TIME('1 02:00:00')), DAY(TIME('26:00:00')); +DAY(TIME('1 02:00:00')) DAY(TIME('26:00:00')) +4 4 +SELECT EXTRACT(HOUR FROM '1 02:00:00'), EXTRACT(HOUR FROM '26:00:00'); +EXTRACT(HOUR FROM '1 02:00:00') EXTRACT(HOUR FROM '26:00:00') +2 2 +SELECT EXTRACT(HOUR FROM TIME'1 02:00:00'), EXTRACT(HOUR FROM TIME'26:00:00'); +EXTRACT(HOUR FROM TIME'1 02:00:00') EXTRACT(HOUR FROM TIME'26:00:00') +2 2 +SELECT EXTRACT(HOUR FROM TIME('1 02:00:00')), EXTRACT(HOUR FROM TIME('26:00:00')); +EXTRACT(HOUR FROM TIME('1 02:00:00')) EXTRACT(HOUR FROM TIME('26:00:00')) +2 2 +SELECT EXTRACT(DAY FROM TIME('1 02:00:00')), EXTRACT(DAY FROM TIME('26:00:00')); +EXTRACT(DAY FROM TIME('1 02:00:00')) EXTRACT(DAY FROM TIME('26:00:00')) +1 1 +SET timestamp=DEFAULT; +# +# MDEV-5458 RQG hits 'sql/tztime.cc:799: my_time_t sec_since_epoch(int, int, int, int, int, int): Assertion `mon > 0 && mon < 13' failed.' +# +SET TIMESTAMP=UNIX_TIMESTAMP('2014-01-22 18:19:20'); +CREATE TABLE t1 (t TIME); +INSERT INTO t1 VALUES ('03:22:30'),('18:30:05'); +SELECT CONVERT_TZ(GREATEST(t, CURRENT_DATE()), '+02:00', '+10:00') FROM t1; +CONVERT_TZ(GREATEST(t, CURRENT_DATE()), '+02:00', '+10:00') +2014-01-22 11:22:30 +2014-01-23 02:30:05 +SELECT GREATEST(t, CURRENT_DATE()) FROM t1; +GREATEST(t, CURRENT_DATE()) +2014-01-22 03:22:30 +2014-01-22 18:30:05 +DROP TABLE t1; +SET TIMESTAMP=DEFAULT; +# +# MDEV-5504 Server crashes in String::length on SELECT with MONTHNAME, GROUP BY, ROLLUP +# +CREATE TABLE t1 (i INT); +INSERT INTO t1 VALUES (1),(2); +SELECT 1 FROM t1 GROUP BY MONTHNAME(0) WITH ROLLUP; +1 +1 +1 +DROP TABLE t1; +# +# MDEV-6099 Bad results for DATE_ADD(.., INTERVAL 2000000000000000000.0 SECOND) +# +SELECT DATE_ADD('2001-01-01 10:20:30',INTERVAL 250000000000.0 SECOND) AS c1, DATE_ADD('2001-01-01 10:20:30',INTERVAL 2000000000000000000.0 SECOND) AS c2; +c1 c2 +9923-03-10 22:47:10.0 NULL +Warnings: +Warning 1292 Truncated incorrect DECIMAL value: '2000000000000000000.0' +# +# MDEV-4838 Wrong metadata for DATE_ADD('string', INVERVAL) +# +SELECT DATE_ADD('2011-01-02 12:13:14', INTERVAL 1 MINUTE); +Catalog Database Table Table_alias Column Column_alias Type Length Max length Is_null Flags Decimals Charsetnr +def DATE_ADD('2011-01-02 12:13:14', INTERVAL 1 MINUTE) 254 19 19 Y 0 0 8 +DATE_ADD('2011-01-02 12:13:14', INTERVAL 1 MINUTE) +2011-01-02 12:14:14 +# +# MDEV-5450 Assertion `cached_field_ type == MYSQL_TYPE_STRING || ltime.time_type == MYSQL_TIMESTAMP_NONE || mysql_type_to_time_type(cached_field_type) == ltime.time_type' fails with IF, ISNULL, ADDDATE +# +CREATE TABLE t1 (a DATETIME, b DATE); +INSERT INTO t1 VALUES (NULL, '2012-12-21'); +SELECT IF(1,ADDDATE(IFNULL(a,b),0),1) FROM t1; +IF(1,ADDDATE(IFNULL(a,b),0),1) +2012-12-21 00:00:00 +SELECT CAST(ADDDATE(IFNULL(a,b),0) AS CHAR) FROM t1; +CAST(ADDDATE(IFNULL(a,b),0) AS CHAR) +2012-12-21 00:00:00 +SELECT CAST(ADDDATE(COALESCE(a,b),0) AS CHAR) FROM t1; +CAST(ADDDATE(COALESCE(a,b),0) AS CHAR) +2012-12-21 00:00:00 +SELECT CAST(ADDDATE(CASE WHEN 0 THEN a ELSE b END,0) AS CHAR) FROM t1; +CAST(ADDDATE(CASE WHEN 0 THEN a ELSE b END,0) AS CHAR) +2012-12-21 00:00:00 +SELECT IF(1,ADDTIME(IFNULL(a,b),0),1) FROM t1; +IF(1,ADDTIME(IFNULL(a,b),0),1) +2012-12-21 00:00:00 +SELECT CAST(ADDTIME(IFNULL(a,b),0) AS CHAR) FROM t1; +CAST(ADDTIME(IFNULL(a,b),0) AS CHAR) +2012-12-21 00:00:00 +SELECT CAST(ADDTIME(COALESCE(a,b),0) AS CHAR) FROM t1; +CAST(ADDTIME(COALESCE(a,b),0) AS CHAR) +2012-12-21 00:00:00 +SELECT CAST(ADDTIME(CASE WHEN 0 THEN a ELSE b END,0) AS CHAR) FROM t1; +CAST(ADDTIME(CASE WHEN 0 THEN a ELSE b END,0) AS CHAR) +2012-12-21 00:00:00 +DROP TABLE t1; +SET timestamp=unix_timestamp('2001-02-03 10:20:30'); +CREATE TABLE t1 (a DATETIME, b TIME); +INSERT INTO t1 VALUES (NULL, '00:20:12'); +SELECT IF(1,ADDDATE(IFNULL(a,b),0),1) FROM t1; +IF(1,ADDDATE(IFNULL(a,b),0),1) +2001-02-03 00:20:12 +SELECT CAST(ADDDATE(IFNULL(a,b),0) AS CHAR) FROM t1; +CAST(ADDDATE(IFNULL(a,b),0) AS CHAR) +2001-02-03 00:20:12 +SELECT CAST(ADDDATE(COALESCE(a,b),0) AS CHAR) FROM t1; +CAST(ADDDATE(COALESCE(a,b),0) AS CHAR) +2001-02-03 00:20:12 +SELECT CAST(ADDDATE(CASE WHEN 0 THEN a ELSE b END,0) AS CHAR) FROM t1; +CAST(ADDDATE(CASE WHEN 0 THEN a ELSE b END,0) AS CHAR) +2001-02-03 00:20:12 +SELECT IF(1,ADDTIME(IFNULL(a,b),0),1) FROM t1; +IF(1,ADDTIME(IFNULL(a,b),0),1) +2001-02-03 00:20:12 +SELECT CAST(ADDTIME(IFNULL(a,b),0) AS CHAR) FROM t1; +CAST(ADDTIME(IFNULL(a,b),0) AS CHAR) +2001-02-03 00:20:12 +SELECT CAST(ADDTIME(COALESCE(a,b),0) AS CHAR) FROM t1; +CAST(ADDTIME(COALESCE(a,b),0) AS CHAR) +2001-02-03 00:20:12 +SELECT CAST(ADDTIME(CASE WHEN 0 THEN a ELSE b END,0) AS CHAR) FROM t1; +CAST(ADDTIME(CASE WHEN 0 THEN a ELSE b END,0) AS CHAR) +2001-02-03 00:20:12 +DROP TABLE t1; +SET timestamp=DEFAULT; +# +# MDEV-5870 Assertion `ltime->neg == 0' fails with COALESCE, ADDDATE, MAKEDATE +# +CREATE TABLE t1 (dt DATETIME); +INSERT INTO t1 VALUES ('2003-05-13 19:36:05'), ('2012-12-12 09:20:06'); +SELECT COALESCE(ADDDATE(MAKEDATE(2011,121), dt), '2006-09-12' ) FROM t1; +COALESCE(ADDDATE(MAKEDATE(2011,121), dt), '2006-09-12' ) +2006-09-12 +2006-09-12 +Warnings: +Warning 1441 Datetime function: datetime field overflow +Warning 1441 Datetime function: datetime field overflow +DROP TABLE t1; +# +# MDEV-6101 Hybrid functions do not add CURRENT_DATE when converting TIME to DATETIME +# +SET timestamp=UNIX_TIMESTAMP('2014-04-15 01:02:03'); +SELECT IF(1,TIME'10:20:30',DATE'2001-01-01'); +IF(1,TIME'10:20:30',DATE'2001-01-01') +2014-04-15 10:20:30 +SELECT IFNULL(TIME'10:20:30',DATE'2001-01-01'); +IFNULL(TIME'10:20:30',DATE'2001-01-01') +2014-04-15 10:20:30 +SELECT CASE WHEN 1 THEN TIME'10:20:30' ELSE DATE'2001-01-01' END; +CASE WHEN 1 THEN TIME'10:20:30' ELSE DATE'2001-01-01' END +2014-04-15 10:20:30 +SELECT COALESCE(TIME'10:20:30',DATE'2001-01-01'); +COALESCE(TIME'10:20:30',DATE'2001-01-01') +2014-04-15 10:20:30 +SET timestamp=DEFAULT; +# +# MDEV-5750 Assertion `ltime->year == 0' fails on a query with EXTRACT DAY_MINUTE and TIME column +# +SET timestamp=UNIX_TIMESTAMP('2010-01-01 00:00:00'); +CREATE TABLE t1 ( d DATE, t TIME ); +INSERT INTO t1 VALUES ('2008-12-05','22:34:09'),('2005-03-27','14:26:02'); +SELECT EXTRACT(DAY_MINUTE FROM GREATEST(t,d)), GREATEST(t,d) FROM t1; +EXTRACT(DAY_MINUTE FROM GREATEST(t,d)) GREATEST(t,d) +12234 2010-01-01 22:34:09 +11426 2010-01-01 14:26:02 +DROP TABLE t1; +SET timestamp=DEFAULT; +# +# MDEV-7221 from_days fails after null value +# +CREATE TABLE t1 ( +id INT(11) NOT NULL PRIMARY KEY, +date1 DATE NULL DEFAULT NULL +); +INSERT INTO t1 VALUES (12, '2011-05-12'); +INSERT INTO t1 VALUES (13, NULL); +INSERT INTO t1 VALUES (14, '2009-10-23'); +INSERT INTO t1 VALUES (15, '2014-10-30'); +INSERT INTO t1 VALUES (16, NULL); +INSERT INTO t1 VALUES (17, NULL); +INSERT INTO t1 VALUES (18, '2010-10-13'); +SELECT a.id,a.date1,FROM_DAYS(TO_DAYS(a.date1)-10) as date2, DATE_ADD(a.date1,INTERVAL -10 DAY),TO_DAYS(a.date1)-10 FROM t1 a ORDER BY a.id; +id date1 date2 DATE_ADD(a.date1,INTERVAL -10 DAY) TO_DAYS(a.date1)-10 +12 2011-05-12 2011-05-02 2011-05-02 734624 +13 NULL NULL NULL NULL +14 2009-10-23 2009-10-13 2009-10-13 734058 +15 2014-10-30 2014-10-20 2014-10-20 735891 +16 NULL NULL NULL NULL +17 NULL NULL NULL NULL +18 2010-10-13 2010-10-03 2010-10-03 734413 +DROP TABLE t1; +# +# MDEV-10524 Assertion `arg1_int >= 0' failed in Item_func_additive_op::result_precision() +# +SELECT 1 MOD ADDTIME( '13:58:57', '00:00:01' ) + 2; +1 MOD ADDTIME( '13:58:57', '00:00:01' ) + 2 +3 +# +# MDEV-11819 NO_ZERO_IN_DATE: Incorrect generated column value +# +SET sql_mode='NO_ZERO_IN_DATE'; +CREATE TABLE t1 (a TIME(6)); +INSERT INTO t1 SELECT timediff(timestamp'2008-12-31 23:59:59.000001',timestamp'2008-12-30 01:01:01.000002'); +SELECT * FROM t1; +a +46:58:57.999999 +DROP TABLE t1; +SET sql_mode=DEFAULT; +# +# MDEV-13972 crash in Item_func_sec_to_time::get_date +# +DO TO_DAYS(SEC_TO_TIME(TIME(CEILING(UUID())))); +DO TO_DAYS(SEC_TO_TIME(MAKEDATE('',RAND(~(''))))); +Warnings: +Warning 1292 Truncated incorrect INTEGER value: '' +Warning 1292 Truncated incorrect INTEGER value: '' +Warning 1292 Truncated incorrect INTEGER value: '' +Warning 1292 Truncated incorrect time value: '20000101' +SELECT SEC_TO_TIME(MAKEDATE(0,RAND(~0))); +SEC_TO_TIME(MAKEDATE(0,RAND(~0))) +838:59:59 +Warnings: +Warning 1292 Truncated incorrect time value: '20000101' +# +# End of 5.5 tests +# +# +# MDEV-8205 timediff returns null when comparing decimal time to time string value +# +SELECT +TIMEDIFF('2014-01-01 00:00:00' , '2014-01-01 01:00:00' ) AS str_str, +TIMEDIFF('2014-01-01 00:00:00' , 20140101010000.000 ) AS str_dec, +TIMEDIFF(20140101000000.000 , 20140101010000.000 ) AS dec_dec, +TIMEDIFF(20140101000000.000 , '2014-01-01 01:00:00' ) AS dec_str; +str_str str_dec dec_dec dec_str +-01:00:00 -01:00:00.000 -01:00:00.000 -01:00:00.000 +SELECT +TIMEDIFF('2014-01-01 00:00:00' , '2014-01-02 01:00:00' ) AS str_str, +TIMEDIFF('2014-01-01 00:00:00' , 20140102010000.000 ) AS str_dec, +TIMEDIFF(20140101000000.000 , 20140102010000.000 ) AS dec_dec, +TIMEDIFF(20140101000000.000 , '2014-01-02 01:00:00' ) AS dec_str; +str_str str_dec dec_dec dec_str +-25:00:00 -25:00:00.000 -25:00:00.000 -25:00:00.000 +SELECT +TIMEDIFF('2014-01-01 00:00:00' , '2014-02-02 01:00:00' ) AS str_str, +TIMEDIFF('2014-01-01 00:00:00' , 20140202010000.000 ) AS str_dec, +TIMEDIFF(20140101000000.000 , 20140202010000.000 ) AS dec_dec, +TIMEDIFF(20140101000000.000 , '2014-02-02 01:00:00' ) AS dec_str; +str_str str_dec dec_dec dec_str +-769:00:00 -769:00:00.000 -769:00:00.000 -769:00:00.000 +SELECT +TIMEDIFF('2014-01-01 00:00:00' , '2014-03-02 01:00:00' ) AS str_str, +TIMEDIFF('2014-01-01 00:00:00' , 20140302010000.000 ) AS str_dec, +TIMEDIFF(20140101000000.000 , 20140302010000.000 ) AS dec_dec, +TIMEDIFF(20140101000000.000 , '2014-03-02 01:00:00' ) AS dec_str; +str_str str_dec dec_dec dec_str +-838:59:59 -838:59:59.999 -838:59:59.999 -838:59:59.999 +Warnings: +Warning 1292 Truncated incorrect time value: '-1441:00:00' +Warning 1292 Truncated incorrect time value: '-1441:00:00' +Warning 1292 Truncated incorrect time value: '-1441:00:00' +Warning 1292 Truncated incorrect time value: '-1441:00:00' +# +# MDEV-10787 Assertion `ltime->neg == 0' failed in void date_to_datetime(MYSQL_TIME*) +# +CREATE TABLE t1 (d DATE); +INSERT INTO t1 VALUES ('2005-07-20'),('2012-12-21'); +SELECT REPLACE( ADDDATE( d, INTERVAL 0.6732771076944444 HOUR_SECOND ), '2', 'x' ) FROM t1; +REPLACE( ADDDATE( d, INTERVAL 0.6732771076944444 HOUR_SECOND ), '2', 'x' ) +NULL +NULL +Warnings: +Warning 1441 Datetime function: datetime field overflow +Warning 1441 Datetime function: datetime field overflow +SELECT REPLACE( ADDDATE( d, INTERVAL '0.6732771076944444' HOUR_SECOND ), '2', 'x' ) FROM t1; +REPLACE( ADDDATE( d, INTERVAL '0.6732771076944444' HOUR_SECOND ), '2', 'x' ) +NULL +NULL +Warnings: +Warning 1441 Datetime function: datetime field overflow +Warning 1441 Datetime function: datetime field overflow +SELECT CAST(ADDDATE( d, INTERVAL 6732771076944444 SECOND) AS CHAR) FROM t1; +CAST(ADDDATE( d, INTERVAL 6732771076944444 SECOND) AS CHAR) +NULL +NULL +Warnings: +Warning 1441 Datetime function: datetime field overflow +Warning 1441 Datetime function: datetime field overflow +SELECT CAST(ADDDATE( d, INTERVAL '67327710769444:44' HOUR_SECOND) AS CHAR) FROM t1; +CAST(ADDDATE( d, INTERVAL '67327710769444:44' HOUR_SECOND) AS CHAR) +NULL +NULL +Warnings: +Warning 1441 Datetime function: datetime field overflow +Warning 1441 Datetime function: datetime field overflow +SELECT CAST(ADDDATE( d, INTERVAL '673277107694:44:44' HOUR_SECOND) AS CHAR) FROM t1; +CAST(ADDDATE( d, INTERVAL '673277107694:44:44' HOUR_SECOND) AS CHAR) +NULL +NULL +Warnings: +Warning 1441 Datetime function: datetime field overflow +Warning 1441 Datetime function: datetime field overflow +DROP TABLE t1; +SELECT ADDDATE(DATE'0000-01-01', INTERVAL '3652423:23:59:59' DAY_SECOND); +ADDDATE(DATE'0000-01-01', INTERVAL '3652423:23:59:59' DAY_SECOND) +9999-12-31 23:59:59 +SELECT ADDDATE(DATE'0000-01-01', INTERVAL '0:87658175:59:59' DAY_SECOND); +ADDDATE(DATE'0000-01-01', INTERVAL '0:87658175:59:59' DAY_SECOND) +9999-12-31 23:59:59 +SELECT ADDDATE(DATE'0000-01-01', INTERVAL '0:0:5259490559:59' DAY_SECOND); +ADDDATE(DATE'0000-01-01', INTERVAL '0:0:5259490559:59' DAY_SECOND) +9999-12-31 23:59:59 +SELECT ADDDATE(DATE'0000-01-01', INTERVAL '0:0:0:315569433599' DAY_SECOND); +ADDDATE(DATE'0000-01-01', INTERVAL '0:0:0:315569433599' DAY_SECOND) +9999-12-31 23:59:59 +SELECT ADDDATE(DATE'0000-01-01', INTERVAL '3652423:0:0:315569433559' DAY_SECOND); +ADDDATE(DATE'0000-01-01', INTERVAL '3652423:0:0:315569433559' DAY_SECOND) +NULL +Warnings: +Warning 1441 Datetime function: datetime field overflow +SELECT ADDDATE(DATE'0000-01-01', INTERVAL '0:87658175:0:315569433559' DAY_SECOND); +ADDDATE(DATE'0000-01-01', INTERVAL '0:87658175:0:315569433559' DAY_SECOND) +NULL +Warnings: +Warning 1441 Datetime function: datetime field overflow +SELECT ADDDATE(DATE'0000-01-01', INTERVAL '0:0:5259490559:315569433599' DAY_SECOND); +ADDDATE(DATE'0000-01-01', INTERVAL '0:0:5259490559:315569433599' DAY_SECOND) +NULL +Warnings: +Warning 1441 Datetime function: datetime field overflow +# +# MDEV-13202 Assertion `ltime->neg == 0' failed in date_to_datetime +# +CREATE TABLE t1 (i INT, d DATE); +INSERT INTO t1 VALUES (1, '1970-01-01'); +SELECT MAX(NULLIF(i,1)) FROM t1 ORDER BY DATE_SUB(d,INTERVAL 17300000 HOUR); +MAX(NULLIF(i,1)) +NULL +Warnings: +Warning 1441 Datetime function: datetime field overflow +DROP TABLE t1; +CREATE TABLE t1 (i INT, d DATE); +INSERT INTO t1 VALUES (1, '1970-01-01'); +SELECT CONCAT(DATE_SUB(d, INTERVAL 17300000 HOUR)) FROM t1; +CONCAT(DATE_SUB(d, INTERVAL 17300000 HOUR)) +NULL +Warnings: +Warning 1441 Datetime function: datetime field overflow +DROP TABLE t1; +SELECT CONCAT(DATE_SUB(TIMESTAMP'1970-01-01 00:00:00', INTERVAL 17300000 HOUR)); +CONCAT(DATE_SUB(TIMESTAMP'1970-01-01 00:00:00', INTERVAL 17300000 HOUR)) +NULL +Warnings: +Warning 1441 Datetime function: datetime field overflow +# +# End of 10.0 tests +# +# +# Start of 10.1 tests +# +# +# MDEV-10317 EXCTACT(MINUTE_MICROSECOND) truncates data +# +CREATE TABLE t1 (a DATETIME(6)); +INSERT INTO t1 VALUES ('1999-12-31 23:59:59.999999'); +SELECT +a, +EXTRACT(YEAR FROM a), +EXTRACT(YEAR_MONTH FROM a), +EXTRACT(QUARTER FROM a), +EXTRACT(MONTH FROM a), +EXTRACT(WEEK FROM a), +EXTRACT(DAY FROM a), +EXTRACT(DAY_HOUR FROM a), +EXTRACT(DAY_MINUTE FROM a), +EXTRACT(DAY_SECOND FROM a), +EXTRACT(HOUR FROM a), +EXTRACT(HOUR_MINUTE FROM a), +EXTRACT(HOUR_SECOND FROM a), +EXTRACT(MINUTE FROM a), +EXTRACT(MINUTE_SECOND FROM a), +EXTRACT(SECOND FROM a), +EXTRACT(MICROSECOND FROM a), +EXTRACT(DAY_MICROSECOND FROM a), +EXTRACT(HOUR_MICROSECOND FROM a), +EXTRACT(MINUTE_MICROSECOND FROM a), +EXTRACT(SECOND_MICROSECOND FROM a) +FROM t1; +Catalog Database Table Table_alias Column Column_alias Type Length Max length Is_null Flags Decimals Charsetnr +def test t1 t1 a a 12 26 26 Y 128 6 63 +def EXTRACT(YEAR FROM a) 3 4 4 Y 32896 0 63 +def EXTRACT(YEAR_MONTH FROM a) 3 6 6 Y 32896 0 63 +def EXTRACT(QUARTER FROM a) 3 2 1 Y 32896 0 63 +def EXTRACT(MONTH FROM a) 3 2 2 Y 32896 0 63 +def EXTRACT(WEEK FROM a) 3 2 2 Y 32896 0 63 +def EXTRACT(DAY FROM a) 3 2 2 Y 32896 0 63 +def EXTRACT(DAY_HOUR FROM a) 3 5 4 Y 32896 0 63 +def EXTRACT(DAY_MINUTE FROM a) 3 7 6 Y 32896 0 63 +def EXTRACT(DAY_SECOND FROM a) 3 9 8 Y 32896 0 63 +def EXTRACT(HOUR FROM a) 3 3 2 Y 32896 0 63 +def EXTRACT(HOUR_MINUTE FROM a) 3 5 4 Y 32896 0 63 +def EXTRACT(HOUR_SECOND FROM a) 3 7 6 Y 32896 0 63 +def EXTRACT(MINUTE FROM a) 3 3 2 Y 32896 0 63 +def EXTRACT(MINUTE_SECOND FROM a) 3 5 4 Y 32896 0 63 +def EXTRACT(SECOND FROM a) 3 3 2 Y 32896 0 63 +def EXTRACT(MICROSECOND FROM a) 3 7 6 Y 32896 0 63 +def EXTRACT(DAY_MICROSECOND FROM a) 8 15 14 Y 32896 0 63 +def EXTRACT(HOUR_MICROSECOND FROM a) 8 13 12 Y 32896 0 63 +def EXTRACT(MINUTE_MICROSECOND FROM a) 8 11 10 Y 32896 0 63 +def EXTRACT(SECOND_MICROSECOND FROM a) 3 9 8 Y 32896 0 63 +a 1999-12-31 23:59:59.999999 +EXTRACT(YEAR FROM a) 1999 +EXTRACT(YEAR_MONTH FROM a) 199912 +EXTRACT(QUARTER FROM a) 4 +EXTRACT(MONTH FROM a) 12 +EXTRACT(WEEK FROM a) 52 +EXTRACT(DAY FROM a) 31 +EXTRACT(DAY_HOUR FROM a) 3123 +EXTRACT(DAY_MINUTE FROM a) 312359 +EXTRACT(DAY_SECOND FROM a) 31235959 +EXTRACT(HOUR FROM a) 23 +EXTRACT(HOUR_MINUTE FROM a) 2359 +EXTRACT(HOUR_SECOND FROM a) 235959 +EXTRACT(MINUTE FROM a) 59 +EXTRACT(MINUTE_SECOND FROM a) 5959 +EXTRACT(SECOND FROM a) 59 +EXTRACT(MICROSECOND FROM a) 999999 +EXTRACT(DAY_MICROSECOND FROM a) 31235959999999 +EXTRACT(HOUR_MICROSECOND FROM a) 235959999999 +EXTRACT(MINUTE_MICROSECOND FROM a) 5959999999 +EXTRACT(SECOND_MICROSECOND FROM a) 59999999 +CREATE TABLE t2 AS SELECT +a, +EXTRACT(YEAR FROM a), +EXTRACT(YEAR_MONTH FROM a), +EXTRACT(QUARTER FROM a), +EXTRACT(MONTH FROM a), +EXTRACT(WEEK FROM a), +EXTRACT(DAY FROM a), +EXTRACT(DAY_HOUR FROM a), +EXTRACT(DAY_MINUTE FROM a), +EXTRACT(DAY_SECOND FROM a), +EXTRACT(HOUR FROM a), +EXTRACT(HOUR_MINUTE FROM a), +EXTRACT(HOUR_SECOND FROM a), +EXTRACT(MINUTE FROM a), +EXTRACT(MINUTE_SECOND FROM a), +EXTRACT(SECOND FROM a), +EXTRACT(MICROSECOND FROM a), +EXTRACT(DAY_MICROSECOND FROM a), +EXTRACT(HOUR_MICROSECOND FROM a), +EXTRACT(MINUTE_MICROSECOND FROM a), +EXTRACT(SECOND_MICROSECOND FROM a) +FROM t1; +SELECT * FROM t2; +a 1999-12-31 23:59:59.999999 +EXTRACT(YEAR FROM a) 1999 +EXTRACT(YEAR_MONTH FROM a) 199912 +EXTRACT(QUARTER FROM a) 4 +EXTRACT(MONTH FROM a) 12 +EXTRACT(WEEK FROM a) 52 +EXTRACT(DAY FROM a) 31 +EXTRACT(DAY_HOUR FROM a) 3123 +EXTRACT(DAY_MINUTE FROM a) 312359 +EXTRACT(DAY_SECOND FROM a) 31235959 +EXTRACT(HOUR FROM a) 23 +EXTRACT(HOUR_MINUTE FROM a) 2359 +EXTRACT(HOUR_SECOND FROM a) 235959 +EXTRACT(MINUTE FROM a) 59 +EXTRACT(MINUTE_SECOND FROM a) 5959 +EXTRACT(SECOND FROM a) 59 +EXTRACT(MICROSECOND FROM a) 999999 +EXTRACT(DAY_MICROSECOND FROM a) 31235959999999 +EXTRACT(HOUR_MICROSECOND FROM a) 235959999999 +EXTRACT(MINUTE_MICROSECOND FROM a) 5959999999 +EXTRACT(SECOND_MICROSECOND FROM a) 59999999 +SHOW CREATE TABLE t2; +Table Create Table +t2 CREATE TABLE `t2` ( + `a` datetime(6) DEFAULT NULL, + `EXTRACT(YEAR FROM a)` int(4) DEFAULT NULL, + `EXTRACT(YEAR_MONTH FROM a)` int(6) DEFAULT NULL, + `EXTRACT(QUARTER FROM a)` int(2) DEFAULT NULL, + `EXTRACT(MONTH FROM a)` int(2) DEFAULT NULL, + `EXTRACT(WEEK FROM a)` int(2) DEFAULT NULL, + `EXTRACT(DAY FROM a)` int(2) DEFAULT NULL, + `EXTRACT(DAY_HOUR FROM a)` int(5) DEFAULT NULL, + `EXTRACT(DAY_MINUTE FROM a)` int(7) DEFAULT NULL, + `EXTRACT(DAY_SECOND FROM a)` int(9) DEFAULT NULL, + `EXTRACT(HOUR FROM a)` int(3) DEFAULT NULL, + `EXTRACT(HOUR_MINUTE FROM a)` int(5) DEFAULT NULL, + `EXTRACT(HOUR_SECOND FROM a)` int(7) DEFAULT NULL, + `EXTRACT(MINUTE FROM a)` int(3) DEFAULT NULL, + `EXTRACT(MINUTE_SECOND FROM a)` int(5) DEFAULT NULL, + `EXTRACT(SECOND FROM a)` int(3) DEFAULT NULL, + `EXTRACT(MICROSECOND FROM a)` int(7) DEFAULT NULL, + `EXTRACT(DAY_MICROSECOND FROM a)` bigint(15) DEFAULT NULL, + `EXTRACT(HOUR_MICROSECOND FROM a)` bigint(13) DEFAULT NULL, + `EXTRACT(MINUTE_MICROSECOND FROM a)` bigint(11) DEFAULT NULL, + `EXTRACT(SECOND_MICROSECOND FROM a)` int(9) DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +DROP TABLE t1,t2; +CREATE TABLE t1 (a TIME(6)); +INSERT INTO t1 VALUES ('-838:59:59.999999'),('838:59:59.999999'); +SELECT +a, +EXTRACT(YEAR FROM a), +EXTRACT(YEAR_MONTH FROM a), +EXTRACT(QUARTER FROM a), +EXTRACT(MONTH FROM a), +EXTRACT(WEEK FROM a), +EXTRACT(DAY FROM a), +EXTRACT(DAY_HOUR FROM a), +EXTRACT(DAY_MINUTE FROM a), +EXTRACT(DAY_SECOND FROM a), +EXTRACT(HOUR FROM a), +EXTRACT(HOUR_MINUTE FROM a), +EXTRACT(HOUR_SECOND FROM a), +EXTRACT(MINUTE FROM a), +EXTRACT(MINUTE_SECOND FROM a), +EXTRACT(SECOND FROM a), +EXTRACT(MICROSECOND FROM a), +EXTRACT(DAY_MICROSECOND FROM a), +EXTRACT(HOUR_MICROSECOND FROM a), +EXTRACT(MINUTE_MICROSECOND FROM a), +EXTRACT(SECOND_MICROSECOND FROM a) +FROM t1; +Catalog Database Table Table_alias Column Column_alias Type Length Max length Is_null Flags Decimals Charsetnr +def test t1 t1 a a 11 17 17 Y 128 6 63 +def EXTRACT(YEAR FROM a) 3 4 1 Y 32896 0 63 +def EXTRACT(YEAR_MONTH FROM a) 3 6 1 Y 32896 0 63 +def EXTRACT(QUARTER FROM a) 3 2 1 Y 32896 0 63 +def EXTRACT(MONTH FROM a) 3 2 1 Y 32896 0 63 +def EXTRACT(WEEK FROM a) 3 2 9 Y 32896 0 63 +def EXTRACT(DAY FROM a) 3 2 2 Y 32896 0 63 +def EXTRACT(DAY_HOUR FROM a) 3 5 5 Y 32896 0 63 +def EXTRACT(DAY_MINUTE FROM a) 3 7 7 Y 32896 0 63 +def EXTRACT(DAY_SECOND FROM a) 3 9 9 Y 32896 0 63 +def EXTRACT(HOUR FROM a) 3 3 3 Y 32896 0 63 +def EXTRACT(HOUR_MINUTE FROM a) 3 5 5 Y 32896 0 63 +def EXTRACT(HOUR_SECOND FROM a) 3 7 7 Y 32896 0 63 +def EXTRACT(MINUTE FROM a) 3 3 3 Y 32896 0 63 +def EXTRACT(MINUTE_SECOND FROM a) 3 5 5 Y 32896 0 63 +def EXTRACT(SECOND FROM a) 3 3 3 Y 32896 0 63 +def EXTRACT(MICROSECOND FROM a) 3 7 7 Y 32896 0 63 +def EXTRACT(DAY_MICROSECOND FROM a) 8 15 15 Y 32896 0 63 +def EXTRACT(HOUR_MICROSECOND FROM a) 8 13 13 Y 32896 0 63 +def EXTRACT(MINUTE_MICROSECOND FROM a) 8 11 11 Y 32896 0 63 +def EXTRACT(SECOND_MICROSECOND FROM a) 3 9 9 Y 32896 0 63 +a -838:59:59.999999 +EXTRACT(YEAR FROM a) 0 +EXTRACT(YEAR_MONTH FROM a) 0 +EXTRACT(QUARTER FROM a) 0 +EXTRACT(MONTH FROM a) 0 +EXTRACT(WEEK FROM a) 613566757 +EXTRACT(DAY FROM a) 34 +EXTRACT(DAY_HOUR FROM a) -3422 +EXTRACT(DAY_MINUTE FROM a) -342259 +EXTRACT(DAY_SECOND FROM a) -34225959 +EXTRACT(HOUR FROM a) -22 +EXTRACT(HOUR_MINUTE FROM a) -2259 +EXTRACT(HOUR_SECOND FROM a) -225959 +EXTRACT(MINUTE FROM a) -59 +EXTRACT(MINUTE_SECOND FROM a) -5959 +EXTRACT(SECOND FROM a) -59 +EXTRACT(MICROSECOND FROM a) -999999 +EXTRACT(DAY_MICROSECOND FROM a) -34225959999999 +EXTRACT(HOUR_MICROSECOND FROM a) -225959999999 +EXTRACT(MINUTE_MICROSECOND FROM a) -5959999999 +EXTRACT(SECOND_MICROSECOND FROM a) -59999999 +a 838:59:59.999999 +EXTRACT(YEAR FROM a) 0 +EXTRACT(YEAR_MONTH FROM a) 0 +EXTRACT(QUARTER FROM a) 0 +EXTRACT(MONTH FROM a) 0 +EXTRACT(WEEK FROM a) 613566757 +EXTRACT(DAY FROM a) 34 +EXTRACT(DAY_HOUR FROM a) 3422 +EXTRACT(DAY_MINUTE FROM a) 342259 +EXTRACT(DAY_SECOND FROM a) 34225959 +EXTRACT(HOUR FROM a) 22 +EXTRACT(HOUR_MINUTE FROM a) 2259 +EXTRACT(HOUR_SECOND FROM a) 225959 +EXTRACT(MINUTE FROM a) 59 +EXTRACT(MINUTE_SECOND FROM a) 5959 +EXTRACT(SECOND FROM a) 59 +EXTRACT(MICROSECOND FROM a) 999999 +EXTRACT(DAY_MICROSECOND FROM a) 34225959999999 +EXTRACT(HOUR_MICROSECOND FROM a) 225959999999 +EXTRACT(MINUTE_MICROSECOND FROM a) 5959999999 +EXTRACT(SECOND_MICROSECOND FROM a) 59999999 +CREATE TABLE t2 AS SELECT +a, +EXTRACT(YEAR FROM a), +EXTRACT(YEAR_MONTH FROM a), +EXTRACT(QUARTER FROM a), +EXTRACT(MONTH FROM a), +EXTRACT(WEEK FROM a), +EXTRACT(DAY FROM a), +EXTRACT(DAY_HOUR FROM a), +EXTRACT(DAY_MINUTE FROM a), +EXTRACT(DAY_SECOND FROM a), +EXTRACT(HOUR FROM a), +EXTRACT(HOUR_MINUTE FROM a), +EXTRACT(HOUR_SECOND FROM a), +EXTRACT(MINUTE FROM a), +EXTRACT(MINUTE_SECOND FROM a), +EXTRACT(SECOND FROM a), +EXTRACT(MICROSECOND FROM a), +EXTRACT(DAY_MICROSECOND FROM a), +EXTRACT(HOUR_MICROSECOND FROM a), +EXTRACT(MINUTE_MICROSECOND FROM a), +EXTRACT(SECOND_MICROSECOND FROM a) +FROM t1; +SELECT * FROM t2; +a -838:59:59.999999 +EXTRACT(YEAR FROM a) 0 +EXTRACT(YEAR_MONTH FROM a) 0 +EXTRACT(QUARTER FROM a) 0 +EXTRACT(MONTH FROM a) 0 +EXTRACT(WEEK FROM a) 613566757 +EXTRACT(DAY FROM a) 34 +EXTRACT(DAY_HOUR FROM a) -3422 +EXTRACT(DAY_MINUTE FROM a) -342259 +EXTRACT(DAY_SECOND FROM a) -34225959 +EXTRACT(HOUR FROM a) -22 +EXTRACT(HOUR_MINUTE FROM a) -2259 +EXTRACT(HOUR_SECOND FROM a) -225959 +EXTRACT(MINUTE FROM a) -59 +EXTRACT(MINUTE_SECOND FROM a) -5959 +EXTRACT(SECOND FROM a) -59 +EXTRACT(MICROSECOND FROM a) -999999 +EXTRACT(DAY_MICROSECOND FROM a) -34225959999999 +EXTRACT(HOUR_MICROSECOND FROM a) -225959999999 +EXTRACT(MINUTE_MICROSECOND FROM a) -5959999999 +EXTRACT(SECOND_MICROSECOND FROM a) -59999999 +a 838:59:59.999999 +EXTRACT(YEAR FROM a) 0 +EXTRACT(YEAR_MONTH FROM a) 0 +EXTRACT(QUARTER FROM a) 0 +EXTRACT(MONTH FROM a) 0 +EXTRACT(WEEK FROM a) 613566757 +EXTRACT(DAY FROM a) 34 +EXTRACT(DAY_HOUR FROM a) 3422 +EXTRACT(DAY_MINUTE FROM a) 342259 +EXTRACT(DAY_SECOND FROM a) 34225959 +EXTRACT(HOUR FROM a) 22 +EXTRACT(HOUR_MINUTE FROM a) 2259 +EXTRACT(HOUR_SECOND FROM a) 225959 +EXTRACT(MINUTE FROM a) 59 +EXTRACT(MINUTE_SECOND FROM a) 5959 +EXTRACT(SECOND FROM a) 59 +EXTRACT(MICROSECOND FROM a) 999999 +EXTRACT(DAY_MICROSECOND FROM a) 34225959999999 +EXTRACT(HOUR_MICROSECOND FROM a) 225959999999 +EXTRACT(MINUTE_MICROSECOND FROM a) 5959999999 +EXTRACT(SECOND_MICROSECOND FROM a) 59999999 +SHOW CREATE TABLE t2; +Table Create Table +t2 CREATE TABLE `t2` ( + `a` time(6) DEFAULT NULL, + `EXTRACT(YEAR FROM a)` int(4) DEFAULT NULL, + `EXTRACT(YEAR_MONTH FROM a)` int(6) DEFAULT NULL, + `EXTRACT(QUARTER FROM a)` int(2) DEFAULT NULL, + `EXTRACT(MONTH FROM a)` int(2) DEFAULT NULL, + `EXTRACT(WEEK FROM a)` int(2) DEFAULT NULL, + `EXTRACT(DAY FROM a)` int(2) DEFAULT NULL, + `EXTRACT(DAY_HOUR FROM a)` int(5) DEFAULT NULL, + `EXTRACT(DAY_MINUTE FROM a)` int(7) DEFAULT NULL, + `EXTRACT(DAY_SECOND FROM a)` int(9) DEFAULT NULL, + `EXTRACT(HOUR FROM a)` int(3) DEFAULT NULL, + `EXTRACT(HOUR_MINUTE FROM a)` int(5) DEFAULT NULL, + `EXTRACT(HOUR_SECOND FROM a)` int(7) DEFAULT NULL, + `EXTRACT(MINUTE FROM a)` int(3) DEFAULT NULL, + `EXTRACT(MINUTE_SECOND FROM a)` int(5) DEFAULT NULL, + `EXTRACT(SECOND FROM a)` int(3) DEFAULT NULL, + `EXTRACT(MICROSECOND FROM a)` int(7) DEFAULT NULL, + `EXTRACT(DAY_MICROSECOND FROM a)` bigint(15) DEFAULT NULL, + `EXTRACT(HOUR_MICROSECOND FROM a)` bigint(13) DEFAULT NULL, + `EXTRACT(MINUTE_MICROSECOND FROM a)` bigint(11) DEFAULT NULL, + `EXTRACT(SECOND_MICROSECOND FROM a)` int(9) DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +DROP TABLE t1,t2; +# +# End of 10.1 tests +# +# +# Start of 10.3 tests +# +# +# MDEV-12515 Wrong value when storing DATE_ADD() and ADDTIME() to a numeric field +# +SET sql_mode=''; +CREATE TABLE t1 AS SELECT +DATE_ADD('2001-01-01',INTERVAL 1 DAY) AS c1, +ADDTIME('10:20:30',1) AS c2; +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `c1` varchar(19) DEFAULT NULL, + `c2` varchar(26) DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +SELECT * FROM t1; +c1 c2 +2001-01-02 10:20:31 +DROP TABLE t1; +CREATE TABLE t2 (c INT); +INSERT INTO t2 SELECT DATE_ADD('2001-01-01',INTERVAL 1 DAY); +Warnings: +Warning 1265 Data truncated for column 'c' at row 1 +INSERT INTO t2 VALUES ('2001-01-02'); +Warnings: +Warning 1265 Data truncated for column 'c' at row 1 +SELECT * FROM t2; +c +2001 +2001 +DROP TABLE t2; +CREATE TABLE t2 (a INT); +INSERT INTO t2 VALUES (ADDTIME('10:20:30',1)); +Warnings: +Warning 1265 Data truncated for column 'a' at row 1 +INSERT INTO t2 VALUES ('10:20:31'); +Warnings: +Warning 1265 Data truncated for column 'a' at row 1 +SELECT * FROM t2; +a +10 +10 +DROP TABLE t2; +SET sql_mode=DEFAULT; +# +# MDEV-12860 Out-of-range error on CREATE..SELECT with a view using MAX and EXTRACT(MINUTE_MICROSECOND..) +# +SET sql_mode=STRICT_ALL_TABLES; +CREATE TABLE t1 ( +id bigint(11) NOT NULL PRIMARY KEY, +dt datetime(6) +); +INSERT INTO t1 VALUES (1,'2001-01-01 11:22:33.123456'); +CREATE OR REPLACE VIEW v1 AS SELECT EXTRACT(MINUTE_MICROSECOND FROM dt) AS dt2 FROM t1; +DESCRIBE v1; +Field Type Null Key Default Extra +dt2 bigint(11) YES NULL +SELECT * FROM v1; +dt2 +2233123456 +CREATE TABLE t2 AS SELECT MAX(dt2) FROM v1; +DESCRIBE t2; +Field Type Null Key Default Extra +MAX(dt2) bigint(11) YES NULL +SELECT * FROM t2; +MAX(dt2) +2233123456 +DROP TABLE t2; +DROP VIEW v1; +DROP TABLE t1; +SET sql_mode=DEFAULT; +# +# MDEV-12866 Out-of-range error with CREATE..SELECT..TO_SECONDS(NOW()) +# +SET sql_mode=STRICT_ALL_TABLES; +CREATE TABLE t1 AS SELECT TO_SECONDS('9999-12-31 23:59:59'); +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `TO_SECONDS('9999-12-31 23:59:59')` bigint(12) DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +DROP TABLE t1; +SET sql_mode=DEFAULT; +# +# MDEV-13966 Parameter data type control for Item_temporal_func +# +SELECT FROM_DAYS(ROW(1,1)); +ERROR HY000: Illegal parameter data type row for operation 'from_days' +SELECT MAKEDATE(ROW(1,1),1); +ERROR HY000: Illegal parameter data type row for operation 'makedate' +SELECT MAKEDATE(1, ROW(1,1)); +ERROR HY000: Illegal parameter data type row for operation 'makedate' +SELECT LAST_DAY(ROW(1,1)); +ERROR HY000: Illegal parameter data type row for operation 'last_day' +SELECT SEC_TO_TIME(ROW(1,1)); +ERROR HY000: Illegal parameter data type row for operation 'sec_to_time' +SELECT TIMEDIFF(ROW(1,1),1); +ERROR HY000: Illegal parameter data type row for operation 'timediff' +SELECT TIMEDIFF(1, ROW(1,1)); +ERROR HY000: Illegal parameter data type row for operation 'timediff' +SELECT MAKETIME(ROW(1,1),1,1); +ERROR HY000: Illegal parameter data type row for operation 'maketime' +SELECT MAKETIME(1, ROW(1,1), 1); +ERROR HY000: Illegal parameter data type row for operation 'maketime' +SELECT MAKETIME(1, 1, ROW(1,1)); +ERROR HY000: Illegal parameter data type row for operation 'maketime' +SELECT FROM_UNIXTIME(ROW(1,1)); +ERROR HY000: Illegal parameter data type row for operation 'from_unixtime' +SELECT CONVERT_TZ(ROW(1,1),1,1); +ERROR HY000: Illegal parameter data type row for operation 'convert_tz' +SELECT CONVERT_TZ(1, ROW(1,1), 1); +ERROR HY000: Illegal parameter data type row for operation 'convert_tz' +SELECT CONVERT_TZ(1, 1, ROW(1,1)); +ERROR HY000: Illegal parameter data type row for operation 'convert_tz' +# +# MDEV-15340 Wrong result HOUR(case_expression_with_time_and_datetime) +# +SET TIMESTAMP=UNIX_TIMESTAMP('2018-02-17 01:02:03'); +SELECT +COALESCE(TIME'800:00:00', NOW()) AS c, +HOUR(COALESCE(TIME'800:00:00',NOW())) AS hc; +c hc +2018-03-22 08:00:00 8 +SELECT +CASE WHEN TRUE THEN TIME'800:00:00' ELSE NOW() END AS c, +HOUR(CASE WHEN TRUE THEN TIME'800:00:00' ELSE NOW() END) AS hc; +c hc +2018-03-22 08:00:00 8 +SELECT +IFNULL(TIME'800:00:00', NOW()) AS c, +HOUR(IFNULL(TIME'800:00:00', NOW())) AS hc; +c hc +2018-03-22 08:00:00 8 +SELECT +IF(TRUE,TIME'800:00:00', NOW()) AS c, +HOUR(IF(TRUE,TIME'800:00:00', NOW())) AS hc; +c hc +2018-03-22 08:00:00 8 +SELECT +ADDTIME(TIME'10:20:30', TIMESTAMP'2001-01-01 00:00:00') AS c1, +ADDTIME(TIME'10:20:30', COALESCE(TIMESTAMP'2001-01-01 00:00:00',TIMESTAMP'2001-01-01 00:00:00')) AS c2, +ADDTIME(TIME'10:20:30', DATE'2001-01-01') AS c3, +ADDTIME(TIME'10:20:30', COALESCE(DATE'2001-01-01',TIMESTAMP'2001-01-01 00:00:00')) AS c4; +c1 c2 c3 c4 +NULL NULL NULL NULL +SELECT +HOUR(TIMESTAMP'0000-00-01 10:00:00') AS h0, +TIME_TO_SEC(TIMESTAMP'0000-00-01 10:00:00') AS tts0, +TIME_TO_SEC(TIMESTAMP'0000-00-01 10:00:00.1') AS tts1, +CAST(TIMESTAMP'0000-00-01 10:00:00' AS TIME) AS c0, +CAST(TIMESTAMP'0000-00-01 10:00:00.1' AS TIME(1)) AS c2; +h0 tts0 tts1 c0 c2 +10 36000 36000.1 10:00:00 10:00:00.1 +SET TIMESTAMP=DEFAULT; +# +# MDEV-15363 Wrong result for CAST(LAST_DAY(TIME'00:00:00') AS TIME) +# +SET TIMESTAMP=UNIX_TIMESTAMP('2018-02-17 01:02:03'); +SELECT +LAST_DAY(TIME'00:00:00') AS c1, +CAST(CAST(LAST_DAY(TIME'00:00:00') AS DATE) AS TIME) AS c2, +CAST(LAST_DAY(TIME'00:00:00') AS TIME) AS c3; +c1 c2 c3 +2018-02-28 00:00:00 00:00:00 +SET TIMESTAMP=DEFAULT; +# +# MDEV-15702 Remove the use of STRING_ITEM from Item_func_date_format::fix_length_and_dec() +# +SELECT DATE_FORMAT('2001-01-01',POINT(1,1)); +ERROR HY000: Illegal parameter data type geometry for operation 'date_format' +SELECT DATE_FORMAT(POINT(1,1),'10'); +ERROR HY000: Illegal parameter data type geometry for operation 'date_format' +SELECT DATE_FORMAT('2001-01-01',ROW(1,1)); +ERROR HY000: Illegal parameter data type row for operation 'date_format' +SELECT DATE_FORMAT(ROW(1,1),'10'); +ERROR HY000: Illegal parameter data type row for operation 'date_format' +SELECT DATE_FORMAT('2001-01-01','%Y',POINT(1,1)); +ERROR HY000: Illegal parameter data type geometry for operation 'date_format' +SELECT DATE_FORMAT('2001-01-01','%Y',ROW(1,1)); +ERROR HY000: Illegal parameter data type row for operation 'date_format' +SELECT DATE_FORMAT('2001-01-01','%Y',@unknown_user_variable); +DATE_FORMAT('2001-01-01','%Y',@unknown_user_variable) +2001 +Warnings: +Warning 1649 Unknown locale: 'NULL' +CREATE TABLE t1 AS SELECT +DATE_FORMAT('2001-01-01',NULL) AS c0, +DATE_FORMAT('2001-01-01','10') AS c1, +DATE_FORMAT('2001-01-01',10) AS c2, +DATE_FORMAT('2001-01-01',10.0) AS c3, +DATE_FORMAT('2001-01-01',10e0) AS c4, +DATE_FORMAT('2001-01-01',TIME'10:20:30') AS c5; +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `c0` char(0) DEFAULT NULL, + `c1` varchar(2) DEFAULT NULL, + `c2` varchar(2) DEFAULT NULL, + `c3` varchar(4) DEFAULT NULL, + `c4` varchar(2) DEFAULT NULL, + `c5` varchar(8) DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +DROP TABLE t1; +EXECUTE IMMEDIATE +"CREATE TABLE t1 AS SELECT + DATE_FORMAT('2001-01-01',?) AS c0, + DATE_FORMAT('2001-01-01',?) AS c1, + DATE_FORMAT('2001-01-01',?) AS c2, + DATE_FORMAT('2001-01-01',?) AS c3, + DATE_FORMAT('2001-01-01',?) AS c4, + DATE_FORMAT('2001-01-01',?) AS c5" +USING NULL, '10', 10, 10.0, 10e0, TIME'10:20:30'; +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `c0` char(0) DEFAULT NULL, + `c1` varchar(2) DEFAULT NULL, + `c2` varchar(2) DEFAULT NULL, + `c3` varchar(4) DEFAULT NULL, + `c4` varchar(2) DEFAULT NULL, + `c5` varchar(100) DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +DROP TABLE t1; |