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
|
# name: test/sql/function/list/generate_series_timestamp.test
# description: Test generate_series/range function
# group: [list]
# standard generate_series (inclusive bound)
query I
SELECT generate_series(timestamp '2020-01-01', timestamp '2020-07-01', interval '3' month);
----
['2020-01-01 00:00:00', '2020-04-01 00:00:00', '2020-07-01 00:00:00']
# exclusive bound
query I
SELECT range(timestamp '2020-01-01', timestamp '2020-07-01', interval '3' month);
----
['2020-01-01 00:00:00', '2020-04-01 00:00:00']
# negative interval
query I
SELECT generate_series(timestamp '2020-06-01', timestamp '2020-01-01', -interval '3' month);
----
['2020-06-01 00:00:00', '2020-03-01 00:00:00']
# start = end
query I
SELECT generate_series(timestamp '2020-01-01', timestamp '2020-01-01', interval '1' day);
----
['2020-01-01 00:00:00']
query I
SELECT range(timestamp '2020-01-01', timestamp '2020-01-01', interval '1' day);
----
[]
# positive interval but start > end
query I
SELECT generate_series(timestamp '2020-06-01', timestamp '2020-01-01', interval '3' month);
----
[]
# negative interval but start < end
query I
SELECT generate_series(timestamp '2020-01-01', timestamp '2020-06-01', -interval '3' month);
----
[]
# null values
query I
SELECT generate_series(NULL, timestamp '2020-06-01', -interval '3' month);
----
NULL
query I
SELECT generate_series(timestamp '2020-01-01', NULL, -interval '3' month);
----
NULL
query I
SELECT generate_series(timestamp '2020-01-01', timestamp '2020-06-01', NULL);
----
NULL
# mixed sign interval not supported
statement error
SELECT generate_series(timestamp '2020-01-01', timestamp '2020-06-01', interval '3' month - interval '3' day);
----
# Infinities will overflow or cause infinite loops so we ban them
statement error
SELECT generate_series('294247-01-10'::TIMESTAMP, 'infinity'::TIMESTAMP, INTERVAL '1 DAY');
----
statement error
SELECT range('294247-01-10'::TIMESTAMP, 'infinity'::TIMESTAMP, INTERVAL '1 DAY');
----
statement error
SELECT generate_series('-infinity'::TIMESTAMP, '290309-12-22 (BC) 00:00:00'::TIMESTAMP, INTERVAL '1 DAY');
----
statement error
SELECT range('-infinity'::TIMESTAMP, '290309-12-22 (BC) 00:00:00'::TIMESTAMP, INTERVAL '1 DAY');
----
statement ok
PRAGMA disable_verification
# > vector size
query I
SELECT count(*) FROM (
SELECT unnest(generate_series(timestamp '2000-01-01', timestamp '2020-06-01', interval '1' day))
);
----
7458
|
