1-- time check
2CREATE TABLE timetmp (a time);
3\copy timetmp from 'data/time.data'
4SET enable_seqscan=on;
5SELECT count(*) FROM timetmp WHERE a <  '10:57:11';
6 count
7-------
8   251
9(1 row)
10
11SELECT count(*) FROM timetmp WHERE a <= '10:57:11';
12 count
13-------
14   252
15(1 row)
16
17SELECT count(*) FROM timetmp WHERE a  = '10:57:11';
18 count
19-------
20     1
21(1 row)
22
23SELECT count(*) FROM timetmp WHERE a >= '10:57:11';
24 count
25-------
26   293
27(1 row)
28
29SELECT count(*) FROM timetmp WHERE a >  '10:57:11';
30 count
31-------
32   292
33(1 row)
34
35SELECT a, a <-> '10:57:11' FROM timetmp ORDER BY a <-> '10:57:11' LIMIT 3;
36    a     |    ?column?
37----------+-----------------
38 10:57:11 | @ 0
39 10:57:10 | @ 1 sec
40 10:55:32 | @ 1 min 39 secs
41(3 rows)
42
43CREATE INDEX timeidx ON timetmp USING gist ( a );
44SET enable_seqscan=off;
45SELECT count(*) FROM timetmp WHERE a <  '10:57:11'::time;
46 count
47-------
48   251
49(1 row)
50
51SELECT count(*) FROM timetmp WHERE a <= '10:57:11'::time;
52 count
53-------
54   252
55(1 row)
56
57SELECT count(*) FROM timetmp WHERE a  = '10:57:11'::time;
58 count
59-------
60     1
61(1 row)
62
63SELECT count(*) FROM timetmp WHERE a >= '10:57:11'::time;
64 count
65-------
66   293
67(1 row)
68
69SELECT count(*) FROM timetmp WHERE a >  '10:57:11'::time;
70 count
71-------
72   292
73(1 row)
74
75EXPLAIN (COSTS OFF)
76SELECT a, a <-> '10:57:11' FROM timetmp ORDER BY a <-> '10:57:11' LIMIT 3;
77                          QUERY PLAN
78--------------------------------------------------------------
79 Limit
80   ->  Index Only Scan using timeidx on timetmp
81         Order By: (a <-> '10:57:11'::time without time zone)
82(3 rows)
83
84SELECT a, a <-> '10:57:11' FROM timetmp ORDER BY a <-> '10:57:11' LIMIT 3;
85    a     |    ?column?
86----------+-----------------
87 10:57:11 | @ 0
88 10:57:10 | @ 1 sec
89 10:55:32 | @ 1 min 39 secs
90(3 rows)
91
92