diff options
Diffstat (limited to 'mysql-test/r/func_time.result')
| -rw-r--r-- | mysql-test/r/func_time.result | 324 |
1 files changed, 278 insertions, 46 deletions
diff --git a/mysql-test/r/func_time.result b/mysql-test/r/func_time.result index fb647723756..8c6aadb15fb 100644 --- a/mysql-test/r/func_time.result +++ b/mysql-test/r/func_time.result @@ -8,20 +8,39 @@ period_add("9602",-12) period_diff(199505,"9404") 199502 13 select now()-now(),weekday(curdate())-weekday(now()),unix_timestamp()-unix_timestamp(now()); now()-now() weekday(curdate())-weekday(now()) unix_timestamp()-unix_timestamp(now()) -0.000000 0 0 +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.000000 +1994-03-02 10:11:12.000000 1994-03-02 10:11:12 19940302101112.000000 select sec_to_time(9001),sec_to_time(9001)+0,time_to_sec("15:12:22"), sec_to_time(time_to_sec("0:30:47")/6.21); sec_to_time(9001) sec_to_time(9001)+0 time_to_sec("15:12:22") sec_to_time(time_to_sec("0:30:47")/6.21) -02:30:01 23001.000000 54742 00:04:57 +02:30:01 23001 54742.000000 00:04:57.423510 +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 +-838:59:59.000000 +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(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.000000 +0 select strcmp(current_timestamp(),concat(current_date()," ",current_time())); strcmp(current_timestamp(),concat(current_date()," ",current_time())) 0 @@ -52,6 +71,9 @@ DAYOFYEAR("1997-03-03") WEEK("1998-03-03") QUARTER(980303) 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.330000 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 @@ -137,18 +159,18 @@ Saturday 5 select monthname("1972-03-04"),monthname("1972-03-04")+0; monthname("1972-03-04") monthname("1972-03-04")+0 March 0 -select time_format(19980131000000,'%H|%I|%k|%l|%i|%p|%r|%S|%T'); -time_format(19980131000000,'%H|%I|%k|%l|%i|%p|%r|%S|%T') -00|12|0|12|00|AM|12:00:00 AM|00|00:00:00 -select time_format(19980131010203,'%H|%I|%k|%l|%i|%p|%r|%S|%T'); -time_format(19980131010203,'%H|%I|%k|%l|%i|%p|%r|%S|%T') -01|01|1|1|02|AM|01:02:03 AM|03|01:02:03 -select time_format(19980131131415,'%H|%I|%k|%l|%i|%p|%r|%S|%T'); -time_format(19980131131415,'%H|%I|%k|%l|%i|%p|%r|%S|%T') -13|01|13|1|14|PM|01:14:15 PM|15|13:14:15 -select time_format(19980131010015,'%H|%I|%k|%l|%i|%p|%r|%S|%T'); -time_format(19980131010015,'%H|%I|%k|%l|%i|%p|%r|%S|%T') -01|01|1|1|00|AM|01:00:15 AM|15|01:00:15 +select 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 @@ -543,10 +565,10 @@ select @a:=FROM_UNIXTIME(1); 1970-01-01 03:00:01 select unix_timestamp(@a); unix_timestamp(@a) -1 +1.000000 select unix_timestamp('1969-12-01 19:00:01'); unix_timestamp('1969-12-01 19:00:01') -0 +NULL select from_unixtime(-1); from_unixtime(-1) NULL @@ -567,31 +589,31 @@ unix_timestamp(from_unixtime(2147483648)) NULL select unix_timestamp('2039-01-20 01:00:00'); unix_timestamp('2039-01-20 01:00:00') -0 +NULL select unix_timestamp('1968-01-20 01:00:00'); unix_timestamp('1968-01-20 01:00:00') -0 +NULL select unix_timestamp('2038-02-10 01:00:00'); unix_timestamp('2038-02-10 01:00:00') -0 +NULL select unix_timestamp('1969-11-20 01:00:00'); unix_timestamp('1969-11-20 01:00:00') -0 +NULL select unix_timestamp('2038-01-20 01:00:00'); unix_timestamp('2038-01-20 01:00:00') -0 +NULL select unix_timestamp('1969-12-30 01:00:00'); unix_timestamp('1969-12-30 01:00:00') -0 +NULL select unix_timestamp('2038-01-17 12:00:00'); unix_timestamp('2038-01-17 12:00:00') -2147331600 +2147331600.000000 select unix_timestamp('1970-01-01 03:00:01'); unix_timestamp('1970-01-01 03:00:01') -1 +1.000000 select unix_timestamp('2038-01-19 07:14:07'); unix_timestamp('2038-01-19 07:14:07') -0 +NULL SELECT CHARSET(DAYNAME(19700101)); CHARSET(DAYNAME(19700101)) latin1 @@ -788,9 +810,7 @@ TIMESTAMPDIFF(year,'2006-01-10 14:30:28','2008-01-10 14:30:29') 2 select date_add(time,INTERVAL 1 SECOND) from t1; date_add(time,INTERVAL 1 SECOND) -NULL -Warnings: -Warning 1264 Out of range value for column 'time' at row 1 +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, @@ -930,10 +950,10 @@ sec_to_time(1) + 0, from_unixtime(1) + 0; show create table t1; Table Create Table t1 CREATE TABLE `t1` ( - `now() - now()` double(23,6) NOT NULL DEFAULT '0.000000', - `curtime() - curtime()` double(23,6) NOT NULL DEFAULT '0.000000', - `sec_to_time(1) + 0` double(23,6) DEFAULT NULL, - `from_unixtime(1) + 0` double(23,6) DEFAULT NULL + `now() - now()` decimal(20,0) NOT NULL DEFAULT '0', + `curtime() - curtime()` decimal(11,0) NOT NULL DEFAULT '0', + `sec_to_time(1) + 0` decimal(11,0) DEFAULT NULL, + `from_unixtime(1) + 0` decimal(20,0) DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 drop table t1; SELECT SEC_TO_TIME(3300000); @@ -943,7 +963,7 @@ Warnings: Warning 1292 Truncated incorrect time value: '3300000' SELECT SEC_TO_TIME(3300000)+0; SEC_TO_TIME(3300000)+0 -8385959.000000 +8385959 Warnings: Warning 1292 Truncated incorrect time value: '3300000' SELECT SEC_TO_TIME(3600 * 4294967296); @@ -953,31 +973,31 @@ Warnings: Warning 1292 Truncated incorrect time value: '15461882265600' SELECT TIME_TO_SEC('916:40:00'); TIME_TO_SEC('916:40:00') -3020399 +3020399.999999 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 +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 +838:59:59.999999 Warnings: Warning 1292 Truncated incorrect time value: '916:40:00' -Warning 1292 Truncated incorrect time value: '1255:39:59' +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 +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 +-838:59:59.999999 Warnings: Warning 1292 Truncated incorrect time value: '-916:40:00' -Warning 1292 Truncated incorrect time value: '-1255:39:59' +Warning 1292 Truncated incorrect time value: '-1255:39:59.999999' SELECT MAKETIME(916,0,0); MAKETIME(916,0,0) 838:59:59 @@ -1003,6 +1023,7 @@ SELECT MAKETIME(CAST(-1 AS UNSIGNED), 0, 0); MAKETIME(CAST(-1 AS UNSIGNED), 0, 0) 838:59:59 Warnings: +Warning 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 '100000:02:03'); EXTRACT(HOUR FROM '100000:02:03') @@ -1022,6 +1043,8 @@ SELECT SEC_TO_TIME(CAST(-1 AS UNSIGNED)); SEC_TO_TIME(CAST(-1 AS UNSIGNED)) 838:59:59 Warnings: +Warning 1105 Cast to unsigned converted negative integer to it's positive complement +Warning 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; @@ -1070,7 +1093,9 @@ 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 +0 0 45 NULL 45 +Warnings: +Warning 1292 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') @@ -1202,6 +1227,11 @@ str_to_date('10:00 PM', '%h:%i %p') + INTERVAL 10 MINUTE NULL Warnings: Warning 1411 Incorrect datetime value: '10:00 PM' for function str_to_date +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 1411 Incorrect datetime value: '1997-00-04 22:23:00' for function str_to_date create table t1 (field DATE); insert into t1 values ('2006-11-06'); select * from t1 where field < '2006-11-06 04:08:36.0'; @@ -1369,8 +1399,6 @@ NULL # Bug#11766126 59166: ANOTHER DATETIME VALGRIND UNINITIALIZED WARNING # SELECT CAST((MONTH(FROM_UNIXTIME(@@GLOBAL.SQL_MODE))) AS BINARY(1025)); -CAST((MONTH(FROM_UNIXTIME(@@GLOBAL.SQL_MODE))) AS BINARY(1025)) -NULL # # Bug#11766124 59164: VALGRIND: UNINITIALIZED VALUE IN NUMBER_TO_DATETIME # @@ -1439,3 +1467,207 @@ 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 drop table t1; +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 +2000-09-12 00:00:00 +2007-04-25 05:08:49 +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 +Warnings: +Warning 1292 Incorrect datetime value: '0' +Warning 1292 Incorrect datetime value: '0' +select last_day(f2) from t1 where last_day(f2) is null; +last_day(f2) +NULL +NULL +Warnings: +Warning 1292 Incorrect datetime value: '0' +Warning 1292 Incorrect datetime value: '0' +Warning 1292 Incorrect datetime value: '0' +Warning 1292 Incorrect datetime value: '0' +select * from t1 order by last_day (f2); +f2 +0 +0 +Warnings: +Warning 1292 Incorrect datetime value: '0' +Warning 1292 Incorrect datetime value: '0' +drop table t1; +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 +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; +select now() > coalesce(time('21:43:24'), date('2010-05-03')); +now() > coalesce(time('21:43:24'), date('2010-05-03')) +1 +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 +select timestampadd(week, 1, date("0000-00-00")); +timestampadd(week, 1, date("0000-00-00")) +NULL +Warnings: +Warning 1292 Truncated incorrect date 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 +Warnings: +Warning 1292 Incorrect datetime value: '0' +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; +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)) +0000-00-00 +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' +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)) +0000-00-00 00:00:00.000000 +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 |