PostgreSQL: Query for tstzrange that contains last instant of a quarter

Issue

Given a PostgreSQL table that is supposed to contain rows with continuous, non-overlapping valid_range ranges such as:

CREATE TABLE tracking (
    id INT PRIMARY KEY,
    valid_range TSTZRANGE NOT NULL,
    EXCLUDE USING gist (valid_range WITH &&)
);

INSERT INTO tracking (id, valid_range) VALUES
    (1, '["2017-03-01 13:00", "2017-03-31 14:00")'),
    (2, '["2017-03-31 14:00", "2017-04-01 00:00")'),
    (3, '["2017-04-01 00:00",)');

That creates a table that contains:

 id |                     valid_range                     
----+-----------------------------------------------------
  1 | ["2017-03-01 13:00:00-07","2017-03-31 14:00:00-06")
  2 | ["2017-03-31 14:00:00-06","2017-04-01 00:00:00-06")
  3 | ["2017-04-01 00:00:00-06",)

I need to query for the row that was the valid row at the end of a given quarter, where I’m defining “at the end of a quarter” as “the instant in time right before the date changed to be the first day of the new quarter.” In the above example, querying for the end of Q1 2017 (Q1 ends at the end of 2017-03-31, and Q2 begins 2017-04-01), I want my query to return only the row with ID 2.

What is the best way to express this condition in PostgreSQL?

SELECT * FROM tracking WHERE valid_range @> TIMESTAMPTZ '2017-03-31' is wrong because it returns the row that contains midnight on 2017-03-31, which is ID 1.

valid_range @> TIMESTAMPTZ '2017-04-01' is also wrong because it skips over the row that was actually valid right at the end of the quarter (ID 2) and instead returns the row with ID 3, which is the row that starts the new quarter.

I’m trying to avoid using something like ...ORDER BY valid_range DESC LIMIT 1 in the query.

Note that the end of the ranges must always be exclusive, I cannot change that.

Solution

The best answer I’ve come up with so far is

SELECT
    *
FROM
    tracking
WHERE
    lower(valid_range) < '2017-04-01'
    AND upper(valid_range) >= '2017-04-01'

This seems like the moral equivalent of saying “I want to reverse the inclusivity/exclusivity of the bounds on this TSTZRANGE column for this query” which makes me think I’m missing a better way of doing this. I wouldn’t be surprised if it also negates the benefits of typical indexes on a range column.

Answered By – Dale

Answer Checked By – Candace Johnson (AngularFixing Volunteer)

Leave a Reply

Your email address will not be published.