1-- timestamptz check
2CREATE TABLE timestamptztmp (a timestamptz);
3\copy timestamptztmp from 'data/timestamptz.data'
4SET enable_seqscan=on;
5SELECT count(*) FROM timestamptztmp WHERE a <  '2018-12-18 10:59:54 GMT+3';
6 count
7-------
8   391
9(1 row)
10
11SELECT count(*) FROM timestamptztmp WHERE a <= '2018-12-18 10:59:54 GMT+3';
12 count
13-------
14   392
15(1 row)
16
17SELECT count(*) FROM timestamptztmp WHERE a  = '2018-12-18 10:59:54 GMT+3';
18 count
19-------
20     1
21(1 row)
22
23SELECT count(*) FROM timestamptztmp WHERE a >= '2018-12-18 10:59:54 GMT+3';
24 count
25-------
26   158
27(1 row)
28
29SELECT count(*) FROM timestamptztmp WHERE a >  '2018-12-18 10:59:54 GMT+3';
30 count
31-------
32   157
33(1 row)
34
35SELECT count(*) FROM timestamptztmp WHERE a <  '2018-12-18 10:59:54 GMT+2';
36 count
37-------
38   391
39(1 row)
40
41SELECT count(*) FROM timestamptztmp WHERE a <= '2018-12-18 10:59:54 GMT+2';
42 count
43-------
44   391
45(1 row)
46
47SELECT count(*) FROM timestamptztmp WHERE a  = '2018-12-18 10:59:54 GMT+2';
48 count
49-------
50     0
51(1 row)
52
53SELECT count(*) FROM timestamptztmp WHERE a >= '2018-12-18 10:59:54 GMT+2';
54 count
55-------
56   158
57(1 row)
58
59SELECT count(*) FROM timestamptztmp WHERE a >  '2018-12-18 10:59:54 GMT+2';
60 count
61-------
62   158
63(1 row)
64
65SELECT count(*) FROM timestamptztmp WHERE a <  '2018-12-18 10:59:54 GMT+4';
66 count
67-------
68   392
69(1 row)
70
71SELECT count(*) FROM timestamptztmp WHERE a <= '2018-12-18 10:59:54 GMT+4';
72 count
73-------
74   392
75(1 row)
76
77SELECT count(*) FROM timestamptztmp WHERE a  = '2018-12-18 10:59:54 GMT+4';
78 count
79-------
80     0
81(1 row)
82
83SELECT count(*) FROM timestamptztmp WHERE a >= '2018-12-18 10:59:54 GMT+4';
84 count
85-------
86   157
87(1 row)
88
89SELECT count(*) FROM timestamptztmp WHERE a >  '2018-12-18 10:59:54 GMT+4';
90 count
91-------
92   157
93(1 row)
94
95SELECT a, a <-> '2018-12-18 10:59:54 GMT+2' FROM timestamptztmp ORDER BY a <-> '2018-12-18 10:59:54 GMT+2' LIMIT 3;
96              a               |             ?column?
97------------------------------+-----------------------------------
98 Tue Dec 18 05:59:54 2018 PST | @ 1 hour
99 Thu Jan 10 03:01:34 2019 PST | @ 22 days 22 hours 1 min 40 secs
100 Thu Jan 24 12:28:12 2019 PST | @ 37 days 7 hours 28 mins 18 secs
101(3 rows)
102
103CREATE INDEX timestamptzidx ON timestamptztmp USING gist ( a );
104SET enable_seqscan=off;
105SELECT count(*) FROM timestamptztmp WHERE a <  '2018-12-18 10:59:54 GMT+3'::timestamptz;
106 count
107-------
108   391
109(1 row)
110
111SELECT count(*) FROM timestamptztmp WHERE a <= '2018-12-18 10:59:54 GMT+3'::timestamptz;
112 count
113-------
114   392
115(1 row)
116
117SELECT count(*) FROM timestamptztmp WHERE a  = '2018-12-18 10:59:54 GMT+3'::timestamptz;
118 count
119-------
120     1
121(1 row)
122
123SELECT count(*) FROM timestamptztmp WHERE a >= '2018-12-18 10:59:54 GMT+3'::timestamptz;
124 count
125-------
126   158
127(1 row)
128
129SELECT count(*) FROM timestamptztmp WHERE a >  '2018-12-18 10:59:54 GMT+3'::timestamptz;
130 count
131-------
132   157
133(1 row)
134
135SELECT count(*) FROM timestamptztmp WHERE a <  '2018-12-18 10:59:54 GMT+2'::timestamptz;
136 count
137-------
138   391
139(1 row)
140
141SELECT count(*) FROM timestamptztmp WHERE a <= '2018-12-18 10:59:54 GMT+2'::timestamptz;
142 count
143-------
144   391
145(1 row)
146
147SELECT count(*) FROM timestamptztmp WHERE a  = '2018-12-18 10:59:54 GMT+2'::timestamptz;
148 count
149-------
150     0
151(1 row)
152
153SELECT count(*) FROM timestamptztmp WHERE a >= '2018-12-18 10:59:54 GMT+2'::timestamptz;
154 count
155-------
156   158
157(1 row)
158
159SELECT count(*) FROM timestamptztmp WHERE a >  '2018-12-18 10:59:54 GMT+2'::timestamptz;
160 count
161-------
162   158
163(1 row)
164
165SELECT count(*) FROM timestamptztmp WHERE a <  '2018-12-18 10:59:54 GMT+4'::timestamptz;
166 count
167-------
168   392
169(1 row)
170
171SELECT count(*) FROM timestamptztmp WHERE a <= '2018-12-18 10:59:54 GMT+4'::timestamptz;
172 count
173-------
174   392
175(1 row)
176
177SELECT count(*) FROM timestamptztmp WHERE a  = '2018-12-18 10:59:54 GMT+4'::timestamptz;
178 count
179-------
180     0
181(1 row)
182
183SELECT count(*) FROM timestamptztmp WHERE a >= '2018-12-18 10:59:54 GMT+4'::timestamptz;
184 count
185-------
186   157
187(1 row)
188
189SELECT count(*) FROM timestamptztmp WHERE a >  '2018-12-18 10:59:54 GMT+4'::timestamptz;
190 count
191-------
192   157
193(1 row)
194
195EXPLAIN (COSTS OFF)
196SELECT a, a <-> '2018-12-18 10:59:54 GMT+2' FROM timestamptztmp ORDER BY a <-> '2018-12-18 10:59:54 GMT+2' LIMIT 3;
197                                     QUERY PLAN
198------------------------------------------------------------------------------------
199 Limit
200   ->  Index Only Scan using timestamptzidx on timestamptztmp
201         Order By: (a <-> 'Tue Dec 18 04:59:54 2018 PST'::timestamp with time zone)
202(3 rows)
203
204SELECT a, a <-> '2018-12-18 10:59:54 GMT+2' FROM timestamptztmp ORDER BY a <-> '2018-12-18 10:59:54 GMT+2' LIMIT 3;
205              a               |             ?column?
206------------------------------+-----------------------------------
207 Tue Dec 18 05:59:54 2018 PST | @ 1 hour
208 Thu Jan 10 03:01:34 2019 PST | @ 22 days 22 hours 1 min 40 secs
209 Thu Jan 24 12:28:12 2019 PST | @ 37 days 7 hours 28 mins 18 secs
210(3 rows)
211
212