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
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
|
# name: test/sql/function/time/test_date_part.test
# description: DATE_PART test
# group: [time]
statement ok
PRAGMA enable_verification
statement ok
CREATE TABLE times(d TIME, s VARCHAR);
statement ok
INSERT INTO times VALUES ('00:01:20', 'hour'), ('20:08:10.998', 'minute'), ('20:08:10.33', 'second'),
('20:08:10.001', 'millisecond');
# test date_part with different combinations of constant/non-constant columns
query I
SELECT date_part(NULL::VARCHAR, NULL::TIME) FROM times;
----
NULL
NULL
NULL
NULL
query I
SELECT date_part(s, NULL::TIME) FROM times;
----
NULL
NULL
NULL
NULL
# times
query I
SELECT date_part(NULL, d) FROM times;
----
NULL
NULL
NULL
NULL
query I
SELECT date_part(s, TIME '14:28:50.447') FROM times;
----
14
28
50
50447
query I
SELECT date_part('hour', d) FROM times;
----
0
20
20
20
query I
SELECT date_part(s, d) FROM times;
----
0
8
10
10001
foreach datepart timezone timezone_hour timezone_minute
query I
select date_part('${datepart}', time '10:00:00');
----
0
endloop
# time gives errors for date-only parts
statement error
SELECT era(i) FROM times
----
statement error
SELECT year(i) FROM times
----
statement error
SELECT month(i) FROM times
----
statement error
SELECT day(i) FROM times
----
statement error
SELECT decade(i) FROM times
----
statement error
SELECT century(i) FROM times
----
statement error
SELECT millennium(i) FROM times
----
statement error
SELECT quarter(i) FROM times
----
statement error
SELECT dayofweek(i) FROM times
----
statement error
SELECT isodow(i) FROM times
----
statement error
SELECT dayofyear(i) FROM times
----
statement error
SELECT week(i) FROM times
----
statement error
SELECT yearweek(i) FROM times
----
statement error
select extract(dow from time '10:00:00');
----
statement error
select extract(doy from time '10:00:00');
----
statement error
select extract(yearweek from time '10:00:00');
----
statement error
select extract(century from time '10:00:00');
----
statement error
SELECT era(i) FROM times
----
statement error
select extract(era from time '10:00:00');
----
statement error
select date_part('era', time '10:00:00');
----
statement error
select extract(julian from time '10:00:00');
----
statement error
select date_part('julian', time '10:00:00');
----
# Correctness: Compare date_part against function value
foreach partcode hour minute second millisecond microsecond epoch
query III
SELECT * FROM (
SELECT d, DATE_PART('${partcode}', d) AS p, ${partcode}(d) AS f
FROM times
) tbl
WHERE p <> f;
----
endloop
#
# Structs
#
# Correctness: Compare against scalar extract
foreach partcode hour minute second millisecond microsecond epoch timezone timezone_hour timezone_minute
query III
SELECT d, DATE_PART('${partcode}', d) AS p, DATE_PART(['${partcode}'], d) AS st
FROM times
WHERE p <> st['${partcode}'];
----
endloop
# Time parts
query II
SELECT d, DATE_PART(['hour', 'minute', 'microsecond'], d) AS parts
FROM times
ORDER BY 1;
----
00:01:20 {'hour': 0, 'minute': 1, 'microsecond': 20000000}
20:08:10.001 {'hour': 20, 'minute': 8, 'microsecond': 10001000}
20:08:10.33 {'hour': 20, 'minute': 8, 'microsecond': 10330000}
20:08:10.998 {'hour': 20, 'minute': 8, 'microsecond': 10998000}
# Miscellaneous parts
query II
SELECT d, DATE_PART(['epoch', 'second', 'timezone', 'timezone_hour', 'timezone_minute'], d) AS parts
FROM times
ORDER BY 1;
----
00:01:20 {'epoch': 80.0, 'second': 20, 'timezone': 0, 'timezone_hour': 0, 'timezone_minute': 0}
20:08:10.001 {'epoch': 72490.001, 'second': 10, 'timezone': 0, 'timezone_hour': 0, 'timezone_minute': 0}
20:08:10.33 {'epoch': 72490.33, 'second': 10, 'timezone': 0, 'timezone_hour': 0, 'timezone_minute': 0}
20:08:10.998 {'epoch': 72490.998, 'second': 10, 'timezone': 0, 'timezone_hour': 0, 'timezone_minute': 0}
# Function-only parts
query II
SELECT d, epoch_ns(d) FROM times ORDER BY ALL;
----
00:01:20 80000000000
20:08:10.001 72490001000000
20:08:10.33 72490330000000
20:08:10.998 72490998000000
query II
SELECT d, epoch_us(d) FROM times ORDER BY ALL;
----
00:01:20 80000000
20:08:10.001 72490001000
20:08:10.33 72490330000
20:08:10.998 72490998000
query II
SELECT d, epoch_ms(d) FROM times ORDER BY ALL;
----
00:01:20 80000
20:08:10.001 72490001
20:08:10.33 72490330
20:08:10.998 72490998
query II
SELECT d, nanosecond(d) FROM times ORDER BY ALL;
----
00:01:20 20000000000
20:08:10.001 10001000000
20:08:10.33 10330000000
20:08:10.998 10998000000
# Invalid parts
foreach datepart year month day decade century millennium quarter dow isodow doy week isoyear yearweek era julian
statement error
SELECT d, DATE_PART(['${datepart}'], d) AS parts
FROM times
ORDER BY 1;
----
endloop
|
