Skip to content

Commit 47bd24b

Browse files
feodorfeodor
committed
add distances for time stamp
1 parent 5ec8103 commit 47bd24b

File tree

4 files changed

+205
-3
lines changed

4 files changed

+205
-3
lines changed

‎expected/timestamp.out

Lines changed: 82 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -1,4 +1,3 @@
1-
set enable_seqscan=off;
21
CREATE TABLE test_timestamp (
32
i timestamp
43
);
@@ -10,7 +9,51 @@ INSERT INTO test_timestamp VALUES
109
( '2004-10-26 09:55:08' ),
1110
( '2004-10-26 10:55:08' )
1211
;
12+
SELECT i <-> '2004-10-26 06:24:08', i FROM test_timestamp ORDER BY 1, 2 ASC;
13+
?column? | i
14+
----------+--------------------------
15+
1740 | Tue Oct 26 05:55:08 2004
16+
5340 | Tue Oct 26 04:55:08 2004
17+
8940 | Tue Oct 26 03:55:08 2004
18+
9060 | Tue Oct 26 08:55:08 2004
19+
12660 | Tue Oct 26 09:55:08 2004
20+
16260 | Tue Oct 26 10:55:08 2004
21+
(6 rows)
22+
23+
SELECT i <-| '2004-10-26 06:24:08', i FROM test_timestamp ORDER BY 1, 2 ASC;
24+
?column? | i
25+
----------+--------------------------
26+
1740 | Tue Oct 26 05:55:08 2004
27+
5340 | Tue Oct 26 04:55:08 2004
28+
8940 | Tue Oct 26 03:55:08 2004
29+
Infinity | Tue Oct 26 08:55:08 2004
30+
Infinity | Tue Oct 26 09:55:08 2004
31+
Infinity | Tue Oct 26 10:55:08 2004
32+
(6 rows)
33+
34+
SELECT i |-> '2004-10-26 06:24:08', i FROM test_timestamp ORDER BY 1, 2 ASC;
35+
?column? | i
36+
----------+--------------------------
37+
9060 | Tue Oct 26 08:55:08 2004
38+
12660 | Tue Oct 26 09:55:08 2004
39+
16260 | Tue Oct 26 10:55:08 2004
40+
Infinity | Tue Oct 26 03:55:08 2004
41+
Infinity | Tue Oct 26 04:55:08 2004
42+
Infinity | Tue Oct 26 05:55:08 2004
43+
(6 rows)
44+
1345
CREATE INDEX idx_timestamp ON test_timestamp USING rum (i);
46+
set enable_seqscan=off;
47+
explain (costs off)
48+
SELECT * FROM test_timestamp WHERE i<'2004-10-26 08:55:08'::timestamp ORDER BY i;
49+
QUERY PLAN
50+
-----------------------------------------------------------------------------------
51+
Sort
52+
Sort Key: i
53+
-> Index Scan using idx_timestamp on test_timestamp
54+
Index Cond: (i < 'Tue Oct 26 08:55:08 2004'::timestamp without time zone)
55+
(4 rows)
56+
1457
SELECT * FROM test_timestamp WHERE i<'2004-10-26 08:55:08'::timestamp ORDER BY i;
1558
i
1659
--------------------------
@@ -19,6 +62,16 @@ SELECT * FROM test_timestamp WHERE i<'2004-10-26 08:55:08'::timestamp ORDER BY i
1962
Tue Oct 26 05:55:08 2004
2063
(3 rows)
2164

65+
explain (costs off)
66+
SELECT * FROM test_timestamp WHERE i<='2004-10-26 08:55:08'::timestamp ORDER BY i;
67+
QUERY PLAN
68+
------------------------------------------------------------------------------------
69+
Sort
70+
Sort Key: i
71+
-> Index Scan using idx_timestamp on test_timestamp
72+
Index Cond: (i <= 'Tue Oct 26 08:55:08 2004'::timestamp without time zone)
73+
(4 rows)
74+
2275
SELECT * FROM test_timestamp WHERE i<='2004-10-26 08:55:08'::timestamp ORDER BY i;
2376
i
2477
--------------------------
@@ -28,12 +81,30 @@ SELECT * FROM test_timestamp WHERE i<='2004-10-26 08:55:08'::timestamp ORDER BY
2881
Tue Oct 26 08:55:08 2004
2982
(4 rows)
3083

