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