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:
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