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.
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).
Returns the interval value next(p) - first(p).
Returns the lowest timestamptz value that is a member of the period p.
Returns the highest timestamptz value that is a member of the period p.
Returns the timestamptz value just prior to first(p).
Returns the next timestamptz value just after last(p).
Returns true if the timestamptz value ts is included in the period p, false otherwise.
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.
Returns true if the timestamptz value ts is included in the period p, false otherwise.
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.
Returns true if next(p1) == first(p2) OR next(p2) == first(p1), false otherwise. In other words, the periods are "touching", but not overlapping.
Returns true if there is at least one timestamptz value that is a member of both periods p1 and p2.
Returns true if all timestamptz values in period p1 are less than or equal to next(p2), false otherwise.
Returns true if all timestamptz values in period p1 are greater than or equal to first(p2), false otherwise.
Returns true if there are no timestamptz values contained in the period p, false otherwise.
Returns true if period p1 is exactly the same as the period p2, false otherwise.
Returns false if period p1 is exactly the same as the period p2, true otherwise.
Returns true if all timestamptz values in the period p1 are less than all timestamptz values in the period p2, false otherwise.
Returns true if all timestamptz values in the period p1 are greater than all timestamptz values in the period p2, false otherwise.
Returns a period consisting of the single timestamptz value ts.
Returns a period from ts1 (inclusive) to ts2 (exclusive).
Returns a period containing no timestamptz values.
Returns a period consisting of all timestamptz values that are a member of both p1 and 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.
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.
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)