Contents
- PERIOD Data Type Reference
- In/Out Functions
- INTERVAL Functions
- TIMESTAMPTZ Functions
- BOOLEAN Functions
- boolean contains(period p, timestamptz ts)
- boolean contains(period p1, period p2)
- boolean contained_by(timestamptz ts, period p)
- boolean contained_by(period p1, period p2)
- boolean adjacent(period p1, period p2)
- boolean overlaps(period p1, period p2)
- boolean overleft(period p1, period p2)
- boolean overright(period p1, period p2)
- boolean is_empty(period p)
- boolean equals(period p1, period p2)
- boolean nequals(period p1, period p2)
- boolean before(period p1, period p2)
- boolean after(period p1, period p2)
- PERIOD Functions
- Operators
- period = period → equals(period, period)
- period != period → nequals(period, period)
- period - period → minus(period, period)
- period + period → union(period, period)
- period @> period → contains(period, period)
- period @> timestamptz → contains(period, timestamptz)
- period <@ period → contained_by(period, period)
- timestamptz <@ period → contained_by(timestamptz, period)
- period ~ period → contains(period, period)
- period ~ timestamptz → contains(period, timestamptz)
- timestamptz @ period → contained_by(timestamptz, period)
- period @ period → contained_by(period, period)
- period && period → overlaps(period, period)
- period << period → before(period, period)
- period >> period → after(period, period)
- period &< period → overleft(period, period)
- period &> period → overright(period, period)
- GiST Index
PERIOD Data Type Reference
In/Out Functions
period period_in(text t)
Converts a text string representation t into a period type. The text representation should be of one of the following forms:
( timestamptz, timestamptz )
[ timestamptz, timestamptz )
( timestamptz, timestamptz ]
[ timestamptz, timestamptz ]
Where timestamptz is a valid representation of a timestamptz. The choice of brackets represents the inclusiveness of the interval. A square bracket makes that side inclusive, and a peren makes that side exclusive.
text period_out(period p)
Outputs period p in the form:
[ ts1, ts2 )
Where ts1 is the text representation of the timestamptz value first(p) and ts2 is the text representation of the timestamptz value next(p).
INTERVAL Functions
interval length(period p)
Returns the interval value next(p) - first(p).
TIMESTAMPTZ Functions
timestamptz first(period p)
Returns the lowest timestamptz value that is a member of the period p.
timestamptz last(period p)
Returns the highest timestamptz value that is a member of the period p.
timestamptz prior(period p)
Returns the timestamptz value just prior to first(p).
timestamptz next(period p)
Returns the next timestamptz value just after last(p).
BOOLEAN Functions
boolean contains(period p, timestamptz ts)
Returns true if the timestamptz value ts is included in the period p, false otherwise.
boolean contains(period p1, period p2)
Returns true if all of the timestamptz values that are a member of the period p2 are also a member of the period p1, false otherwise.
boolean contained_by(timestamptz ts, period p)
Returns true if the timestamptz value ts is included in the period p, false otherwise.
boolean contained_by(period p1, period p2)
Returns true if all of the timestamptz values that are a member of the period p1 are also a member of the period p2, false otherwise.
boolean adjacent(period p1, period p2)
Returns true if next(p1) == first(p2) OR next(p2) == first(p1), false otherwise. In other words, the periods are "touching", but not overlapping.
boolean overlaps(period p1, period p2)
Returns true if there is at least one timestamptz value that is a member of both periods p1 and p2.
boolean overleft(period p1, period p2)
Returns true if all timestamptz values in period p1 are less than or equal to next(p2), false otherwise.
boolean overright(period p1, period p2)
Returns true if all timestamptz values in period p1 are greater than or equal to first(p2), false otherwise.
boolean is_empty(period p)
Returns true if there are no timestamptz values contained in the period p, false otherwise.
boolean equals(period p1, period p2)
Returns true if period p1 is exactly the same as the period p2, false otherwise.
boolean nequals(period p1, period p2)
Returns false if period p1 is exactly the same as the period p2, true otherwise.
boolean before(period p1, period p2)
Returns true if all timestamptz values in the period p1 are less than all timestamptz values in the period p2, false otherwise.
boolean after(period p1, period p2)
Returns true if all timestamptz values in the period p1 are greater than all timestamptz values in the period p2, false otherwise.
PERIOD Functions
period period(timestamptz ts)
Returns a period consisting of the single timestamptz value ts.
period period(timestamptz ts1, timestamptz ts2)
Returns a period from ts1 (inclusive) to ts2 (exclusive).
period empty_period()
Returns a period containing no timestamptz values.
period period_intersect(period p1, period p2)
Returns a period consisting of all timestamptz values that are a member of both p1 and p2.
period period_union(period p1, period p2)
Returns a period consisting of all timestamptz values that are a member of either p1 or p2. If p1 and p2 do not overlap, and are not adjacent, an exception is raised.
period minus(period p1, period p2)
Return a period consisting of all timestamptz values that are a member of p1 but not a member of p2. If p2 is contained completely inside p1 -- that is, first(p1) < first(p2) AND last(p2) < last(p1) -- an exception is raised.
Operators
period = period → equals(period, period)
period != period → nequals(period, period)
period - period → minus(period, period)
period + period → union(period, period)
period @> period → contains(period, period)
period @> timestamptz → contains(period, timestamptz)
period <@ period → contained_by(period, period)
timestamptz <@ period → contained_by(timestamptz, period)
period ~ period → contains(period, period)
period ~ timestamptz → contains(period, timestamptz)
timestamptz @ period → contained_by(timestamptz, period)
period @ period → contained_by(period, period)
period && period → overlaps(period, period)
period << period → before(period, period)
period >> period → after(period, period)
period &< period → overleft(period, period)
period &> period → overright(period, period)
GiST Index
temporal=> CREATE TABLE test(test_period period);
CREATE TABLE
temporal=> CREATE INDEX test_period_idx ON test USING GiST (test_period);
CREATE INDEX
temporal=> INSERT INTO test (
temporal(> SELECT period(
temporal(> clock_timestamp() + (generate_series * '1 second'::interval),
temporal(> clock_timestamp() + (generate_series * '20 second'::interval)
temporal(> )
temporal(> FROM generate_series(1,1000001)
temporal(> );
INSERT 0 1000001
temporal=> VACUUM ANALYZE TEST;
VACUUM
temporal=> EXPLAIN ANALYZE SELECT count(*) FROM test WHERE test_period @> (now() + (19000000 * '1 second'::interval));
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=2585.05..2585.06 rows=1 width=0) (actual time=24.424..24.425 rows=1 loops=1)
-> Bitmap Heap Scan on test (cost=32.21..2582.55 rows=1000 width=0) (actual time=6.026..15.441 rows=49998 loops=1)
Recheck Cond: (test_period @> (now() + '5277:46:40'::interval))
-> Bitmap Index Scan on test_period_idx (cost=0.00..31.96 rows=1000 width=0) (actual time=5.982..5.982 rows=49998 loops=1)
Index Cond: (test_period @> (now() + '5277:46:40'::interval))
Total runtime: 24.458 ms
(6 rows)