CODEWITHSUNDEEP
postgresql
imderek
imderek

Reputation: 1316

PG: Fetching "day" records using start_date and end_date columns

Each record has a "date range", represented by start_date and end_date columns. When the start_date and end_date are the same date value, that record spans one day.

+-----------+------------+------------+
| foo_count | start_date |  end_date  |
+-----------+------------+------------+
|        25 | 2014-02-11 | 2014-02-17 | <- spans one week
|        30 | 2014-02-20 | 2014-02-20 | <- spans one day
|        15 | 2014-02-21 | 2014-02-21 | <- spans one day
|        45 | 2014-02-22 | 2014-02-22 | <- spans one day
+-----------+------------+------------+

Is there a way to fetch only records that represent a single day (i.e. the start_date and end_date match)?

Upvotes: 0

Views: 59

Answers (1)

harmic
harmic

Reputation: 30597

To match records that span a single day:

SELECT * FROM footable WHERE start_date = end_date;

To match records that span a week:

SELECT * FROM footable WHERE  end_date - start_date = 6;

This page in the manual shows date and time operators. Subtracting two dates gives the number of days between.

Btw I would normally arrange the boundaries of such a range so that only one end was inclusive, ie. Start <= date < end, so that subtracting end - start gives the actual number of days.

Upvotes: 2

Related Questions