← Back to branch summary

~drizzle-trunk/drizzle/development

« back to all changes in this revision

Viewing changes to tests/r/func_sapdb.result

  • Committer: Brian Aker
  • Date: 2008-09-04 19:31:00 UTC
  • Revision ID: brian@tangent.org-20080904193100-l849hgghfy4urj43
Changing default character set from this point on.

Show diffs side-by-side

added added

removed removed

Lines of Context:
tests/r/func_sapdb.result
1
 
drop table if exists t1, test;
2
 
select extract(DAY_MICROSECOND FROM "1999-01-02 10:11:12.000123");
3
 
extract(DAY_MICROSECOND FROM "1999-01-02 10:11:12.000123")
4
 
2101112000123
5
 
select extract(HOUR_MICROSECOND FROM "1999-01-02 10:11:12.000123");
6
 
extract(HOUR_MICROSECOND FROM "1999-01-02 10:11:12.000123")
7
 
101112000123
8
 
select extract(MINUTE_MICROSECOND FROM "1999-01-02 10:11:12.000123");
9
 
extract(MINUTE_MICROSECOND FROM "1999-01-02 10:11:12.000123")
10
 
1112000123
11
 
select extract(SECOND_MICROSECOND FROM "1999-01-02 10:11:12.000123");
12
 
extract(SECOND_MICROSECOND FROM "1999-01-02 10:11:12.000123")
13
 
12000123
14
 
select extract(MICROSECOND FROM "1999-01-02 10:11:12.000123");
15
 
extract(MICROSECOND FROM "1999-01-02 10:11:12.000123")
16
 
123
17
 
select date_format("1997-12-31 23:59:59.000002", "%f");
18
 
date_format("1997-12-31 23:59:59.000002", "%f")
19
 
000002
20
 
select date_add("1997-12-31 23:59:59.000002",INTERVAL "10000 99:99:99.999999" DAY_MICROSECOND);
21
 
date_add("1997-12-31 23:59:59.000002",INTERVAL "10000 99:99:99.999999" DAY_MICROSECOND)
22
 
2025-05-23 04:40:39.000001
23
 
select date_add("1997-12-31 23:59:59.000002",INTERVAL "10000:99:99.999999" HOUR_MICROSECOND);
24
 
date_add("1997-12-31 23:59:59.000002",INTERVAL "10000:99:99.999999" HOUR_MICROSECOND)
25
 
1999-02-21 17:40:39.000001
26
 
select date_add("1997-12-31 23:59:59.000002",INTERVAL "10000:99.999999" MINUTE_MICROSECOND);
27
 
date_add("1997-12-31 23:59:59.000002",INTERVAL "10000:99.999999" MINUTE_MICROSECOND)
28
 
1998-01-07 22:41:39.000001
29
 
select date_add("1997-12-31 23:59:59.000002",INTERVAL "10000.999999" SECOND_MICROSECOND);
30
 
date_add("1997-12-31 23:59:59.000002",INTERVAL "10000.999999" SECOND_MICROSECOND)
31
 
1998-01-01 02:46:40.000001
32
 
select date_add("1997-12-31 23:59:59.000002",INTERVAL "999999" MICROSECOND);
33
 
date_add("1997-12-31 23:59:59.000002",INTERVAL "999999" MICROSECOND)
34
 
1998-01-01 00:00:00.000001
35
 
select date_sub("1998-01-01 00:00:00.000001",INTERVAL "1 1:1:1.000002" DAY_MICROSECOND);
36
 
date_sub("1998-01-01 00:00:00.000001",INTERVAL "1 1:1:1.000002" DAY_MICROSECOND)
37
 
1997-12-30 22:58:58.999999
38
 
select date_sub("1998-01-01 00:00:00.000001",INTERVAL "1:1:1.000002" HOUR_MICROSECOND);
39
 
date_sub("1998-01-01 00:00:00.000001",INTERVAL "1:1:1.000002" HOUR_MICROSECOND)
40
 
1997-12-31 22:58:58.999999
41
 
select date_sub("1998-01-01 00:00:00.000001",INTERVAL "1:1.000002" MINUTE_MICROSECOND);
42
 
