1--disable_warnings 2drop table if exists t1, test; 3--enable_warnings 4 5 6# 7# time functions 8# 9select extract(DAY_MICROSECOND FROM "1999-01-02 10:11:12.000123"); 10select extract(HOUR_MICROSECOND FROM "1999-01-02 10:11:12.000123"); 11select extract(MINUTE_MICROSECOND FROM "1999-01-02 10:11:12.000123"); 12select extract(SECOND_MICROSECOND FROM "1999-01-02 10:11:12.000123"); 13select extract(MICROSECOND FROM "1999-01-02 10:11:12.000123"); 14select date_format("1997-12-31 23:59:59.000002", "%f"); 15 16select date_add("1997-12-31 23:59:59.000002",INTERVAL "10000 99:99:99.999999" DAY_MICROSECOND); 17select date_add("1997-12-31 23:59:59.000002",INTERVAL "10000:99:99.999999" HOUR_MICROSECOND); 18select date_add("1997-12-31 23:59:59.000002",INTERVAL "10000:99.999999" MINUTE_MICROSECOND); 19select date_add("1997-12-31 23:59:59.000002",INTERVAL "10000.999999" SECOND_MICROSECOND); 20select date_add("1997-12-31 23:59:59.000002",INTERVAL "999999" MICROSECOND); 21 22select date_sub("1998-01-01 00:00:00.000001",INTERVAL "1 1:1:1.000002" DAY_MICROSECOND); 23select date_sub("1998-01-01 00:00:00.000001",INTERVAL "1:1:1.000002" HOUR_MICROSECOND); 24select date_sub("1998-01-01 00:00:00.000001",INTERVAL "1:1.000002" MINUTE_MICROSECOND); 25select date_sub("1998-01-01 00:00:00.000001",INTERVAL "1.000002" SECOND_MICROSECOND); 26select date_sub("1998-01-01 00:00:00.000001",INTERVAL "000002" MICROSECOND); 27 28#Date functions 29select adddate("1997-12-31 23:59:59.000001", 10); 30select subdate("1997-12-31 23:59:59.000001", 10); 31 32select datediff("1997-12-31 23:59:59.000001","1997-12-30"); 33select datediff("1997-11-30 23:59:59.000001","1997-12-31"); 34SET @@SQL_MODE="ALLOW_INVALID_DATES"; 35select datediff("1997-11-31 23:59:59.000001","1997-12-31"); 36SET @@SQL_MODE=""; 37 38# This will give a warning 39select datediff("1997-11-31 23:59:59.000001","1997-12-31"); 40select datediff("1997-11-30 23:59:59.000001",null); 41 42select weekofyear("1997-11-30 23:59:59.000001"); 43 44select makedate(03,1); 45select makedate('0003',1); 46select makedate(1997,1); 47select makedate(1997,0); 48select makedate(9999,365); 49select makedate(9999,366); 50select makedate(100,1); 51 52#Time functions 53 54select addtime("1997-12-31 23:59:59.999999", "1 1:1:1.000002"); 55select subtime("1997-12-31 23:59:59.000001", "1 1:1:1.000002"); 56select addtime("1997-12-31 23:59:59.999999", "1998-01-01 01:01:01.999999"); 57select subtime("1997-12-31 23:59:59.999999", "1998-01-01 01:01:01.999999"); 58select subtime("01:00:00.999999", "02:00:00.999998"); 59select subtime("02:01:01.999999", "01:01:01.999999"); 60 61# PS doesn't support fractional seconds 62--disable_ps_protocol 63select timediff("1997-01-01 23:59:59.000001","1995-12-31 23:59:59.000002"); 64select timediff("1997-12-31 23:59:59.000001","1997-12-30 01:01:01.000002"); 65select timediff("1997-12-30 23:59:59.000001","1997-12-31 23:59:59.000002"); 66select timediff("1997-12-31 23:59:59.000001","23:59:59.000001"); 67select timediff("2000:01:01 00:00:00", "2000:01:01 00:00:00.000001"); 68select timediff("2005-01-11 15:48:49.999999", "2005-01-11 15:48:50"); 69--enable_ps_protocol 70 71select maketime(10,11,12); 72select maketime(25,11,12); 73select maketime(-25,11,12); 74 75# Extraction functions 76 77# PS doesn't support fractional seconds 78--disable_ps_protocol 79select timestamp("2001-12-01", "01:01:01.999999"); 80select timestamp("2001-13-01", "01:01:01.000001"); 81select timestamp("2001-12-01", "25:01:01"); 82select timestamp("2001-12-01 01:01:01.000100"); 83select timestamp("2001-12-01"); 84select day("1997-12-31 23:59:59.000001"); 85select date("1997-12-31 23:59:59.000001"); 86select date("1997-13-31 23:59:59.000001"); 87select time("1997-12-31 23:59:59.000001"); 88select time("1997-12-31 25:59:59.000001"); 89select microsecond("1997-12-31 23:59:59.000001"); 90--enable_ps_protocol 91 92create table t1 93select makedate(1997,1) as f1, 94 addtime(cast("1997-12-31 23:59:59.000001" as datetime(6)), "1 1:1:1.000002") as f2, 95 addtime(cast("23:59:59.999999" as time(6)) , "1 1:1:1.000002") as f3, 96 timediff("1997-12-31 23:59:59.000001","1997-12-30 01:01:01.000002") as f4, 97 timediff("1997-12-30 23:59:59.000001","1997-12-31 23:59:59.000002") as f5, 98 maketime(10,11,12) as f6, 99 timestamp(cast("2001-12-01" as date), "01:01:01") as f7, 100 date("1997-12-31 23:59:59.000001") as f8, 101 time("1997-12-31 23:59:59.000001") as f9; 102describe t1; 103# PS doesn't support fractional seconds 104--disable_ps_protocol 105select * from t1; 106--enable_ps_protocol 107 108create table test(t1 datetime, t2 time, t3 time, t4 datetime); 109insert into test values 110('2001-01-01 01:01:01', '01:01:01', null, '2001-02-01 01:01:01'), 111('2001-01-01 01:01:01', '-01:01:01', '-23:59:59', "1997-12-31 23:59:59.000001"), 112('1997-12-31 23:59:59.000001', '-23:59:59', '-01:01:01', '2001-01-01 01:01:01'), 113('2001-01-01 01:01:01', '01:01:01', '-1 01:01:01', null), 114('2001-01-01 01:01:01', '-01:01:01', '1 01:01:01', '2001-01-01 01:01:01'), 115('2001-01-01 01:01:01', null, '-1 01:01:01', null), 116(null, null, null, null), 117('2001-01-01 01:01:01', '01:01:01', '1 01:01:01', '2001-01-01 01:01:01'); 118 119SELECT ADDTIME(t1,t2) As ttt, ADDTIME(t2, t3) As qqq from test; 120# PS doesn't support fractional seconds 121--disable_ps_protocol 122SELECT TIMEDIFF(t1, t4) As ttt, TIMEDIFF(t2, t3) As qqq, 123 TIMEDIFF(t3, t2) As eee, TIMEDIFF(t2, t4) As rrr from test; 124--enable_ps_protocol 125 126drop table t1, test; 127 128select addtime("-01:01:01.01", "-23:59:59.1") as a; 129select microsecond("1997-12-31 23:59:59.01") as a; 130select microsecond(19971231235959.01) as a; 131select date_add("1997-12-31",INTERVAL "10.09" SECOND_MICROSECOND) as a; 132# PS doesn't support fractional seconds 133--disable_ps_protocol 134select str_to_date("2003-01-02 10:11:12.0012", "%Y-%m-%d %H:%i:%S.%f"); 135--enable_ps_protocol 136 137# End of 4.1 tests 138 139 140 141# 142# Bug#37553: MySql Error Compare TimeDiff & Time 143# 144 145# calculations involving negative time values ignored sign 146select timediff('2008-09-29 20:10:10','2008-09-30 20:10:10'),time('00:00:00'); 147select timediff('2008-09-29 20:10:10','2008-09-30 20:10:10')>time('00:00:00'); 148select timediff('2008-09-29 20:10:10','2008-09-30 20:10:10')<time('00:00:00'); 149 150# show that conversion to DECIMAL no longer drops sign 151SELECT CAST(time('-73:42:12') AS DECIMAL); 152 153 154 155# 156# Bug#42525 - TIMEDIFF function 157# 158 159SELECT TIMEDIFF(TIME('17:00:00'),TIME('17:00:00'))=TIME('00:00:00') AS 1Eq, 160 TIMEDIFF(TIME('17:59:00'),TIME('17:00:00'))=TIME('00:00:00') AS 1NEq1, 161 TIMEDIFF(TIME('18:00:00'),TIME('17:00:00'))=TIME('00:00:00') AS 1NEq2, 162 TIMEDIFF(TIME('17:00:00'),TIME('17:00:00'))= '00:00:00' AS 2Eq, 163 TIMEDIFF(TIME('17:59:00'),TIME('17:00:00'))= '00:00:00' AS 2NEq1, 164 TIMEDIFF(TIME('18:00:00'),TIME('17:00:00'))= '00:00:00' AS 2NEq2, 165 TIMEDIFF(TIME('17:00:00'),TIME('17:00:00'))=TIME(0) AS 3Eq, 166 TIMEDIFF(TIME('17:59:00'),TIME('17:00:00'))=TIME(0) AS 3NEq1, 167 TIMEDIFF(TIME('18:00:00'),TIME('17:00:00'))=TIME(0) AS 3NEq2, 168 TIME(0) AS Time0, TIME('00:00:00') AS Time00, '00:00:00' AS Literal0000, 169 TIMEDIFF(TIME('17:59:00'),TIME('17:00:00')), 170 TIMEDIFF(TIME('17:00:00'),TIME('17:59:00')); 171 172# 173# Bug#42661 - sec_to_time() and signedness 174# 175 176SELECT sec_to_time(3020399)=TIME('838:59:59'); 177SELECT sec_to_time(-3020399)=TIME('-838:59:59'); 178SELECT sec_to_time(-3020399)='-838:59:59'; 179SELECT time(sec_to_time(-3020399))=TIME('-838:59:59'); 180SELECT time(sec_to_time(-3020399))=TIME('-838:59:58'); 181 182# 183# Bug#42662 - maketime() and signedness 184# 185 186# TIME(...) and CAST(... AS TIME) go through the same code-path here, 187# but we'll explicitly show show that both work in case the ever changes. 188SELECT maketime(-1,0,1)='-01:00:01'; 189SELECT TIME(maketime(-1,0,1))=CAST('-01:00:01' AS TIME); 190SELECT maketime(-1,0,1)=CAST('-01:00:01' AS TIME); 191SELECT maketime(1,0,1)=CAST('01:00:01' AS TIME); 192SELECT maketime(1,0,1)=CAST('01:00:02' AS TIME); 193 194# End of 5.0 tests 195