--disable_warnings drop table if exists t1, test; --enable_warnings # # time functions # select extract(DAY_MICROSECOND FROM "1999-01-02 10:11:12.000123"); select extract(HOUR_MICROSECOND FROM "1999-01-02 10:11:12.000123"); select extract(MINUTE_MICROSECOND FROM "1999-01-02 10:11:12.000123"); select extract(SECOND_MICROSECOND FROM "1999-01-02 10:11:12.000123"); select extract(MICROSECOND FROM "1999-01-02 10:11:12.000123"); select date_format("1997-12-31 23:59:59.000002", "%f"); select date_add("1997-12-31 23:59:59.000002",INTERVAL "10000 99:99:99.999999" DAY_MICROSECOND); select date_add("1997-12-31 23:59:59.000002",INTERVAL "10000:99:99.999999" HOUR_MICROSECOND); select date_add("1997-12-31 23:59:59.000002",INTERVAL "10000:99.999999" MINUTE_MICROSECOND); select date_add("1997-12-31 23:59:59.000002",INTERVAL "10000.999999" SECOND_MICROSECOND); select date_add("1997-12-31 23:59:59.000002",INTERVAL "999999" MICROSECOND); select date_sub("1998-01-01 00:00:00.000001",INTERVAL "1 1:1:1.000002" DAY_MICROSECOND); select date_sub("1998-01-01 00:00:00.000001",INTERVAL "1:1:1.000002" HOUR_MICROSECOND); select date_sub("1998-01-01 00:00:00.000001",INTERVAL "1:1.000002" MINUTE_MICROSECOND); select date_sub("1998-01-01 00:00:00.000001",INTERVAL "1.000002" SECOND_MICROSECOND); select date_sub("1998-01-01 00:00:00.000001",INTERVAL "000002" MICROSECOND); #Date functions select adddate("1997-12-31 23:59:59.000001", 10); select subdate("1997-12-31 23:59:59.000001", 10); select datediff("1997-12-31 23:59:59.000001","1997-12-30"); select datediff("1997-11-30 23:59:59.000001","1997-12-31"); SET @@SQL_MODE="ALLOW_INVALID_DATES"; select datediff("1997-11-31 23:59:59.000001","1997-12-31"); SET @@SQL_MODE=""; # This will give a warning select datediff("1997-11-31 23:59:59.000001","1997-12-31"); select datediff("1997-11-30 23:59:59.000001",null); select weekofyear("1997-11-30 23:59:59.000001"); select makedate(1997,1); select makedate(1997,0); select makedate(9999,365); select makedate(9999,366); #Time functions select addtime("1997-12-31 23:59:59.999999", "1 1:1:1.000002"); select subtime("1997-12-31 23:59:59.000001", "1 1:1:1.000002"); select addtime("1997-12-31 23:59:59.999999", "1998-01-01 01:01:01.999999"); select subtime("1997-12-31 23:59:59.999999", "1998-01-01 01:01:01.999999"); select subtime("01:00:00.999999", "02:00:00.999998"); select subtime("02:01:01.999999", "01:01:01.999999"); # PS doesn't support fractional seconds --disable_ps_protocol select timediff("1997-01-01 23:59:59.000001","1995-12-31 23:59:59.000002"); select timediff("1997-12-31 23:59:59.000001","1997-12-30 01:01:01.000002"); select timediff("1997-12-30 23:59:59.000001","1997-12-31 23:59:59.000002"); select timediff("1997-12-31 23:59:59.000001","23:59:59.000001"); select timediff("2000:01:01 00:00:00", "2000:01:01 00:00:00.000001"); select timediff("2005-01-11 15:48:49.999999", "2005-01-11 15:48:50"); --enable_ps_protocol select maketime(10,11,12); select maketime(25,11,12); select maketime(-25,11,12); # Extraction functions # PS doesn't support fractional seconds --disable_ps_protocol select timestamp("2001-12-01", "01:01:01.999999"); select timestamp("2001-13-01", "01:01:01.000001"); select timestamp("2001-12-01", "25:01:01"); select timestamp("2001-12-01 01:01:01.000100"); select timestamp("2001-12-01"); select day("1997-12-31 23:59:59.000001"); select date("1997-12-31 23:59:59.000001"); select date("1997-13-31 23:59:59.000001"); select time("1997-12-31 23:59:59.000001"); select time("1997-12-31 25:59:59.000001"); select microsecond("1997-12-31 23:59:59.000001"); --enable_ps_protocol 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; # PS doesn't support fractional seconds --disable_ps_protocol select * from t1; --enable_ps_protocol 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; # PS doesn't support fractional seconds --disable_ps_protocol SELECT TIMEDIFF(t1, t4) As ttt, TIMEDIFF(t2, t3) As qqq, TIMEDIFF(t3, t2) As eee, TIMEDIFF(t2, t4) As rrr from test; --enable_ps_protocol drop table t1, test; select addtime("-01:01:01.01", "-23:59:59.1") as a; select microsecond("1997-12-31 23:59:59.01") as a; select microsecond(19971231235959.01) as a; select date_add("1997-12-31",INTERVAL "10.09" SECOND_MICROSECOND) as a; # PS doesn't support fractional seconds --disable_ps_protocol select str_to_date("2003-01-02 10:11:12.0012", "%Y-%m-%d %H:%i:%S.%f"); --enable_ps_protocol --disable_query_log drop database pbxt; --enable_query_log # End of 4.1 tests