Movatterモバイル変換

[0]ホーム
URL:

Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly

 Sign up
Appearance settings

Commit47bd24b

Browse files
committed
add distances for time stamp
1 parent5ec8103 commit47bd24b

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
STORAGEtext;
6767

6868

69-
-- timestamp
69+
-- timestamp ops
70+
71+
CREATEFUNCTIONtimestamp_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+
CREATEFUNCTIONtimestamp_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+
CREATEFUNCTIONtimestamp_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
CREATEFUNCTIONrum_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+
Timestampdt1=PG_GETARG_TIMESTAMP(0);
148+
Timestampdt2=PG_GETARG_TIMESTAMP(1);
149+
doublediff;
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+
Timestampdt1=PG_GETARG_TIMESTAMP(0);
173+
Timestampdt2=PG_GETARG_TIMESTAMP(1);
174+
doublediff;
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+
Timestampdt1=PG_GETARG_TIMESTAMP(0);
198+
Timestampdt2=PG_GETARG_TIMESTAMP(1);
199+
doublediff;
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
CREATETABLEtest_timestamp (
43
itimestamp
@@ -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', iFROM test_timestampORDER BY1,2ASC;
16+
SELECT i<-|'2004-10-26 06:24:08', iFROM test_timestampORDER BY1,2ASC;
17+
SELECT i |->'2004-10-26 06:24:08', iFROM test_timestampORDER BY1,2ASC;
18+
1619
CREATEINDEXidx_timestampON test_timestamp USING rum (i);
1720

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

0 commit comments

Comments
 (0)
[8]ページ先頭
©2009-2025 Movatter.jp