date_sub("1998-01-01 00:00:00.000001",INTERVAL "1:1.000002" MINUTE_MICROSECOND)
43
 
1997-12-31 23:58:58.999999
44
 
select date_sub("1998-01-01 00:00:00.000001",INTERVAL "1.000002" SECOND_MICROSECOND);
45
 
date_sub("1998-01-01 00:00:00.000001",INTERVAL "1.000002" SECOND_MICROSECOND)
46
 
1997-12-31 23:59:58.999999
47
 
select date_sub("1998-01-01 00:00:00.000001",INTERVAL "000002" MICROSECOND);
48
 
date_sub("1998-01-01 00:00:00.000001",INTERVAL "000002" MICROSECOND)
49
 
1997-12-31 23:59:59.999999
50
 
select adddate("1997-12-31 23:59:59.000001", 10);
51
 
adddate("1997-12-31 23:59:59.000001", 10)
52
 
1998-01-10 23:59:59.000001
53
 
select subdate("1997-12-31 23:59:59.000001", 10);
54
 
subdate("1997-12-31 23:59:59.000001", 10)
55
 
1997-12-21 23:59:59.000001
56
 
select datediff("1997-12-31 23:59:59.000001","1997-12-30");
57
 
datediff("1997-12-31 23:59:59.000001","1997-12-30")
58
 
1
59
 
select datediff("1997-11-30 23:59:59.000001","1997-12-31");
60
 
datediff("1997-11-30 23:59:59.000001","1997-12-31")
61
 
-31
62
 
select datediff("1997-11-31 23:59:59.000001","1997-12-31");
63
 
datediff("1997-11-31 23:59:59.000001","1997-12-31")
64
 
NULL
65
 
Warnings:
66
 
Warning 1292    Incorrect datetime value: '1997-11-31 23:59:59.000001'
67
 
select datediff("1997-11-30 23:59:59.000001",null);
68
 
datediff("1997-11-30 23:59:59.000001",null)
69
 
NULL
70
 
select weekofyear("1997-11-30 23:59:59.000001");
71
 
weekofyear("1997-11-30 23:59:59.000001")
72
 
48
73
 
select makedate(03,1);
74
 
makedate(03,1)
75
 
2003-01-01
76
 
select makedate('0003',1);
77
 
makedate('0003',1)
78
 
2003-01-01
79
 
select makedate(1997,1);
80
 
makedate(1997,1)
81
 
1997-01-01
82
 
select makedate(1997,0);
83
 
makedate(1997,0)
84
 
NULL
85
 
select makedate(9999,365);
86
 
makedate(9999,365)
87
 
9999-12-31
88
 
select makedate(9999,366);
89
 
makedate(9999,366)
90
 
NULL
91
 
select makedate(100,1);
92
 
makedate(100,1)
93
 
0100-01-01
94
 
select addtime("1997-12-31 23:59:59.999999", "1 1:1:1.000002");
95
 
addtime("1997-12-31 23:59:59.999999", "1 1:1:1.000002")
96
 
1998-01-02 01:01:01.000001
97
 
select subtime("1997-12-31 23:59:59.000001", "1 1:1:1.000002");
98
 
subtime("1997-12-31 23:59:59.000001", "1 1:1:1.000002")
99
 
1997-12-30 22:58:57.999999
100
 
select addtime("1997-12-31 23:59:59.999999", "1998-01-01 01:01:01.999999");
101
 
addtime("1997-12-31 23:59:59.999999", "1998-01-01 01:01:01.999999")
102
 
NULL
103
 
select subtime("1997-12-31 23:59:59.999999", "1998-01-01 01:01:01.999999");
104
 
subtime("1997-12-31 23:59:59.999999", "1998-01-01 01:01:01.999999")
105
 
NULL
106
 
select subtime("01:00:00.999999", "02:00:00.999998");
107
 
subtime("01:00:00.999999", "02:00:00.999998")
108
 
-00:59:59.999999
109
 
select subtime("02:01:01.999999", "01:01:01.999999");
110
 
subtime("02:01:01.999999", "01:01:01.999999")
111
 
01:00:00.000000
112
 
select timediff("1997-01-01 23:59:59.000001","1995-12-31 23:59:59.000002");
113
 
