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
|
# name: test/sql/function/timestamp/test_icu_datediff.test
# description: Test ICU date difference function
# group: [timestamp]
require icu
# Normalise the testing locale
statement ok
SET Calendar = 'gregorian';
statement ok
SET TimeZone = 'America/Los_Angeles';
#
# TIMESTAMP
#
statement ok
CREATE TABLE datetime1 AS
SELECT '2005-12-31 23:59:59.9999999-08'::TIMESTAMPTZ AS startdate, '2006-01-01 00:00:00.0000000-08'::TIMESTAMPTZ AS enddate;
foreach datepart year quarter month day dayofyear hour minute second millisecond microsecond julian
query I
SELECT DATEDIFF('${datepart}', startdate, enddate) FROM datetime1
----
1
endloop
foreach datepart decade century millennium week yearweek isoyear
query I
SELECT DATEDIFF('${datepart}', startdate, enddate) FROM datetime1
----
0
endloop
# ISO Year boundary
query I
SELECT DATEDIFF('isoyear', '2022-01-01 00:00:00-08'::TIMESTAMPTZ, '2022-01-03 00:00:00-08'::TIMESTAMPTZ);
----
1
#
# Infinities
#
foreach specifier year isoyear month day decade century millennium quarter week microseconds milliseconds second minute hour julian
foreach lspecial infinity -infinity 2004-01-31T12:00:00.000050-08
foreach rspecial infinity -infinity
query I
SELECT DATEDIFF('${specifier}', '${lspecial}'::TIMESTAMPTZ, '${rspecial}'::TIMESTAMPTZ);
----
NULL
query I
SELECT DATEDIFF('${specifier}', '${rspecial}'::TIMESTAMPTZ, '${lspecial}'::TIMESTAMPTZ);
----
NULL
endloop
endloop
endloop
# Negation
foreach datepart year quarter month day dayofyear hour minute second millisecond microsecond decade century millennium week yearweek isoyear julian
query I
SELECT DATEDIFF('${datepart}', startdate, enddate) + DATEDIFF('${datepart}', enddate, startdate) FROM datetime1
----
0
endloop
# Table
foreach datepart year month day hour minute second millisecond microsecond
query I
SELECT DATEDIFF('${datepart}', startdate, startdate + INTERVAL 1 ${datepart})
FROM (SELECT '2021-07-30'::TIMESTAMP + INTERVAL (d) DAY AS startdate FROM range(0, 5) tbl(d)) days
----
1
1
1
1
1
endloop
# Week diffs are just day counts
query III
SELECT *, DATE_DIFF('week', lo, hi)
FROM (
SELECT (d - INTERVAL 9 HOUR)::TIMESTAMPTZ AS lo, (d + INTERVAL 7 HOUR)::TIMESTAMPTZ AS hi
FROM generate_series('2022-09-01'::DATE, '2022-09-12'::DATE, INTERVAL 1 DAY) tbl(d)
);
----
2022-08-31 15:00:00-07 2022-09-01 07:00:00-07 0
2022-09-01 15:00:00-07 2022-09-02 07:00:00-07 0
2022-09-02 15:00:00-07 2022-09-03 07:00:00-07 0
2022-09-03 15:00:00-07 2022-09-04 07:00:00-07 0
2022-09-04 15:00:00-07 2022-09-05 07:00:00-07 0
2022-09-05 15:00:00-07 2022-09-06 07:00:00-07 0
2022-09-06 15:00:00-07 2022-09-07 07:00:00-07 0
2022-09-07 15:00:00-07 2022-09-08 07:00:00-07 0
2022-09-08 15:00:00-07 2022-09-09 07:00:00-07 0
2022-09-09 15:00:00-07 2022-09-10 07:00:00-07 0
2022-09-10 15:00:00-07 2022-09-11 07:00:00-07 0
2022-09-11 15:00:00-07 2022-09-12 07:00:00-07 0
query I
SELECT date_diff('week', '2015-10-06 04:22:11'::timestamptz, '2016-11-25 23:19:37'::timestamptz);
----
59
# DST diffs should not be negative
statement ok
set timezone='CET';
statement ok
CREATE TABLE issue9673(starttime TIMESTAMPTZ, recordtime TIMESTAMPTZ);
statement ok
INSERT INTO issue9673 VALUES ('2022-10-30 02:17:00+02', '2022-10-30 02:00:21+01');
statement ok
INSERT INTO issue9673 VALUES ('2021-10-31 02:39:00+02', '2021-10-31 02:38:20+01');
query III
SELECT starttime, recordtime, date_diff('minute', starttime, recordtime)
FROM issue9673;
----
2022-10-30 02:17:00+02 2022-10-30 02:00:21+01 43
2021-10-31 02:39:00+02 2021-10-31 02:38:20+01 59
# Promotion cast from DATE
query I
select date_diff('day', '2022-01-04 19:00:00'::timestamptz, '2024-03-01'::date) as c1;
----
787
|
