CODEWITHSUNDEEP
sqlpostgresqldate
user2403954
user2403954

Reputation: 11

Query to check date range between start and end dates if already exists in database in postgres

I have to allot ticket booking between two date range, ie., from an available start date and end end(which is not allotted previously). For this, I need to check in database if there is already any allotment happened in between these dates

For example, I need to allot ticket booking from 2017/04/20 to 2017/04/25 .Before inserting this record, I need to check if there is any booking previously happened between the start date 2017/04/20 and end date 2017/04/25

I have tried with below query.But it is not giving the correct result

select "id" 
from "table" 
where "from_date" >= '2017-04-20' 
  and "to_date" > '2017-04-20' 
  and "from_date" < '2017-04-25' 
  and "to_date" <= '2017-04-25'

Upvotes: 1

Views: 4888

Answers (4)

Danish Shaikh
Danish Shaikh

Reputation: 474

I was also having the same problem. The below query will check if the given start_date or end_date exists in the db.

$start_date = '2010-01-01';
$end_date = '2017-01-01';

SELECT * FROM `ticket_history` WHERE 
($start_date <= start_date AND $start_date <= end_date AND 
(($end_date >= start_date AND $end_date >= end_date) OR ($end_date >= 
start_date AND $end_date <= end_date))) OR
($start_date >= start_date AND $start_date <= end_date AND 
(($end_date >= start_date AND $end_date >= end_date) OR ($end_date >= 
start_date AND $end_date <= end_date)))

Upvotes: 0

pozs
pozs

Reputation: 36274

Your query does not give the expected result, because you only test for 1 of the 3 (or 4) possible ways of overlapping/intersecting:

existing date periods:  +--------------+      +-----+       +----+   +-----+
                            contains        contained by        touches
testing periods:            +------+       +------------+     +---------+

To test every possible way of overlapping, you can use daterange's overlaps operator: &&

select count(*)
from   booking
where  daterange(from_date, to_date, '[]') && daterange('2017-04-20', '2017-04-25', '[]');

But be warned: only using these kind of manual checking will not prevent inserting overlapping date ranges in high concurrency. There is a (small) window, after this test & before the actual insertion for another, concurrent statement to insert a conflicting row. To also avoid that, you can use exclusion constraints:

alter table booking
  add constraint exclude_overlapping_bookings
  exclude using gist (daterange(from_date, to_date, '[]') with &&);

Note: daterange's overlaps operator (&&) works the same way as the overlaps operator, mentioned by @a_horse_with_no_name. Except:

  • it supports exclusion constraints
  • it supports use of indexes (if you added the exclusion constraint, the index is created automatically for you)
  • you can fine-tune how to include the range's boundaries. The default is [), meaning: the lower bound is inclusive, but the upper bound is exclusive. With overlaps, this is the only supported inclusion.
  • overlaps works with timestamp with time zone values. Your date values will be casted to that, using midnight as time & the current TimeZone setting. This is may or may not what you want.

http://rextester.com/NNWD52481

Upvotes: 3

etsa
etsa

Reputation: 5060

Just to examine another way, another query that doesn't use overlap operator, but only AND et OR: (using data posted by a_horse...). This is the logic:

TO_DATE>= [your start date] AND TO_DATE<= [your end date]
OR
FROM_DATE <= [your end date] AND FROM_DATE>= [your start date]

select *
from bookings
where to_date>=date '2017-04-18' AND to_date<=date '2017-04-19'
OR from_date<= date '2017-04-19' AND from_date>=date '2017-04-18';

select *
from bookings
where to_date>=date '2017-04-20' AND to_date<=date '2017-04-25'
OR from_date<= date '2017-04-25' AND from_date>=date '2017-04-20';

Upvotes: 1

user330315
user330315

Reputation:

Use the overlaps operator:

select count(*) = 0 as allow_booking
from "table"
where (from_date, to_date) overlaps (date '2017-04-20', date '2017-04-25');

That will also take care of bookings that do not completely fall in the range to test.

The query will return true if the booking is allowed, and false if not.

Examples: 

create table bookings 
(
  id serial,
  from_date date, 
  to_date date
);

insert into bookings 
  (from_date, to_date) 
values 
  (date '2017-04-20', date '2017-04-20'),
  (date '2017-04-20', date '2017-04-24'),
  (date '2017-04-26', date '2017-04-29');

Then the following 

select *
from bookings
where (from_date, to_date) overlaps (date '2017-04-18', date '2017-04-19');

will return nothing, so, count(*) = 0 returns true

The following query:

select *
from bookings
where (from_date, to_date) overlaps (date '2017-04-20', date '2017-04-25');

returns: 

id | from_date  | to_date   
---+------------+-----------
 1 | 2017-04-20 | 2017-04-20
 2 | 2017-04-20 | 2017-04-24

So count(*) = 0 returns false

And the query:

select *
from bookings
where (from_date, to_date) overlaps (date '2017-04-27', date '2017-04-28');

will return: 

id | from_date  | to_date   
---+------------+-----------
 3 | 2017-04-26 | 2017-04-29

And for that count(*) = 0 is also false.

Upvotes: 1

Related Questions