drop table if exists t1, test; select extract(DAY_MICROSECOND FROM "1999-01-02 10:11:12.000123"); extract(DAY_MICROSECOND FROM "1999-01-02 10:11:12.000123") 2101112000123 select extract(HOUR_MICROSECOND FROM "1999-01-02 10:11:12.000123"); extract(HOUR_MICROSECOND FROM "1999-01-02 10:11:12.000123") 101112000123 select extract(MINUTE_MICROSECOND FROM "1999-01-02 10:11:12.000123"); extract(MINUTE_MICROSECOND FROM "1999-01-02 10:11:12.000123") 1112000123 select extract(SECOND_MICROSECOND FROM "1999-01-02 10:11:12.000123"); extract(SECOND_MICROSECOND FROM "1999-01-02 10:11:12.000123") 12000123 select extract(MICROSECOND FROM "1999-01-02 10:11:12.000123"); extract(MICROSECOND FROM "1999-01-02 10:11:12.000123") 123 select date_format("1997-12-31 23:59:59.000002", "%f"); date_format("1997-12-31 23:59:59.000002", "%f") 000002 select date_add("1997-12-31 23:59:59.000002",INTERVAL "10000 99:99:99.999999" DAY_MICROSECOND); date_add("1997-12-31 23:59:59.000002",INTERVAL "10000 99:99:99.999999" DAY_MICROSECOND) 2025-05-23 04:40:39.000001 select date_add("1997-12-31 23:59:59.000002",INTERVAL "10000:99:99.999999" HOUR_MICROSECOND); date_add("1997-12-31 23:59:59.000002",INTERVAL "10000:99:99.999999" HOUR_MICROSECOND) 1999-02-21 17:40:39.000001 select date_add("1997-12-31 23:59:59.000002",INTERVAL "10000:99.999999" MINUTE_MICROSECOND); date_add("1997-12-31 23:59:59.000002",INTERVAL "10000:99.999999" MINUTE_MICROSECOND) 1998-01-07 22:41:39.000001 select date_add("1997-12-31 23:59:59.000002",INTERVAL "10000.999999" SECOND_MICROSECOND); date_add("1997-12-31 23:59:59.000002",INTERVAL "10000.999999" SECOND_MICROSECOND) 1998-01-01 02:46:40.000001 select date_add("1997-12-31 23:59:59.000002",INTERVAL "999999" MICROSECOND); date_add("1997-12-31 23:59:59.000002",INTERVAL "999999" MICROSECOND) 1998-01-01 00:00:00.000001 select date_sub("1998-01-01 00:00:00.000001",INTERVAL "1 1:1:1.000002" DAY_MICROSECOND); date_sub("1998-01-01 00:00:00.000001",INTERVAL "1 1:1:1.000002" DAY_MICROSECOND) 1997-12-30 22:58:58.999999 select date_sub("1998-01-01 00:00:00.000001",INTERVAL "1:1:1.000002" HOUR_MICROSECOND); date_sub("1998-01-01 00:00:00.000001",INTERVAL "1:1:1.000002" HOUR_MICROSECOND) 1997-12-31 22:58:58.999999 select date_sub("1998-01-01 00:00:00.000001",INTERVAL "1:1.000002" MINUTE_MICROSECOND); date_sub("1998-01-01 00:00:00.000001",INTERVAL "1:1.000002" MINUTE_MICROSECOND) 1997-12-31 23:58:58.999999 select date_sub("1998-01-01 00:00:00.000001",INTERVAL "1.000002" SECOND_MICROSECOND); date_sub("1998-01-01 00:00:00.000001",INTERVAL "1.000002" SECOND_MICROSECOND) 1997-12-31 23:59:58.999999 select date_sub("1998-01-01 00:00:00.000001",INTERVAL "000002" MICROSECOND); date_sub("1998-01-01 00:00:00.000001",INTERVAL "000002" MICROSECOND) 1997-12-31 23:59:59.999999 select adddate("1997-12-31 23:59:59.000001", 10); adddate("1997-12-31 23:59:59.000001", 10) 1998-01-10 23:59:59.000001 select subdate("1997-12-31 23:59:59.000001", 10); subdate("1997-12-31 23:59:59.000001", 10) 1997-12-21 23:59:59.000001 select datediff("1997-12-31 23:59:59.000001","1997-12-30"); datediff("1997-12-31 23:59:59.000001","1997-12-30") 1 select datediff("1997-11-31 23:59:59.000001","1997-12-31"); datediff("1997-11-31 23:59:59.000001","1997-12-31") -30 select datediff("1997-11-31 23:59:59.000001",null); datediff("1997-11-31 23:59:59.000001",null) NULL select weekofyear("1997-11-31 23:59:59.000001"); weekofyear("1997-11-31 23:59:59.000001") 49 select makedate(1997,1); makedate(1997,1) 1997-01-01 select makedate(1997,0); makedate(1997,0) NULL select addtime("1997-12-31 23:59:59.999999", "1 1:1:1.000002"); addtime("1997-12-31 23:59:59.999999", "1 1:1:1.000002") 1998-01-02 01:01:01.000001 select subtime("1997-12-31 23:59:59.000001", "1 1:1:1.000002"); subtime("1997-12-31 23:59:59.000001", "1 1:1:1.000002") 1997-12-30 22:58:57.999999 select addtime("1997-12-31 23:59:59.999999", "1998-01-01 01:01:01.999999"); addtime("1997-12-31 23:59:59.999999", "1998-01-01 01:01:01.999999") NULL select subtime("1997-12-31 23:59:59.999999", "1998-01-01 01:01:01.999999"); subtime("1997-12-31 23:59:59.999999", "1998-01-01 01:01:01.999999") NULL select subtime("01:00:00.999999", "02:00:00.999998"); subtime("01:00:00.999999", "02:00:00.999998") -00:59:59.999999 select subtime("02:01:01.999999", "01:01:01.999999"); subtime("02:01:01.999999", "01:01:01.999999") 01:00:00.000000 select timediff("1997-01-01 23:59:59.000001","1995-12-31 23:59:59.000002"); timediff("1997-01-01 23:59:59.000001","1995-12-31 23:59:59.000002") 8807:59:59.999999 select timediff("1997-12-31 23:59:59.000001","1997-12-30 01:01:01.000002"); timediff("1997-12-31 23:59:59.000001","1997-12-30 01:01:01.000002") 46:58:57.999999 select timediff("1997-12-30 23:59:59.000001","1997-12-31 23:59:59.000002"); timediff("1997-12-30 23:59:59.000001","1997-12-31 23:59:59.000002") -24:00:00.000001 select timediff("1997-12-31 23:59:59.000001","23:59:59.000001"); timediff("1997-12-31 23:59:59.000001","23:59:59.000001") NULL select timediff("2000:01:01 00:00:00", "2000:01:01 00:00:00.000001"); timediff("2000:01:01 00:00:00", "2000:01:01 00:00:00.000001") -00:00:00.000001 select timediff("2005-01-11 15:48:49.999999", "2005-01-11 15:48:50"); timediff("2005-01-11 15:48:49.999999", "2005-01-11 15:48:50") -00:00:00.000001 select maketime(10,11,12); maketime(10,11,12) 10:11:12 select maketime(25,11,12); maketime(25,11,12) 25:11:12 select maketime(-25,11,12); maketime(-25,11,12) -25:11:12 select timestamp("2001-12-01", "01:01:01.999999"); timestamp("2001-12-01", "01:01:01.999999") 2001-12-01 01:01:01.999999 select timestamp("2001-13-01", "01:01:01.000001"); timestamp("2001-13-01", "01:01:01.000001") NULL Warnings: Warning 1292 Truncated incorrect datetime value: '2001-13-01' select timestamp("2001-12-01", "25:01:01"); timestamp("2001-12-01", "25:01:01") 2001-12-02 01:01:01 select timestamp("2001-12-01 01:01:01.000100"); timestamp("2001-12-01 01:01:01.000100") 2001-12-01 01:01:01.000100 select timestamp("2001-12-01"); timestamp("2001-12-01") 2001-12-01 00:00:00 select day("1997-12-31 23:59:59.000001"); day("1997-12-31 23:59:59.000001") 31 select date("1997-12-31 23:59:59.000001"); date("1997-12-31 23:59:59.000001") 1997-12-31 select date("1997-13-31 23:59:59.000001"); date("1997-13-31 23:59:59.000001") NULL Warnings: Warning 1292 Truncated incorrect datetime value: '1997-13-31 23:59:59.000001' select time("1997-12-31 23:59:59.000001"); time("1997-12-31 23:59:59.000001") 23:59:59.000001 select time("1997-12-31 25:59:59.000001"); time("1997-12-31 25:59:59.000001") NULL Warnings: Warning 1292 Truncated incorrect time value: '1997-12-31 25:59:59.000001' select microsecond("1997-12-31 23:59:59.000001"); microsecond("1997-12-31 23:59:59.000001") 1 create table t1 select makedate(1997,1) as f1, addtime(cast("1997-12-31 23:59:59.000001" as datetime), "1 1:1:1.000002") as f2, addtime(cast("23:59:59.999999" as time) , "1 1:1:1.000002") as f3, timediff("1997-12-31 23:59:59.000001","1997-12-30 01:01:01.000002") as f4, timediff("1997-12-30 23:59:59.000001","1997-12-31 23:59:59.000002") as f5, maketime(10,11,12) as f6, timestamp(cast("2001-12-01" as date), "01:01:01") as f7, date("1997-12-31 23:59:59.000001") as f8, time("1997-12-31 23:59:59.000001") as f9; describe t1; Field Type Null Key Default Extra f1 date 0000-00-00 f2 datetime YES NULL f3 time YES NULL f4 time 00:00:00 f5 time 00:00:00 f6 time 00:00:00 f7 datetime YES NULL f8 date YES NULL f9 time YES NULL select * from t1; f1 f2 f3 f4 f5 f6 f7 f8 f9 1997-01-01 1998-01-02 01:01:00 49:01:01 46:58:57 -24:00:00 10:11:12 2001-12-01 01:01:01 1997-12-31 23:59:59 create table test(t1 datetime, t2 time, t3 time, t4 datetime); insert into test values ('2001-01-01 01:01:01', '01:01:01', null, '2001-02-01 01:01:01'), ('2001-01-01 01:01:01', '-01:01:01', '-23:59:59', "1997-12-31 23:59:59.000001"), ('1997-12-31 23:59:59.000001', '-23:59:59', '-01:01:01', '2001-01-01 01:01:01'), ('2001-01-01 01:01:01', '01:01:01', '-1 01:01:01', null), ('2001-01-01 01:01:01', '-01:01:01', '1 01:01:01', '2001-01-01 01:01:01'), ('2001-01-01 01:01:01', null, '-1 01:01:01', null), (null, null, null, null), ('2001-01-01 01:01:01', '01:01:01', '1 01:01:01', '2001-01-01 01:01:01'); SELECT ADDTIME(t1,t2) As ttt, ADDTIME(t2, t3) As qqq from test; ttt qqq 2001-01-01 02:02:02 NULL 2001-01-01 00:00:00 -25:01:00 1997-12-31 00:00:00 -25:01:00 2001-01-01 02:02:02 -24:00:00 2001-01-01 00:00:00 24:00:00 NULL NULL NULL NULL 2001-01-01 02:02:02 26:02:02 SELECT TIMEDIFF(t1,t4) As ttt, TIMEDIFF(t2, t3) As qqq from test; ttt qqq -744:00:00 NULL 26305:01:02 22:58:58 -26305:01:02 -22:58:58 NULL 26:02:02 00:00:00 -26:02:02 NULL NULL NULL NULL 00:00:00 -24:00:00 drop table t1, test; select addtime("-01:01:01.01", "-23:59:59.1") as a; a -25:01:00.110000 select microsecond("1997-12-31 23:59:59.01") as a; a 10000 select microsecond(19971231235959.01) as a; a 10000 select date_add("1997-12-31",INTERVAL "10.09" SECOND_MICROSECOND) as a; a 1997-12-31 00:00:10.090000 select str_to_date("2003-01-02 10:11:12.0012", "%Y-%m-%d %H:%i:%S.%f"); str_to_date("2003-01-02 10:11:12.0012", "%Y-%m-%d %H:%i:%S.%f") 2003-01-02 10:11:12.001200