func_sapdb.test 4.27 KB
Newer Older
unknown's avatar
unknown committed
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53
--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-31 23:59:59.000001","1997-12-31");
select datediff("1997-11-31 23:59:59.000001",null);

select weekofyear("1997-11-31 23:59:59.000001");

select makedate(1997,1);
select makedate(1997,0);

#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");

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");
unknown's avatar
unknown committed
54
select timediff("2000:01:01 00:00:00", "2000:01:01 00:00:00.000001");
unknown's avatar
unknown committed
55 56 57 58 59 60 61 62 63

select maketime(10,11,12);
select maketime(25,11,12);
select maketime(-25,11,12);

#Extraction functions
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");
unknown's avatar
unknown committed
64 65
select timestamp("2001-12-01 01:01:01.000100");
select timestamp("2001-12-01");
unknown's avatar
unknown committed
66 67 68 69 70 71 72 73 74 75 76 77 78 79
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");

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,
unknown's avatar
unknown committed
80
   timestamp(cast("2001-12-01" as date), "01:01:01") as f7,
unknown's avatar
unknown committed
81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99
   date("1997-12-31 23:59:59.000001") as f8,
   time("1997-12-31 23:59:59.000001") as f9;
describe t1;
select * from t1;

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', 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;
SELECT TIMEDIFF(t1,t4) As ttt, TIMEDIFF(t2, t3) As qqq from test;

drop table t1, test;