PostgreSQL querying range plus data on either side
I had a friend wanting to query data to display a graph. Though they also had the requirement that the result must include the element before and after the range specified.
As an example, if we have the following five dates:
- 2020-01-01
- 2020-01-02
- 2020-01-03
- 2020-01-04
- 2020-01-05
And they gave the date range 2020-01-02 to 2020-01-03 it should provide the results from 2020-01-02 to 2020-01-04.
The natural response to solving this is to get the max and min date needed as two separate queries the run a third query to get the final result.
Though as we all know, you should try and send as few queries to your database as possible as you lose time every time you send a query. In this trivial example, it won’t cost much, but once you start hitting significant, more complicated cases, especially if looping is involved, the cost can go up fast.
So here is how to solve it as a single query you send to the database. First, let us create our demo table that we are going to using to demonstrate this.
CREATE TABLE demo (
id BIGSERIAL PRIMARY KEY NOT NULL,
created_at TIMESTAMPTZ DEFAULT current_timestamp NOT NULL,
random_number BIGINT NOT NULL
);
Now to insert some data:
INSERT
INTO
demo
(created_at, random_number)
VALUES
('2020-01-01',1),
('2020-01-02',2),
('2020-01-03',3),
('2020-01-04',4),
('2020-01-05',5);
Next, we want to write the queries to get the start and end dates:
SELECT
max(created_at) AS start_date
FROM
demo
WHERE
created_at <= :min_date
SELECT
min(created_at) AS end_date
FROM
demo
WHERE
created_at >= :max_date
I’ve also added a coalesce as this returns the first none null result from a list. That way we now can’t get null when there is no date to either side. For example, someone specifies a min date of 2020-01-01 the start date for that would be null, so we can get around this by saying coalesce(start_date, ‘:min_date’) which would then return :min_date.
The :min_date, in this case, is the date that is the start of the range.
As we are using timestamp rember to use dates in the following format when testing ‘2020-01-02 00:00:00‘ and ‘2020-01-03 00:00:00‘
WITH
min_date AS (
SELECT
max(created_at) AS start_date
FROM
demo
WHERE
created_at < :min_date ), max_date AS ( SELECT min(created_at) AS end_date FROM demo WHERE created_at > :max_date
)
SELECT
demo.*
FROM
demo
JOIN min_date
ON demo.created_at >= coalesce(min_date.start_date, cast(:min_date AS TIMESTAMPTZ))
JOIN max_date
ON demo.created_at <= coalesce(max_date.end_date, cast(:max_date AS TIMESTAMPTZ))
ORDER BY
demo.created_at
For those in the MySQL side of the world here is how to do it
CREATE TABLE demo (
id BIGINT PRIMARY KEY AUTO_INCREMENT NOT NULL,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL,
random_number BIGINT NOT NULL
);
INSERT INTO
demo
(
created_at,
random_number
)
VALUES
(
'2020-01-01',
1
),
(
'2020-01-02',
2
),
(
'2020-01-03',
3
),
(
'2020-01-04',
4
),
(
'2020-01-05',
5
);
SELECT
demo.*
FROM
demo
JOIN (
SELECT
max(created_at) AS start_date
FROM
demo
WHERE
created_at < :min_date ) min_date ON demo.created_at >= coalesce(min_date.start_date, TIMESTAMP(:min_date))
JOIN (
SELECT
min(created_at) AS end_date
FROM
demo
WHERE
created_at > :max_date
) max_date
ON demo.created_at <= coalesce(max_date.end_date, TIMESTAMP(:max_date))
ORDER BY
demo.created_at