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"; 63Warnings: 64Warning 3090 Changing sql mode 'NO_AUTO_CREATE_USER' is deprecated. It will be removed in a future release. 65select datediff("1997-11-31 23:59:59.000001","1997-12-31"); 66datediff("1997-11-31 23:59:59.000001","1997-12-31") 67-30 68SET @@SQL_MODE=""; 69select datediff("1997-11-31 23:59:59.000001","1997-12-31"); 70datediff("1997-11-31 23:59:59.000001","1997-12-31") 71NULL 72Warnings: 73Warning 1292 Incorrect datetime value: '1997-11-31 23:59:59.000001' 74select datediff("1997-11-30 23:59:59.000001",null); 75datediff("1997-11-30 23:59:59.000001",null) 76NULL 77select weekofyear("1997-11-30 23:59:59.000001"); 78weekofyear("1997-11-30 23:59:59.000001") 7948 80select makedate(03,1); 81makedate(03,1) 822003-01-01 83select makedate('0003',1); 84makedate('0003',1) 852003-01-01 86select makedate(1997,1); 87makedate(1997,1) 881997-01-01 89select makedate(1997,0); 90makedate(1997,0) 91NULL 92select makedate(9999,365); 93makedate(9999,365) 949999-12-31 95select makedate(9999,366); 96makedate(9999,366) 97NULL 98select makedate(100,1); 99makedate(100,1) 1000100-01-01 101select addtime("1997-12-31 23:59:59.999999", "1 1:1:1.000002"); 102addtime("1997-12-31 23:59:59.999999", "1 1:1:1.000002") 1031998-01-02 01:01:01.000001 104select subtime("1997-12-31 23:59:59.000001", "1 1:1:1.000002"); 105subtime("1997-12-31 23:59:59.000001", "1 1:1:1.000002") 1061997-12-30 22:58:57.999999 107select addtime("1997-12-31 23:59:59.999999", "1998-01-01 01:01:01.999999"); 108addtime("1997-12-31 23:59:59.999999", "1998-01-01 01:01:01.999999") 109NULL 110select subtime("1997-12-31 23:59:59.999999", "1998-01-01 01:01:01.999999"); 111subtime("1997-12-31 23:59:59.999999", "1998-01-01 01:01:01.999999") 112NULL 113select subtime("01:00:00.999999", "02:00:00.999998"); 114subtime("01:00:00.999999", "02:00:00.999998") 115-00:59:59.999999 116select subtime("02:01:01.999999", "01:01:01.999999"); 117subtime("02:01:01.999999", "01:01:01.999999") 11801:00:00 119select timediff("1997-01-01 23:59:59.000001","1995-12-31 23:59:59.000002"); 120timediff("1997-01-01 23:59:59.000001","1995-12-31 23:59:59.000002") 121838:59:59.000000 122Warnings: 123Warning 1292 Truncated incorrect time value: '8807:59:59.999999' 124select timediff("1997-12-31 23:59:59.000001","1997-12-30 01:01:01.000002"); 125timediff("1997-12-31 23:59:59.000001","1997-12-30 01:01:01.000002") 12646:58:57.999999 127select timediff("1997-12-30 23:59:59.000001","1997-12-31 23:59:59.000002"); 128timediff("1997-12-30 23:59:59.000001","1997-12-31 23:59:59.000002") 129-24:00:00.000001 130select timediff("1997-12-31 23:59:59.000001","23:59:59.000001"); 131timediff("1997-12-31 23:59:59.000001","23:59:59.000001") 132NULL 133select timediff("2000:01:01 00:00:00", "2000:01:01 00:00:00.000001"); 134timediff("2000:01:01 00:00:00", "2000:01:01 00:00:00.000001") 135-00:00:00.000001 136select timediff("2005-01-11 15:48:49.999999", "2005-01-11 15:48:50"); 137timediff("2005-01-11 15:48:49.999999", "2005-01-11 15:48:50") 138-00:00:00.000001 139select maketime(10,11,12); 140maketime(10,11,12) 14110:11:12 142select maketime(25,11,12); 143maketime(25,11,12) 14425:11:12 145select maketime(-25,11,12); 146maketime(-25,11,12) 147-25:11:12 148select timestamp("2001-12-01", "01:01:01.999999"); 149timestamp("2001-12-01", "01:01:01.999999") 1502001-12-01 01:01:01.999999 151select timestamp("2001-13-01", "01:01:01.000001"); 152timestamp("2001-13-01", "01:01:01.000001") 153NULL 154Warnings: 155Warning 1292 Incorrect datetime value: '2001-13-01' 156select timestamp("2001-12-01", "25:01:01"); 157timestamp("2001-12-01", "25:01:01") 1582001-12-02 01:01:01 159select timestamp("2001-12-01 01:01:01.000100"); 160timestamp("2001-12-01 01:01:01.000100") 1612001-12-01 01:01:01.000100 162select timestamp("2001-12-01"); 163timestamp("2001-12-01") 1642001-12-01 00:00:00 165select day("1997-12-31 23:59:59.000001"); 166day("1997-12-31 23:59:59.000001") 16731 168select date("1997-12-31 23:59:59.000001"); 169date("1997-12-31 23:59:59.000001") 1701997-12-31 171select date("1997-13-31 23:59:59.000001"); 172date("1997-13-31 23:59:59.000001") 173NULL 174Warnings: 175Warning 1292 Incorrect datetime value: '1997-13-31 23:59:59.000001' 176select time("1997-12-31 23:59:59.000001"); 177time("1997-12-31 23:59:59.000001") 17823:59:59.000001 179select time("1997-12-31 25:59:59.000001"); 180time("1997-12-31 25:59:59.000001") 181NULL 182Warnings: 183Warning 1292 Truncated incorrect time value: '1997-12-31 25:59:59.000001' 184select microsecond("1997-12-31 23:59:59.000001"); 185microsecond("1997-12-31 23:59:59.000001") 1861 187create table t1 188select makedate(1997,1) as f1, 189addtime(cast("1997-12-31 23:59:59.000001" as datetime), "1 1:1:1.000002") as f2, 190addtime(cast("23:59:59.999999" as time) , "1 1:1:1.000002") as f3, 191timediff("1997-12-31 23:59:59.000001","1997-12-30 01:01:01.000002") as f4, 192timediff("1997-12-30 23:59:59.000001","1997-12-31 23:59:59.000002") as f5, 193maketime(10,11,12) as f6, 194timestamp(cast("2001-12-01" as date), "01:01:01") as f7, 195date("1997-12-31 23:59:59.000001") as f8, 196time("1997-12-31 23:59:59.000001") as f9; 197describe t1; 198Field Type Null Key Default Extra 199f1 date YES NULL 200f2 datetime(6) YES NULL 201f3 time(6) YES NULL 202f4 time(6) YES NULL 203f5 time(6) YES NULL 204f6 time YES NULL 205f7 datetime YES NULL 206f8 date YES NULL 207f9 time(6) YES NULL 208select * from t1; 209f1 f2 f3 f4 f5 f6 f7 f8 f9 2101997-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 211create table test(t1 datetime, t2 time, t3 time, t4 datetime); 212insert into test values 213('2001-01-01 01:01:01', '01:01:01', null, '2001-02-01 01:01:01'), 214('2001-01-01 01:01:01', '-01:01:01', '-23:59:59', "1997-12-31 23:59:59.000001"), 215('1997-12-31 23:59:59.000001', '-23:59:59', '-01:01:01', '2001-01-01 01:01:01'), 216('2001-01-01 01:01:01', '01:01:01', '-1 01:01:01', null), 217('2001-01-01 01:01:01', '-01:01:01', '1 01:01:01', '2001-01-01 01:01:01'), 218('2001-01-01 01:01:01', null, '-1 01:01:01', null), 219(null, null, null, null), 220('2001-01-01 01:01:01', '01:01:01', '1 01:01:01', '2001-01-01 01:01:01'); 221SELECT ADDTIME(t1,t2) As ttt, ADDTIME(t2, t3) As qqq from test; 222ttt qqq 2232001-01-01 02:02:02 NULL 2242001-01-01 00:00:00 -25:01:00 2251997-12-31 00:00:00 -25:01:00 2262001-01-01 02:02:02 -24:00:00 2272001-01-01 00:00:00 24:00:00 228NULL NULL 229NULL NULL 2302001-01-01 02:02:02 26:02:02 231SELECT TIMEDIFF(t1, t4) As ttt, TIMEDIFF(t2, t3) As qqq, 232TIMEDIFF(t3, t2) As eee, TIMEDIFF(t2, t4) As rrr from test; 233ttt qqq eee rrr 234-744:00:00 NULL NULL NULL 235838:59:59 22:58:58 -22:58:58 NULL 236-838:59:59 -22:58:58 22:58:58 NULL 237NULL 26:02:02 -26:02:02 NULL 23800:00:00 -26:02:02 26:02:02 NULL 239NULL NULL NULL NULL 240NULL NULL NULL NULL 24100:00:00 -24:00:00 24:00:00 NULL 242Warnings: 243Warning 1292 Truncated incorrect time value: '26305:01:02' 244Warning 1292 Truncated incorrect time value: '-26305:01:02' 245drop table t1, test; 246select addtime("-01:01:01.01", "-23:59:59.1") as a; 247a 248-25:01:00.110000 249select microsecond("1997-12-31 23:59:59.01") as a; 250a 25110000 252select microsecond(19971231235959.01) as a; 253a 25410000 255select date_add("1997-12-31",INTERVAL "10.09" SECOND_MICROSECOND) as a; 256a 2571997-12-31 00:00:10.090000 258select str_to_date("2003-01-02 10:11:12.0012", "%Y-%m-%d %H:%i:%S.%f"); 259str_to_date("2003-01-02 10:11:12.0012", "%Y-%m-%d %H:%i:%S.%f") 2602003-01-02 10:11:12.001200 261select timediff('2008-09-29 20:10:10','2008-09-30 20:10:10'),time('00:00:00'); 262timediff('2008-09-29 20:10:10','2008-09-30 20:10:10') time('00:00:00') 263-24:00:00 00:00:00 264select timediff('2008-09-29 20:10:10','2008-09-30 20:10:10')>time('00:00:00'); 265timediff('2008-09-29 20:10:10','2008-09-30 20:10:10')>time('00:00:00') 2660 267select timediff('2008-09-29 20:10:10','2008-09-30 20:10:10')<time('00:00:00'); 268timediff('2008-09-29 20:10:10','2008-09-30 20:10:10')<time('00:00:00') 2691 270SELECT CAST(time('-73:42:12') AS DECIMAL); 271CAST(time('-73:42:12') AS DECIMAL) 272-734212 273SELECT TIMEDIFF(TIME('17:00:00'),TIME('17:00:00'))=TIME('00:00:00') AS 1Eq, 274TIMEDIFF(TIME('17:59:00'),TIME('17:00:00'))=TIME('00:00:00') AS 1NEq1, 275TIMEDIFF(TIME('18:00:00'),TIME('17:00:00'))=TIME('00:00:00') AS 1NEq2, 276TIMEDIFF(TIME('17:00:00'),TIME('17:00:00'))= '00:00:00' AS 2Eq, 277TIMEDIFF(TIME('17:59:00'),TIME('17:00:00'))= '00:00:00' AS 2NEq1, 278TIMEDIFF(TIME('18:00:00'),TIME('17:00:00'))= '00:00:00' AS 2NEq2, 279TIMEDIFF(TIME('17:00:00'),TIME('17:00:00'))=TIME(0) AS 3Eq, 280TIMEDIFF(TIME('17:59:00'),TIME('17:00:00'))=TIME(0) AS 3NEq1, 281TIMEDIFF(TIME('18:00:00'),TIME('17:00:00'))=TIME(0) AS 3NEq2, 282TIME(0) AS Time0, TIME('00:00:00') AS Time00, '00:00:00' AS Literal0000, 283TIMEDIFF(TIME('17:59:00'),TIME('17:00:00')), 284TIMEDIFF(TIME('17:00:00'),TIME('17:59:00')); 2851Eq 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')) 2861 0 0 1 0 0 1 0 0 00:00:00 00:00:00 00:00:00 00:59:00 -00:59:00 287SELECT sec_to_time(3020399)=TIME('838:59:59'); 288sec_to_time(3020399)=TIME('838:59:59') 2891 290SELECT sec_to_time(-3020399)=TIME('-838:59:59'); 291sec_to_time(-3020399)=TIME('-838:59:59') 2921 293SELECT sec_to_time(-3020399)='-838:59:59'; 294sec_to_time(-3020399)='-838:59:59' 2951 296SELECT time(sec_to_time(-3020399))=TIME('-838:59:59'); 297time(sec_to_time(-3020399))=TIME('-838:59:59') 2981 299SELECT time(sec_to_time(-3020399))=TIME('-838:59:58'); 300time(sec_to_time(-3020399))=TIME('-838:59:58') 3010 302SELECT maketime(-1,0,1)='-01:00:01'; 303maketime(-1,0,1)='-01:00:01' 3041 305SELECT TIME(maketime(-1,0,1))=CAST('-01:00:01' AS TIME); 306TIME(maketime(-1,0,1))=CAST('-01:00:01' AS TIME) 3071 308SELECT maketime(-1,0,1)=CAST('-01:00:01' AS TIME); 309maketime(-1,0,1)=CAST('-01:00:01' AS TIME) 3101 311SELECT maketime(1,0,1)=CAST('01:00:01' AS TIME); 312maketime(1,0,1)=CAST('01:00:01' AS TIME) 3131 314SELECT maketime(1,0,1)=CAST('01:00:02' AS TIME); 315maketime(1,0,1)=CAST('01:00:02' AS TIME) 3160 317