1drop table if exists t1, test; 2select extract(DAY_MICROSECOND FROM "1999-01-02 10:11:12.000123"); 3extract(DAY_MICROSECOND FROM "1999-01-02 10:11:12.000123") 42101112000123 5select extract(HOUR_MICROSECOND FROM "1999-01-02 10:11:12.000123"); 6extract(HOUR_MICROSECOND FROM "1999-01-02 10:11:12.000123") 7101112000123 8select extract(MINUTE_MICROSECOND FROM "1999-01-02 10:11:12.000123"); 9extract(MINUTE_MICROSECOND FROM "1999-01-02 10:11:12.000123") 101112000123 11select extract(SECOND_MICROSECOND FROM "1999-01-02 10:11:12.000123"); 12extract(SECOND_MICROSECOND FROM "1999-01-02 10:11:12.000123") 1312000123 14select extract(MICROSECOND FROM "1999-01-02 10:11:12.000123"); 15extract(MICROSECOND FROM "1999-01-02 10:11:12.000123") 16123 17select date_format("1997-12-31 23:59:59.000002", "%f"); 18date_format("1997-12-31 23:59:59.000002", "%f") 19000002 20select date_add("1997-12-31 23:59:59.000002",INTERVAL "10000 99:99:99.999999" DAY_MICROSECOND); 21date_add("1997-12-31 23:59:59.000002",INTERVAL "10000 99:99:99.999999" DAY_MICROSECOND) 222025-05-23 04:40:39.000001 23select date_add("1997-12-31 23:59:59.000002",INTERVAL "10000:99:99.999999" HOUR_MICROSECOND); 24date_add("1997-12-31 23:59:59.000002",INTERVAL "10000:99:99.999999" HOUR_MICROSECOND) 251999-02-21 17:40:39.000001 26select date_add("1997-12-31 23:59:59.000002",INTERVAL "10000:99.999999" MINUTE_MICROSECOND); 27date_add("1997-12-31 23:59:59.000002",INTERVAL "10000:99.999999" MINUTE_MICROSECOND) 281998-01-07 22:41:39.000001 29select date_add("1997-12-31 23:59:59.000002",INTERVAL "10000.999999" SECOND_MICROSECOND); 30date_add("1997-12-31 23:59:59.000002",INTERVAL "10000.999999" SECOND_MICROSECOND) 311998-01-01 02:46:40.000001 32select date_add("1997-12-31 23:59:59.000002",INTERVAL "999999" MICROSECOND); 33date_add("1997-12-31 23:59:59.000002",INTERVAL "999999" MICROSECOND) 341998-01-01 00:00:00.000001 35select date_sub("1998-01-01 00:00:00.000001",INTERVAL "1 1:1:1.000002" DAY_MICROSECOND); 36date_sub("1998-01-01 00:00:00.000001",INTERVAL "1 1:1:1.000002" DAY_MICROSECOND) 371997-12-30 22:58:58.999999 38select date_sub("1998-01-01 00:00:00.000001",INTERVAL "1:1:1.000002" HOUR_MICROSECOND); 39date_sub("1998-01-01 00:00:00.000001",INTERVAL "1:1:1.000002" HOUR_MICROSECOND) 401997-12-31 22:58:58.999999 41select date_sub("1998-01-01 00:00:00.000001",INTERVAL "1:1.000002" MINUTE_MICROSECOND); 42date_sub("1998-01-01 00:00:00.000001",INTERVAL "1:1.000002" MINUTE_MICROSECOND) 431997-12-31 23:58:58.999999 44select date_sub("1998-01-01 00:00:00.000001",INTERVAL "1.000002" SECOND_MICROSECOND); 45date_sub("1998-01-01 00:00:00.000001",INTERVAL "1.000002" SECOND_MICROSECOND) 461997-12-31 23:59:58.999999 47select date_sub("1998-01-01 00:00:00.000001",INTERVAL "000002" MICROSECOND); 48date_sub("1998-01-01 00:00:00.000001",INTERVAL "000002" MICROSECOND) 491997-12-31 23:59:59.999999 50select adddate("1997-12-31 23:59:59.000001", 10); 51adddate("1997-12-31 23:59:59.000001", 10) 521998-01-10 23:59:59.000001 53select subdate("1997-12-31 23:59:59.000001", 10); 54subdate("1997-12-31 23:59:59.000001", 10) 551997-12-21 23:59:59.000001 56select datediff("1997-12-31 23:59:59.000001","1997-12-30"); 57datediff("1997-12-31 23:59:59.000001","1997-12-30") 581 59select datediff("1997-11-30 23:59:59.000001","1997-12-31"); 60datediff("1997-11-30 23:59:59.000001","1997-12-31") 61-31 62SET @@SQL_MODE="ALLOW_INVALID_DATES"; 63select datediff("1997-11-31 23:59:59.000001","1997-12-31"); 64datediff("1997-11-31 23:59:59.000001","1997-12-31") 65-30 66SET @@SQL_MODE=""; 67select datediff("1997-11-31 23:59:59.000001","1997-12-31"); 68datediff("1997-11-31 23:59:59.000001","1997-12-31") 69NULL 70Warnings: 71Warning 1292 Incorrect datetime value: '1997-11-31 23:59:59.000001' 72select datediff("1997-11-30 23:59:59.000001",null); 73datediff("1997-11-30 23:59:59.000001",null) 74NULL 75select weekofyear("1997-11-30 23:59:59.000001"); 76weekofyear("1997-11-30 23:59:59.000001") 7748 78select makedate(03,1); 79makedate(03,1) 802003-01-01 81select makedate('0003',1); 82makedate('0003',1) 832003-01-01 84select makedate(1997,1); 85makedate(1997,1) 861997-01-01 87select makedate(1997,0); 88makedate(1997,0) 89NULL 90select makedate(9999,365); 91makedate(9999,365) 929999-12-31 93select makedate(9999,366); 94makedate(9999,366) 95NULL 96select makedate(100,1); 97makedate(100,1) 980100-01-01 99select addtime("1997-12-31 23:59:59.999999", "1 1:1:1.000002"); 100addtime("1997-12-31 23:59:59.999999", "1 1:1:1.000002") 1011998-01-02 01:01:01.000001 102select subtime("1997-12-31 23:59:59.000001", "1 1:1:1.000002"); 103subtime("1997-12-31 23:59:59.000001", "1 1:1:1.000002") 1041997-12-30 22:58:57.999999 105select addtime("1997-12-31 23:59:59.999999", "1998-01-01 01:01:01.999999"); 106addtime("1997-12-31 23:59:59.999999", "1998-01-01 01:01:01.999999") 107NULL 108select subtime("1997-12-31 23:59:59.999999", "1998-01-01 01:01:01.999999"); 109subtime("1997-12-31 23:59:59.999999", "1998-01-01 01:01:01.999999") 110NULL 111select subtime("01:00:00.999999", "02:00:00.999998"); 112subtime("01:00:00.999999", "02:00:00.999998") 113-00:59:59.999999 114select subtime("02:01:01.999999", "01:01:01.999999"); 115subtime("02:01:01.999999", "01:01:01.999999") 11601:00:00 117select timediff("1997-01-01 23:59:59.000001","1995-12-31 23:59:59.000002"); 118timediff("1997-01-01 23:59:59.000001","1995-12-31 23:59:59.000002") 119838:59:59.000000 120Warnings: 121Warning 1292 Truncated incorrect time value: '8807:59:59.999999' 122select timediff("1997-12-31 23:59:59.000001","1997-12-30 01:01:01.000002"); 123timediff("1997-12-31 23:59:59.000001","1997-12-30 01:01:01.000002") 12446:58:57.999999 125select timediff("1997-12-30 23:59:59.000001","1997-12-31 23:59:59.000002"); 126timediff("1997-12-30 23:59:59.000001","1997-12-31 23:59:59.000002") 127-24:00:00.000001 128select timediff("1997-12-31 23:59:59.000001","23:59:59.000001"); 129timediff("1997-12-31 23:59:59.000001","23:59:59.000001") 130NULL 131select timediff("2000:01:01 00:00:00", "2000:01:01 00:00:00.000001"); 132timediff("2000:01:01 00:00:00", "2000:01:01 00:00:00.000001") 133-00:00:00.000001 134select timediff("2005-01-11 15:48:49.999999", "2005-01-11 15:48:50"); 135timediff("2005-01-11 15:48:49.999999", "2005-01-11 15:48:50") 136-00:00:00.000001 137select maketime(10,11,12); 138maketime(10,11,12) 13910:11:12 140select maketime(25,11,12); 141maketime(25,11,12) 14225:11:12 143select maketime(-25,11,12); 144maketime(-25,11,12) 145-25:11:12 146select timestamp("2001-12-01", "01:01:01.999999"); 147timestamp("2001-12-01", "01:01:01.999999") 1482001-12-01 01:01:01.999999 149select timestamp("2001-13-01", "01:01:01.000001"); 150timestamp("2001-13-01", "01:01:01.000001") 151NULL 152Warnings: 153Warning 1292 Incorrect datetime value: '2001-13-01' 154select timestamp("2001-12-01", "25:01:01"); 155timestamp("2001-12-01", "25:01:01") 1562001-12-02 01:01:01 157select timestamp("2001-12-01 01:01:01.000100"); 158timestamp("2001-12-01 01:01:01.000100") 1592001-12-01 01:01:01.000100 160select timestamp("2001-12-01"); 161timestamp("2001-12-01") 1622001-12-01 00:00:00 163select day("1997-12-31 23:59:59.000001"); 164day("1997-12-31 23:59:59.000001") 16531 166select date("1997-12-31 23:59:59.000001"); 167date("1997-12-31 23:59:59.000001") 1681997-12-31 169select date("1997-13-31 23:59:59.000001"); 170date("1997-13-31 23:59:59.000001") 171NULL 172Warnings: 173Warning 1292 Incorrect datetime value: '1997-13-31 23:59:59.000001' 174select time("1997-12-31 23:59:59.000001"); 175time("1997-12-31 23:59:59.000001") 17623:59:59.000001 177select time("1997-12-31 25:59:59.000001"); 178time("1997-12-31 25:59:59.000001") 179NULL 180Warnings: 181Warning 1292 Truncated incorrect time value: '1997-12-31 25:59:59.000001' 182select microsecond("1997-12-31 23:59:59.000001"); 183microsecond("1997-12-31 23:59:59.000001") 1841 185create table t1 186select makedate(1997,1) as f1, 187addtime(cast("1997-12-31 23:59:59.000001" as datetime), "1 1:1:1.000002") as f2, 188addtime(cast("23:59:59.999999" as time) , "1 1:1:1.000002") as f3, 189timediff("1997-12-31 23:59:59.000001","1997-12-30 01:01:01.000002") as f4, 190timediff("1997-12-30 23:59:59.000001","1997-12-31 23:59:59.000002") as f5, 191maketime(10,11,12) as f6, 192timestamp(cast("2001-12-01" as date), "01:01:01") as f7, 193date("1997-12-31 23:59:59.000001") as f8, 194time("1997-12-31 23:59:59.000001") as f9; 195describe t1; 196Field Type Null Key Default Extra 197f1 date YES NULL 198f2 datetime(6) YES NULL 199f3 time(6) YES NULL 200f4 time(6) YES NULL 201f5 time(6) YES NULL 202f6 time YES NULL 203f7 datetime YES NULL 204f8 date YES NULL 205f9 time(6) YES NULL 206select * from t1; 207f1 f2 f3 f4 f5 f6 f7 f8 f9 2081997-01-01 1998-01-02 01:01:00.000002 49:01:01.000002 46:58:57.999999 -24:00:00.000001 10:11:12 2001-12-01 01:01:01 1997-12-31 23:59:59.000001 209create table test(t1 datetime, t2 time, t3 time, t4 datetime); 210insert into test values 211('2001-01-01 01:01:01', '01:01:01', null, '2001-02-01 01:01:01'), 212('2001-01-01 01:01:01', '-01:01:01', '-23:59:59', "1997-12-31 23:59:59.000001"), 213('1997-12-31 23:59:59.000001', '-23:59:59', '-01:01:01', '2001-01-01 01:01:01'), 214('2001-01-01 01:01:01', '01:01:01', '-1 01:01:01', null), 215('2001-01-01 01:01:01', '-01:01:01', '1 01:01:01', '2001-01-01 01:01:01'), 216('2001-01-01 01:01:01', null, '-1 01:01:01', null), 217(null, null, null, null), 218('2001-01-01 01:01:01', '01:01:01', '1 01:01:01', '2001-01-01 01:01:01'); 219SELECT ADDTIME(t1,t2) As ttt, ADDTIME(t2, t3) As qqq from test; 220ttt qqq 2212001-01-01 02:02:02 NULL 2222001-01-01 00:00:00 -25:01:00 2231997-12-31 00:00:00 -25:01:00 2242001-01-01 02:02:02 -24:00:00 2252001-01-01 00:00:00 24:00:00 226NULL NULL 227NULL NULL 2282001-01-01 02:02:02 26:02:02 229SELECT TIMEDIFF(t1, t4) As ttt, TIMEDIFF(t2, t3) As qqq, 230TIMEDIFF(t3, t2) As eee, TIMEDIFF(t2, t4) As rrr from test; 231ttt qqq eee rrr 232-744:00:00 NULL NULL NULL 233838:59:59 22:58:58 -22:58:58 NULL 234-838:59:59 -22:58:58 22:58:58 NULL 235NULL 26:02:02 -26:02:02 NULL 23600:00:00 -26:02:02 26:02:02 NULL 237NULL NULL NULL NULL 238NULL NULL NULL NULL 23900:00:00 -24:00:00 24:00:00 NULL 240Warnings: 241Warning 1292 Truncated incorrect time value: '26305:01:02' 242Warning 1292 Truncated incorrect time value: '-26305:01:02' 243drop table t1, test; 244select addtime("-01:01:01.01", "-23:59:59.1") as a; 245a 246-25:01:00.110000 247select microsecond("1997-12-31 23:59:59.01") as a; 248a 24910000 250select microsecond(19971231235959.01) as a; 251a 25210000 253select date_add("1997-12-31",INTERVAL "10.09" SECOND_MICROSECOND) as a; 254a 2551997-12-31 00:00:10.090000 256select str_to_date("2003-01-02 10:11:12.0012", "%Y-%m-%d %H:%i:%S.%f"); 257str_to_date("2003-01-02 10:11:12.0012", "%Y-%m-%d %H:%i:%S.%f") 2582003-01-02 10:11:12.001200 259select timediff('2008-09-29 20:10:10','2008-09-30 20:10:10'),time('00:00:00'); 260timediff('2008-09-29 20:10:10','2008-09-30 20:10:10') time('00:00:00') 261-24:00:00 00:00:00 262select timediff('2008-09-29 20:10:10','2008-09-30 20:10:10')>time('00:00:00'); 263timediff('2008-09-29 20:10:10','2008-09-30 20:10:10')>time('00:00:00') 2640 265select timediff('2008-09-29 20:10:10','2008-09-30 20:10:10')<time('00:00:00'); 266timediff('2008-09-29 20:10:10','2008-09-30 20:10:10')<time('00:00:00') 2671 268SELECT CAST(time('-73:42:12') AS DECIMAL); 269CAST(time('-73:42:12') AS DECIMAL) 270-734212 271SELECT TIMEDIFF(TIME('17:00:00'),TIME('17:00:00'))=TIME('00:00:00') AS 1Eq, 272TIMEDIFF(TIME('17:59:00'),TIME('17:00:00'))=TIME('00:00:00') AS 1NEq1, 273TIMEDIFF(TIME('18:00:00'),TIME('17:00:00'))=TIME('00:00:00') AS 1NEq2, 274TIMEDIFF(TIME('17:00:00'),TIME('17:00:00'))= '00:00:00' AS 2Eq, 275TIMEDIFF(TIME('17:59:00'),TIME('17:00:00'))= '00:00:00' AS 2NEq1, 276TIMEDIFF(TIME('18:00:00'),TIME('17:00:00'))= '00:00:00' AS 2NEq2, 277TIMEDIFF(TIME('17:00:00'),TIME('17:00:00'))=TIME(0) AS 3Eq, 278TIMEDIFF(TIME('17:59:00'),TIME('17:00:00'))=TIME(0) AS 3NEq1, 279TIMEDIFF(TIME('18:00:00'),TIME('17:00:00'))=TIME(0) AS 3NEq2, 280TIME(0) AS Time0, TIME('00:00:00') AS Time00, '00:00:00' AS Literal0000, 281TIMEDIFF(TIME('17:59:00'),TIME('17:00:00')), 282TIMEDIFF(TIME('17:00:00'),TIME('17:59:00')); 2831Eq 1NEq1 1NEq2 2Eq 2NEq1 2NEq2 3Eq 3NEq1 3NEq2 Time0 Time00 Literal0000 TIMEDIFF(TIME('17:59:00'),TIME('17:00:00')) TIMEDIFF(TIME('17:00:00'),TIME('17:59:00')) 2841 0 0 1 0 0 1 0 0 00:00:00 00:00:00 00:00:00 00:59:00 -00:59:00 285SELECT sec_to_time(3020399)=TIME('838:59:59'); 286sec_to_time(3020399)=TIME('838:59:59') 2871 288SELECT sec_to_time(-3020399)=TIME('-838:59:59'); 289sec_to_time(-3020399)=TIME('-838:59:59') 2901 291SELECT sec_to_time(-3020399)='-838:59:59'; 292sec_to_time(-3020399)='-838:59:59' 2931 294SELECT time(sec_to_time(-3020399))=TIME('-838:59:59'); 295time(sec_to_time(-3020399))=TIME('-838:59:59') 2961 297SELECT time(sec_to_time(-3020399))=TIME('-838:59:58'); 298time(sec_to_time(-3020399))=TIME('-838:59:58') 2990 300SELECT maketime(-1,0,1)='-01:00:01'; 301maketime(-1,0,1)='-01:00:01' 3021 303SELECT TIME(maketime(-1,0,1))=CAST('-01:00:01' AS TIME); 304TIME(maketime(-1,0,1))=CAST('-01:00:01' AS TIME) 3051 306SELECT maketime(-1,0,1)=CAST('-01:00:01' AS TIME); 307maketime(-1,0,1)=CAST('-01:00:01' AS TIME) 3081 309SELECT maketime(1,0,1)=CAST('01:00:01' AS TIME); 310maketime(1,0,1)=CAST('01:00:01' AS TIME) 3111 312SELECT maketime(1,0,1)=CAST('01:00:02' AS TIME); 313maketime(1,0,1)=CAST('01:00:02' AS TIME) 3140 315