CODEWITHSUNDEEP
javasqlhibernatepostgresql
membersound
membersound

Reputation: 86747

How to query database objects within a date period?

I'd like to return all objects from the DB, where both Date fields of the object have to be within a specific time period.

Can I do better than writing a query with BETWEEN for both date fields?

@Entity
public class Book {
    Date from;
    Date to;
}

//the period to search for
Date fromDate;
Date toDate;

//return all Book objects having from+to paramter laying within the period
b.from BETWEEN :fromDate AND :toDate AND b.to BETWEEN :fromDate AND :toDate

Upvotes: 1

Views: 309

Answers (4)

pstef
pstef

Reputation: 91

I'm assuming you really mean strict containment, and not overlapping of two periods of time (for that you have the OVERLAPS operator).

Let's generate test data first:

CREATE UNLOGGED TABLE books ("from", "to") AS
SELECT g.date::date, (g.date + random() * 100 * INTERVAL '1 day')::date
FROM generate_series('1980-01-01', '2016-12-31', INTERVAL '1 minute') g ("date");
SELECT 19460161
Time: 24783.529 ms

Now I see three choices, but first let's create the three indexes I'll be using:

CREATE INDEX point_gist
ON books
USING gist (point(EXTRACT(EPOCH FROM "from"), EXTRACT(EPOCH FROM "to")));
CREATE INDEX
Time: 242062.079 ms

CREATE INDEX from_to_btree ON books USING btree ("from", "to");
CREATE INDEX
Time: 26107.162 ms

CREATE INDEX daterange_gist ON books USING gist (daterange("from", "to", '[]'));
CREATE INDEX
Time: 791420.184 ms

VACUUM ANALYZE books;
VACUUM
Time: 3000.284 ms

Please don't rely on these timings; each query was executed exactly once as their performance wasn't my primary concern here. YMMV.

1. The usual BETWEEN

EXPLAIN ANALYZE
SELECT *
FROM books
WHERE
    "from" BETWEEN '2000-01-01' AND '2001-01-01'
    AND
    "to" BETWEEN '2000-01-01' AND '2001-01-01';
                                                                     QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------------------
 Index Only Scan using from_to_btree on books  (cost=0.44..14849.78 rows=16578 width=8) (actual time=0.033..79.268 rows=456512 loops=1)
   Index Cond: (("from" >= '2000-01-01'::date) AND ("from" <= '2001-01-01'::date) AND ("to" >= '2000-01-01'::date) AND ("to" <= '2001-01-01'::date))
   Heap Fetches: 0
 Total runtime: 93.792 ms
(4 rows)

2. The 9.2+ type, daterange

EXPLAIN ANALYZE
SELECT *
FROM books
WHERE daterange("from", "to", '[]') <@ daterange(date '2000-01-01', date '2001-01-01', '[]');
                                                               QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------
 Bitmap Heap Scan on books  (cost=16350.89..109027.83 rows=437996 width=8) (actual time=175.644..212.508 rows=456512 loops=1)
   Recheck Cond: (daterange("from", "to", '[]'::text) <@ '[2000-01-01,2001-01-02)'::daterange)
   ->  Bitmap Index Scan on daterange_gist  (cost=0.00..16241.39 rows=437996 width=0) (actual time=175.277..175.277 rows=456512 loops=1)
         Index Cond: (daterange("from", "to", '[]'::text) <@ '[2000-01-01,2001-01-02)'::daterange)
 Total runtime: 226.568 ms
(5 rows)

3. The geometric types and operators abuse

EXPLAIN ANALYZE
SELECT *
FROM books
WHERE
    point(EXTRACT(EPOCH FROM "from"), EXTRACT(EPOCH FROM "to"))
    <@
    box(
        point(EXTRACT(EPOCH FROM date '2000-01-01'), EXTRACT(EPOCH FROM date '2000-01-01')),
        point(EXTRACT(EPOCH FROM date '2001-01-01'), EXTRACT(EPOCH FROM date '2001-01-01'))
    );
                                                                                                    QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Bitmap Heap Scan on books  (cost=959.25..47748.30 rows=19460 width=8) (actual time=97.305..147.931 rows=456512 loops=1)
   Recheck Cond: (point(date_part('epoch'::text, ("from")::timestamp without time zone), date_part('epoch'::text, ("to")::timestamp without time zone)) <@ '(978307200,978307200),(946684800,946684800)'::box)
   ->  Bitmap Index Scan on point_gist  (cost=0.00..954.38 rows=19460 width=0) (actual time=96.926..96.926 rows=456512 loops=1)
         Index Cond: (point(date_part('epoch'::text, ("from")::timestamp without time zone), date_part('epoch'::text, ("to")::timestamp without time zone)) <@ '(978307200,978307200),(946684800,946684800)'::box)
 Total runtime: 161.947 ms
(5 rows)

This one requires a bit of explanation. So you create a point by prividing the (x,y) coordinates, while a box is created by providing two points (opposite corners): ((x1,y1),(x2,y2)). You can see that the requirement that the point is contained by the box (<@) means that x from the point has to lie between x1 and x2, inclusive and y from the point has to lie between y1 and y2, inclusive.

Upvotes: 1

Leon
Leon

Reputation: 38

This is a preferred alternative, but less succint. BETWEEN is a 'closed interval and can be a problem with dates' as mentioned in this thread.

 SELECT from, to 
 FROM Books b
 WHERE 
 (b.from >= :fromDate AND b.from =< :toDate)
 AND
 (b.to >= :fromDate AND b.to <= :toDate);

Upvotes: 0

Jens Schauder
Jens Schauder

Reputation: 81907

Can I do better than writing a query with BETWEEN for both date fields?

No, that is exactly the way to do it.

Upvotes: 0

Petar Butkovic
Petar Butkovic

Reputation: 1900

I don't get it what you actually need, but if I understood correctly, you only need to check date boundaries.

 select * from books where b.from >= :fromDate AND b.to <= :toDate

You can assume that fromDate is smaller than toDate.

Upvotes: 1

Related Questions