timediff("1997-01-01 23:59:59.000001","1995-12-31 23:59:59.000002")
114
 
838:59:59
115
 
Warnings:
116
 
Warning 1292    Truncated incorrect time value: '8807:59:59.999999'
117
 
select timediff("1997-12-31 23:59:59.000001","1997-12-30 01:01:01.000002");
118
 
timediff("1997-12-31 23:59:59.000001","1997-12-30 01:01:01.000002")
119
 
46:58:57.999999
120
 
select timediff("1997-12-30 23:59:59.000001","1997-12-31 23:59:59.000002");
121
 
timediff("1997-12-30 23:59:59.000001","1997-12-31 23:59:59.000002")
122
 
-24:00:00.000001
123
 
select timediff("1997-12-31 23:59:59.000001","23:59:59.000001");
124
 
timediff("1997-12-31 23:59:59.000001","23:59:59.000001")
125
 
NULL
126
 
select timediff("2000:01:01 00:00:00", "2000:01:01 00:00:00.000001");
127
 
timediff("2000:01:01 00:00:00", "2000:01:01 00:00:00.000001")
128
 
-00:00:00.000001
129
 
select timediff("2005-01-11 15:48:49.999999", "2005-01-11 15:48:50");
130
 
timediff("2005-01-11 15:48:49.999999", "2005-01-11 15:48:50")
131
 
-00:00:00.000001
132
 
select maketime(10,11,12);
133
 
maketime(10,11,12)
134
 
10:11:12
135
 
select maketime(25,11,12);
136
 
maketime(25,11,12)
137
 
25:11:12
138
 
select maketime(-25,11,12);
139
 
maketime(-25,11,12)
140
 
-25:11:12
141
 
select timestamp("2001-12-01", "01:01:01.999999");
142
 
timestamp("2001-12-01", "01:01:01.999999")
143
 
2001-12-01 01:01:01.999999
144
 
select timestamp("2001-13-01", "01:01:01.000001");
145
 
timestamp("2001-13-01", "01:01:01.000001")
146
 
NULL
147
 
Warnings:
148
 
Warning 1292    Incorrect datetime value: '2001-13-01'
149
 
select timestamp("2001-12-01", "25:01:01");
150
 
timestamp("2001-12-01", "25:01:01")
151
 
2001-12-02 01:01:01
152
 
select timestamp("2001-12-01 01:01:01.000100");
153
 
timestamp("2001-12-01 01:01:01.000100")
154
 
2001-12-01 01:01:01.000100
155
 
select timestamp("2001-12-01");
156
 
timestamp("2001-12-01")
157
 
2001-12-01 00:00:00
158
 
select day("1997-12-31 23:59:59.000001");
159
 
day("1997-12-31 23:59:59.000001")
160
 
31
161
 
select date("1997-12-31 23:59:59.000001");
162
 
date("1997-12-31 23:59:59.000001")
163
 
1997-12-31
164
 
select date("1997-13-31 23:59:59.000001");
165
 
date("1997-13-31 23:59:59.000001")
166
 
NULL
167
 
Warnings:
168
 
Warning 1292    Incorrect datetime value: '1997-13-31 23:59:59.000001'
169
 
select time("1997-12-31 23:59:59.000001");
170
 
time("1997-12-31 23:59:59.000001")
171
 
23:59:59.000001
172
 
select time("1997-12-31 25:59:59.000001");
173
 
time("1997-12-31 25:59:59.000001")
174
 
NULL
175
 
Warnings:
176
 
Warning 1292    Truncated incorrect time value: '1997-12-31 25:59:59.000001'
177
 
select microsecond("1997-12-31 23:59:59.000001");
178
 
microsecond("1997-12-31 23:59:59.000001")
179
 
1
180
 
create table t1 
181
 
select makedate(1997,1) as f1,
182
 
addtime(cast("1997-12-31 23:59:59.000001" as datetime), "1 1:1:1.000002") as f2,
183
 
addtime(cast("23:59:59.999999" as time) , "1 1:1:1.000002") as f3,
184
 
timediff("1997-12-31 23:59:59.000001","1997-12-30 01:01:01.000002") as f4,
185
 
timediff("1997-12-30 23:59:59.000001","1997-12-31 23:59:59.000002") as f5,
186
 