84+
explain (costs off)
85+
SELECT * FROM test_timestamp WHERE i='2004-10-26 08:55:08'::timestamp ORDER BY i;
86+
QUERY PLAN
87+
-----------------------------------------------------------------------------
88+
Index Scan using idx_timestamp on test_timestamp
89+
Index Cond: (i = 'Tue Oct 26 08:55:08 2004'::timestamp without time zone)
90+
(2 rows)
91+
3192
SELECT * FROM test_timestamp WHERE i='2004-10-26 08:55:08'::timestamp ORDER BY i;
3293
i
3394
--------------------------
3495
Tue Oct 26 08:55:08 2004
3596
(1 row)
3697

98+
explain (costs off)
99+
SELECT * FROM test_timestamp WHERE i>='2004-10-26 08:55:08'::timestamp ORDER BY i;
100+
QUERY PLAN
101+
------------------------------------------------------------------------------------
102+
Sort
103+
Sort Key: i
104+
-> Index Scan using idx_timestamp on test_timestamp
105+
Index Cond: (i >= 'Tue Oct 26 08:55:08 2004'::timestamp without time zone)
106+
(4 rows)
107+
37108
SELECT * FROM test_timestamp WHERE i>='2004-10-26 08:55:08'::timestamp ORDER BY i;
38109
i
39110
--------------------------
@@ -42,6 +113,16 @@ SELECT * FROM test_timestamp WHERE i>='2004-10-26 08:55:08'::timestamp ORDER BY
42113
Tue Oct 26 10:55:08 2004
43114
(3 rows)
44115

116+
explain (costs off)
117+
SELECT * FROM test_timestamp WHERE i>'2004-10-26 08:55:08'::timestamp ORDER BY i;
118+
QUERY PLAN
119+
-----------------------------------------------------------------------------------
120+
Sort
121+
Sort Key: i
122+
-> Index Scan using idx_timestamp on test_timestamp
123+
Index Cond: (i > 'Tue Oct 26 08:55:08 2004'::timestamp without time zone)
124+
(4 rows)
125+
45126
SELECT * FROM test_timestamp WHERE i>'2004-10-26 08:55:08'::timestamp ORDER BY i;
46127
i
47128
--------------------------

‎rum--1.0.sql

Lines changed: 40 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -66,7 +66,46 @@ AS
6666
STORAGE text;
6767

6868

69-
-- timestamp
69+
-- timestamp ops
70+
71+
CREATE FUNCTION timestamp_distance(timestamp, timestamp)
72+
RETURNS float8
73+
AS 'MODULE_PATHNAME'
74+
LANGUAGE C IMMUTABLE STRICT;
75+
76+
CREATE OPERATOR <-> (
77+
PROCEDURE = timestamp_distance,
78+
LEFTARG = timestamp,
79+
RIGHTARG = timestamp,
80+
COMMUTATOR = <->
81+
);
82+
83+
CREATE FUNCTION timestamp_left_distance(timestamp, timestamp)
84+
RETURNS float8
85+
AS 'MODULE_PATHNAME'
86+
LANGUAGE C IMMUTABLE STRICT;
87+
88+
CREATE OPERATOR <-| (
89+
PROCEDURE = timestamp_left_distance,
90+
LEFTARG = timestamp,
91+
RIGHTARG = timestamp,
92+
COMMUTATOR = |->
93+
);
94+
95+
CREATE FUNCTION timestamp_right_distance(timestamp, timestamp)
96+
RETURNS float8
97+
AS 'MODULE_PATHNAME'
98+
LANGUAGE C IMMUTABLE STRICT;
99+
100+
CREATE OPERATOR |-> (
101+
PROCEDURE = timestamp_right_distance,
102+
LEFTARG = timestamp,
103+
RIGHTARG = timestamp,
104+
COMMUTATOR = <-|
105+
);
106+
107+
108+
-- timestamp operator class
70109

71110
CREATE FUNCTION rum_timestamp_extract_value(timestamp,internal,internal,internal,internal)
72111
RETURNS internal

‎rum_timestamp.c

