blob: 0dada0b662cbb0d54f692a78eb965fd0f412eaf7 (
plain)
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
|
set enable_seqscan=off;
CREATE TABLE test_timestamptz (
i timestamptz
);
INSERT INTO test_timestamptz VALUES
( '2004-10-26 03:55:08' ),
( '2004-10-26 04:55:08' ),
( '2004-10-26 05:55:08' ),
( '2004-10-26 08:55:08' ),
( '2004-10-27 09:55:08' ),
( '2004-10-27 10:55:08' )
;
CREATE INDEX idx_timestamptz ON test_timestamptz USING gin (i);
SELECT * FROM test_timestamptz WHERE i<'2004-10-26 08:55:08'::timestamptz ORDER BY i;
i
------------------------------
Tue Oct 26 03:55:08 2004 PDT
Tue Oct 26 04:55:08 2004 PDT
Tue Oct 26 05:55:08 2004 PDT
(3 rows)
SELECT * FROM test_timestamptz WHERE i<='2004-10-26 08:55:08'::timestamptz ORDER BY i;
i
------------------------------
Tue Oct 26 03:55:08 2004 PDT
Tue Oct 26 04:55:08 2004 PDT
Tue Oct 26 05:55:08 2004 PDT
Tue Oct 26 08:55:08 2004 PDT
(4 rows)
SELECT * FROM test_timestamptz WHERE i='2004-10-26 08:55:08'::timestamptz ORDER BY i;
i
------------------------------
Tue Oct 26 08:55:08 2004 PDT
(1 row)
SELECT * FROM test_timestamptz WHERE i>='2004-10-26 08:55:08'::timestamptz ORDER BY i;
i
------------------------------
Tue Oct 26 08:55:08 2004 PDT
Wed Oct 27 09:55:08 2004 PDT
Wed Oct 27 10:55:08 2004 PDT
(3 rows)
SELECT * FROM test_timestamptz WHERE i>'2004-10-26 08:55:08'::timestamptz ORDER BY i;
i
------------------------------
Wed Oct 27 09:55:08 2004 PDT
Wed Oct 27 10:55:08 2004 PDT
(2 rows)
explain (costs off)
SELECT * FROM test_timestamptz WHERE i<'2004-10-27'::date ORDER BY i;
QUERY PLAN
----------------------------------------------------
Sort
Sort Key: i
-> Bitmap Heap Scan on test_timestamptz
Recheck Cond: (i < '10-27-2004'::date)
-> Bitmap Index Scan on idx_timestamptz
Index Cond: (i < '10-27-2004'::date)
(6 rows)
SELECT * FROM test_timestamptz WHERE i<'2004-10-27'::date ORDER BY i;
i
------------------------------
Tue Oct 26 03:55:08 2004 PDT
Tue Oct 26 04:55:08 2004 PDT
Tue Oct 26 05:55:08 2004 PDT
Tue Oct 26 08:55:08 2004 PDT
(4 rows)
SELECT * FROM test_timestamptz WHERE i<='2004-10-27'::date ORDER BY i;
i
------------------------------
Tue Oct 26 03:55:08 2004 PDT
Tue Oct 26 04:55:08 2004 PDT
Tue Oct 26 05:55:08 2004 PDT
Tue Oct 26 08:55:08 2004 PDT
(4 rows)
SELECT * FROM test_timestamptz WHERE i='2004-10-27'::date ORDER BY i;
i
---
(0 rows)
SELECT * FROM test_timestamptz WHERE i>='2004-10-27'::date ORDER BY i;
i
------------------------------
Wed Oct 27 09:55:08 2004 PDT
Wed Oct 27 10:55:08 2004 PDT
(2 rows)
SELECT * FROM test_timestamptz WHERE i>'2004-10-27'::date ORDER BY i;
i
------------------------------
Wed Oct 27 09:55:08 2004 PDT
Wed Oct 27 10:55:08 2004 PDT
(2 rows)
explain (costs off)
SELECT * FROM test_timestamptz WHERE i<'2004-10-26 08:55:08'::timestamp ORDER BY i;
QUERY PLAN
-----------------------------------------------------------------------------------------
Sort
Sort Key: i
-> Bitmap Heap Scan on test_timestamptz
Recheck Cond: (i < 'Tue Oct 26 08:55:08 2004'::timestamp without time zone)
-> Bitmap Index Scan on idx_timestamptz
Index Cond: (i < 'Tue Oct 26 08:55:08 2004'::timestamp without time zone)
(6 rows)
SELECT * FROM test_timestamptz WHERE i<'2004-10-26 08:55:08'::timestamp ORDER BY i;
i
------------------------------
Tue Oct 26 03:55:08 2004 PDT
Tue Oct 26 04:55:08 2004 PDT
Tue Oct 26 05:55:08 2004 PDT
(3 rows)
SELECT * FROM test_timestamptz WHERE i<='2004-10-26 08:55:08'::timestamp ORDER BY i;
i
------------------------------
Tue Oct 26 03:55:08 2004 PDT
Tue Oct 26 04:55:08 2004 PDT
Tue Oct 26 05:55:08 2004 PDT
Tue Oct 26 08:55:08 2004 PDT
(4 rows)
SELECT * FROM test_timestamptz WHERE i='2004-10-26 08:55:08'::timestamp ORDER BY i;
i
------------------------------
Tue Oct 26 08:55:08 2004 PDT
(1 row)
SELECT * FROM test_timestamptz WHERE i>='2004-10-26 08:55:08'::timestamp ORDER BY i;
i
------------------------------
Tue Oct 26 08:55:08 2004 PDT
Wed Oct 27 09:55:08 2004 PDT
Wed Oct 27 10:55:08 2004 PDT
(3 rows)
SELECT * FROM test_timestamptz WHERE i>'2004-10-26 08:55:08'::timestamp ORDER BY i;
i
------------------------------
Wed Oct 27 09:55:08 2004 PDT
Wed Oct 27 10:55:08 2004 PDT
(2 rows)
|