maketime(10,11,12) as f6,
187
 
timestamp(cast("2001-12-01" as date), "01:01:01") as f7,
188
 
date("1997-12-31 23:59:59.000001") as f8,
189
 
time("1997-12-31 23:59:59.000001") as f9;
190
 
describe t1;
191
 
Field   Type    Null    Key     Default Extra
192
 
f1      date    YES             NULL    
193
 
f2      datetime        YES             NULL    
194
 
f3      time    YES             NULL    
195
 
f4      time    YES             NULL    
196
 
f5      time    YES             NULL    
197
 
f6      time    YES             NULL    
198
 
f7      datetime        YES             NULL    
199
 
f8      date    YES             NULL    
200
 
f9      time    YES             NULL    
201
 
select * from t1;
202
 
f1      f2      f3      f4      f5      f6      f7      f8      f9
203
 
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
204
 
create table test(t1 datetime, t2 time, t3 time, t4 datetime);
205
 
insert into test values 
206
 
('2001-01-01 01:01:01', '01:01:01', null, '2001-02-01 01:01:01'),
207
 
('2001-01-01 01:01:01', '-01:01:01', '-23:59:59', "1997-12-31 23:59:59.000001"),
208
 
('1997-12-31 23:59:59.000001', '-23:59:59', '-01:01:01', '2001-01-01 01:01:01'),
209
 
('2001-01-01 01:01:01', '01:01:01', '-1 01:01:01', null),
210
 
('2001-01-01 01:01:01', '-01:01:01', '1 01:01:01', '2001-01-01 01:01:01'),
211
 
('2001-01-01 01:01:01', null, '-1 01:01:01', null),
212
 
(null, null, null, null),
213
 
('2001-01-01 01:01:01', '01:01:01', '1 01:01:01', '2001-01-01 01:01:01');
214
 
SELECT ADDTIME(t1,t2) As ttt, ADDTIME(t2, t3) As qqq from test;
215
 
ttt     qqq
216
 
2001-01-01 02:02:02     NULL
217
 
2001-01-01 00:00:00     -25:01:00
218
 
1997-12-31 00:00:00     -25:01:00
219
 
2001-01-01 02:02:02     -24:00:00
220
 
2001-01-01 00:00:00     24:00:00
221
 
NULL    NULL
222
 
NULL    NULL
223
 
2001-01-01 02:02:02     26:02:02
224
 
SELECT TIMEDIFF(t1, t4) As ttt, TIMEDIFF(t2, t3) As qqq,
225
 
TIMEDIFF(t3, t2) As eee, TIMEDIFF(t2, t4) As rrr from test;
226
 
ttt     qqq     eee     rrr
227
 
-744:00:00      NULL    NULL    NULL
228
 
838:59:59       22:58:58        -22:58:58       NULL
229
 
-838:59:59      -22:58:58       22:58:58        NULL
230
 
NULL    26:02:02        -26:02:02       NULL
231
 
00:00:00        -26:02:02       26:02:02        NULL
232
 
NULL    NULL    NULL    NULL
233
 
NULL    NULL    NULL    NULL
234
 
00:00:00        -24:00:00       24:00:00        NULL
235
 
Warnings:
236
 
Warning 1292    Truncated incorrect time value: '26305:01:02'
237
 
Warning 1292    Truncated incorrect time value: '-26305:01:02'
238
 
drop table t1, test;
239
 
select addtime("-01:01:01.01", "-23:59:59.1") as a;
240
 
a
241
 
-25:01:00.110000
242
 
select microsecond("1997-12-31 23:59:59.01") as a;
243
 
a
244
 
10000
245
 
select microsecond(19971231235959.01) as a;
246
 
a
247
 
10000
248
 
select date_add("1997-12-31",INTERVAL "10.09" SECOND_MICROSECOND) as a;
249
 
a
250
 
1997-12-31 00:00:10.090000
251
 
select str_to_date("2003-01-02 10:11:12.0012", "%Y-%m-%d %H:%i:%S.%f");
252
 
str_to_date("2003-01-02 10:11:12.0012", "%Y-%m-%d %H:%i:%S.%f")
253
 
2003-01-02 10:11:12.001200