Lines changed: 77 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -139,3 +139,80 @@ rum_timestamp_consistent(PG_FUNCTION_ARGS)
139139
*recheck = false;
140140
PG_RETURN_BOOL(true);
141141
}
142+
143+
PG_FUNCTION_INFO_V1(timestamp_distance);
144+
Datum
145+
timestamp_distance(PG_FUNCTION_ARGS)
146+
{
147+
Timestamp dt1 = PG_GETARG_TIMESTAMP(0);
148+
Timestamp dt2 = PG_GETARG_TIMESTAMP(1);
149+
double diff;
150+
151+
if (TIMESTAMP_NOT_FINITE(dt1) || TIMESTAMP_NOT_FINITE(dt2))
152+
{
153+
if (TIMESTAMP_NOT_FINITE(dt1) && TIMESTAMP_NOT_FINITE(dt2))
154+
diff = 0;
155+
else
156+
diff = get_float8_infinity();
157+
}
158+
else
159+
{
160+
/* see timestamp_mi */
161+
diff = (dt1 > dt2) ? dt1 - dt2 : dt2 - dt1;
162+
diff /= 1e6;
163+
}
164+
165+
PG_RETURN_FLOAT8(diff);
166+
}
167+
168+
PG_FUNCTION_INFO_V1(timestamp_left_distance);
169+
Datum
170+
timestamp_left_distance(PG_FUNCTION_ARGS)
171+
{
172+
Timestamp dt1 = PG_GETARG_TIMESTAMP(0);
173+
Timestamp dt2 = PG_GETARG_TIMESTAMP(1);
174+
double diff;
175+
176+
if (TIMESTAMP_NOT_FINITE(dt1) || TIMESTAMP_NOT_FINITE(dt2))
177+
{
178+
if (TIMESTAMP_NOT_FINITE(dt1) && TIMESTAMP_NOT_FINITE(dt2))
179+
diff = 0;
180+
else
181+
diff = get_float8_infinity();
182+
}
183+
else
184+
{
185+
/* see timestamp_mi */
186+
diff = (dt1 > dt2) ? get_float8_infinity() : dt2 - dt1;
187+
diff /= 1e6;
188+
}
189+
190+
PG_RETURN_FLOAT8(diff);
191+
}
192+
193+
PG_FUNCTION_INFO_V1(timestamp_right_distance);
194+
Datum
195+
timestamp_right_distance(PG_FUNCTION_ARGS)
196+
{
197+
Timestamp dt1 = PG_GETARG_TIMESTAMP(0);
198+
Timestamp dt2 = PG_GETARG_TIMESTAMP(1);
199+
double diff;
200+
201+
if (TIMESTAMP_NOT_FINITE(dt1) || TIMESTAMP_NOT_FINITE(dt2))
202+
{
203+
if (TIMESTAMP_NOT_FINITE(dt1) && TIMESTAMP_NOT_FINITE(dt2))
204+
diff = 0;
205+
else
206+
diff = get_float8_infinity();
207+
}
208+
else
209+
{
210+
/* see timestamp_mi */
211+
diff = (dt1 > dt2) ? dt1 - dt2 : get_float8_infinity();
212+
diff /= 1e6;
213+
}
214+
215+
PG_RETURN_FLOAT8(diff);
216+
}
217+
218+

‎sql/timestamp.sql

Lines changed: 6 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -1,4 +1,3 @@
1-
set enable_seqscan=off;
21

32
CREATE TABLE test_timestamp (
43
i timestamp
@@ -13,8 +12,14 @@ INSERT INTO test_timestamp VALUES
1312
( '2004-10-26 10:55:08' )
1413
;
1514

15+
SELECT i <-> '2004-10-26 06:24:08', i FROM test_timestamp ORDER BY 1, 2 ASC;
16+
SELECT i <-| '2004-10-26 06:24:08', i FROM test_timestamp ORDER BY 1, 2 ASC;
17+
SELECT i |-> '2004-10-26 06:24:08', i FROM test_timestamp ORDER BY 1, 2 ASC;
18+
1619
CREATE INDEX idx_timestamp ON test_timestamp USING rum (i);
1720

21+
set enable_seqscan=off;
22+
1823
explain (costs off)
1924
SELECT * FROM test_timestamp WHERE i<'2004-10-26 08:55:08'::timestamp ORDER BY i;
2025
SELECT * FROM test_timestamp WHERE i<'2004-10-26 08:55:08'::timestamp ORDER BY i;

0 commit comments

Comments
 (0)