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
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
|
--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");
# This will give an error
--error 1686 # Bad datetime
select datediff("1997-11-31 23:59:59.000001","1997-12-31");
select datediff("1997-11-30 23:59:59.000001",null);
select makedate(03,1);
select makedate('0003',1);
select makedate(1997,1);
select makedate(1997,0);
select makedate(9999,365);
select makedate(9999,366);
select makedate(100,1);
# Extraction functions
# PS doesn't support fractional seconds
--disable_ps_protocol
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 microsecond("1997-12-31 23:59:59.000001");
--enable_ps_protocol
create table t1
select makedate(1997,1) as f1,
date("1997-12-31 23:59:59.000001") as f8;
describe t1;
# PS doesn't support fractional seconds
--disable_ps_protocol
select * from t1;
--enable_ps_protocol
create table test(t1 datetime, t4 datetime);
insert into test values
('2001-01-01 01:01:01', '2001-02-01 01:01:01'),
('2001-01-01 01:01:01', "1997-12-31 23:59:59.000001"),
('1997-12-31 23:59:59.000001', '2001-01-01 01:01:01'),
('2001-01-01 01:01:01', null),
('2001-01-01 01:01:01', '2001-01-01 01:01:01'),
('2001-01-01 01:01:01', null),
(null, null),
('2001-01-01 01:01:01', '2001-01-01 01:01:01');
drop table t1, test;
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
# End of 4.1 tests
|