/
interval.sql
259 lines (217 loc) · 9 KB
/
interval.sql
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
257
258
259
-- test for intervals
-- greater than or equal
select interval '1 day' > interval '23 hour';
select interval '-1 day' >= interval '-23 hour';
select interval '-1 day' > null;
select null > interval '-1 day';
-- less than or equal
select interval '1 minutes' < interval '1 hour';
select interval '-1 day' <= interval '-23 hour';
-- equal
select interval '1 year' = interval '360 days';
select interval '1 year 2 month' = interval '420 days';
select interval '1 year' = interval '365 days';
select interval '1 month' = interval '30 days';
select interval '1 minutes' = interval '1 hour';
select interval '1 minutes' = null;
select null = interval '-1 day';
-- null safe equal
select interval '1 minutes' <=> null;
select null <=> interval '1 minutes';
-- complex interval representation
select INTERVAL '9 years 1 months -1 weeks -4 days -10 hours -46 minutes' > interval '1 minutes';
-- ordering
select cast(v as interval) i from VALUES ('1 seconds'), ('4 seconds'), ('3 seconds') t(v) order by i;
-- unlimited days
select interval '1 month 120 days' > interval '2 month';
select interval '1 month 30 days' = interval '2 month';
-- unlimited microseconds
select interval '1 month 29 days 40 hours' > interval '2 month';
-- max
select max(cast(v as interval)) from VALUES ('1 seconds'), ('4 seconds'), ('3 seconds') t(v);
-- min
select min(cast(v as interval)) from VALUES ('1 seconds'), ('4 seconds'), ('3 seconds') t(v);
-- multiply and divide an interval by a number
select 3 * (timestamp'2019-10-15 10:11:12.001002' - date'2019-10-15');
select interval 4 month 2 weeks 3 microseconds * 1.5;
select (timestamp'2019-10-15' - timestamp'2019-10-14') / 1.5;
-- interval operation with null and zero case
select interval '2 seconds' / 0;
select interval '2 seconds' / null;
select interval '2 seconds' * null;
select null * interval '2 seconds';
-- interval with a positive/negative sign
select -interval '-1 month 1 day -1 second';
select -interval -1 month 1 day -1 second;
select +interval '-1 month 1 day -1 second';
select +interval -1 month 1 day -1 second;
-- make intervals
select make_interval(1);
select make_interval(1, 2);
select make_interval(1, 2, 3);
select make_interval(1, 2, 3, 4);
select make_interval(1, 2, 3, 4, 5);
select make_interval(1, 2, 3, 4, 5, 6);
select make_interval(1, 2, 3, 4, 5, 6, 7.008009);
-- cast string to intervals
select cast('1 second' as interval);
select cast('+1 second' as interval);
select cast('-1 second' as interval);
select cast('+ 1 second' as interval);
select cast('- 1 second' as interval);
select cast('- -1 second' as interval);
select cast('- +1 second' as interval);
-- justify intervals
select justify_days(cast(null as interval));
select justify_hours(cast(null as interval));
select justify_interval(cast(null as interval));
select justify_days(interval '1 month 59 day 25 hour');
select justify_hours(interval '1 month 59 day 25 hour');
select justify_interval(interval '1 month 59 day 25 hour');
select justify_days(interval '1 month -59 day 25 hour');
select justify_hours(interval '1 month -59 day 25 hour');
select justify_interval(interval '1 month -59 day 25 hour');
select justify_days(interval '1 month 59 day -25 hour');
select justify_hours(interval '1 month 59 day -25 hour');
select justify_interval(interval '1 month 59 day -25 hour');
-- interval literal
select interval 13.123456789 seconds, interval -13.123456789 second;
select interval 1 year 2 month 3 week 4 day 5 hour 6 minute 7 seconds 8 millisecond 9 microsecond;
select interval '30' year '25' month '-100' day '40' hour '80' minute '299.889987299' second;
select interval '0 0:0:0.1' day to second;
select interval '10-9' year to month;
select interval '20 15:40:32.99899999' day to hour;
select interval '20 15:40:32.99899999' day to minute;
select interval '20 15:40:32.99899999' day to second;
select interval '15:40:32.99899999' hour to minute;
select interval '15:40.99899999' hour to second;
select interval '15:40' hour to second;
select interval '15:40:32.99899999' hour to second;
select interval '20 40:32.99899999' minute to second;
select interval '40:32.99899999' minute to second;
select interval '40:32' minute to second;
select interval 30 day day;
-- ns is not supported
select interval 10 nanoseconds;
-- map + interval test
select map(1, interval 1 day, 2, interval 3 week);
-- typed interval expression
select interval 'interval 3 year 1 hour';
select interval '3 year 1 hour';
-- malformed interval literal
select interval;
select interval 1 fake_unit;
select interval 1 year to month;
select interval '1' year to second;
select interval '10-9' year to month '2-1' year to month;
select interval '10-9' year to month '12:11:10' hour to second;
select interval '1 15:11' day to minute '12:11:10' hour to second;
select interval 1 year '2-1' year to month;
select interval 1 year '12:11:10' hour to second;
select interval '10-9' year to month '1' year;
select interval '12:11:10' hour to second '1' year;
select interval (-30) day;
select interval (a + 1) day;
select interval 30 day day day;
-- sum interval values
-- null
select sum(cast(null as interval));
-- empty set
select sum(cast(v as interval)) from VALUES ('1 seconds') t(v) where 1=0;
-- basic interval sum
select sum(cast(v as interval)) from VALUES ('1 seconds'), ('2 seconds'), (null) t(v);
select sum(cast(v as interval)) from VALUES ('-1 seconds'), ('2 seconds'), (null) t(v);
select sum(cast(v as interval)) from VALUES ('-1 seconds'), ('-2 seconds'), (null) t(v);
select sum(cast(v as interval)) from VALUES ('-1 weeks'), ('2 seconds'), (null) t(v);
-- group by
select
i,
sum(cast(v as interval))
from VALUES (1, '-1 weeks'), (2, '2 seconds'), (3, null), (1, '5 days') t(i, v)
group by i;
-- having
select
sum(cast(v as interval)) as sv
from VALUES (1, '-1 weeks'), (2, '2 seconds'), (3, null), (1, '5 days') t(i, v)
having sv is not null;
-- window
SELECT
i,
sum(cast(v as interval)) OVER (ORDER BY i ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING)
FROM VALUES(1, '1 seconds'), (1, '2 seconds'), (2, NULL), (2, NULL) t(i,v);
-- average with interval type
-- null
select avg(cast(v as interval)) from VALUES (null) t(v);
-- empty set
select avg(cast(v as interval)) from VALUES ('1 seconds'), ('2 seconds'), (null) t(v) where 1=0;
-- basic interval avg
select avg(cast(v as interval)) from VALUES ('1 seconds'), ('2 seconds'), (null) t(v);
select avg(cast(v as interval)) from VALUES ('-1 seconds'), ('2 seconds'), (null) t(v);
select avg(cast(v as interval)) from VALUES ('-1 seconds'), ('-2 seconds'), (null) t(v);
select avg(cast(v as interval)) from VALUES ('-1 weeks'), ('2 seconds'), (null) t(v);
-- group by
select
i,
avg(cast(v as interval))
from VALUES (1, '-1 weeks'), (2, '2 seconds'), (3, null), (1, '5 days') t(i, v)
group by i;
-- having
select
avg(cast(v as interval)) as sv
from VALUES (1, '-1 weeks'), (2, '2 seconds'), (3, null), (1, '5 days') t(i, v)
having sv is not null;
-- window
SELECT
i,
avg(cast(v as interval)) OVER (ORDER BY i ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING)
FROM VALUES (1,'1 seconds'), (1,'2 seconds'), (2,NULL), (2,NULL) t(i,v);
-- Interval year-month arithmetic
create temporary view interval_arithmetic as
select CAST(dateval AS date), CAST(tsval AS timestamp) from values
('2012-01-01', '2012-01-01')
as interval_arithmetic(dateval, tsval);
select
dateval,
dateval - interval '2-2' year to month,
dateval - interval '-2-2' year to month,
dateval + interval '2-2' year to month,
dateval + interval '-2-2' year to month,
- interval '2-2' year to month + dateval,
interval '2-2' year to month + dateval
from interval_arithmetic;
select
tsval,
tsval - interval '2-2' year to month,
tsval - interval '-2-2' year to month,
tsval + interval '2-2' year to month,
tsval + interval '-2-2' year to month,
- interval '2-2' year to month + tsval,
interval '2-2' year to month + tsval
from interval_arithmetic;
select
interval '2-2' year to month + interval '3-3' year to month,
interval '2-2' year to month - interval '3-3' year to month
from interval_arithmetic;
-- Interval day-time arithmetic
select
dateval,
dateval - interval '99 11:22:33.123456789' day to second,
dateval - interval '-99 11:22:33.123456789' day to second,
dateval + interval '99 11:22:33.123456789' day to second,
dateval + interval '-99 11:22:33.123456789' day to second,
-interval '99 11:22:33.123456789' day to second + dateval,
interval '99 11:22:33.123456789' day to second + dateval
from interval_arithmetic;
select
tsval,
tsval - interval '99 11:22:33.123456789' day to second,
tsval - interval '-99 11:22:33.123456789' day to second,
tsval + interval '99 11:22:33.123456789' day to second,
tsval + interval '-99 11:22:33.123456789' day to second,
-interval '99 11:22:33.123456789' day to second + tsval,
interval '99 11:22:33.123456789' day to second + tsval
from interval_arithmetic;
select
interval '99 11:22:33.123456789' day to second + interval '10 9:8:7.123456789' day to second,
interval '99 11:22:33.123456789' day to second - interval '10 9:8:7.123456789' day to second
from interval_arithmetic;