CODEWITHSUNDEEP
phppostgresql
SergkeiM
SergkeiM

Reputation: 4168

Postgresql date range

I have a calendar with events: Calendar always have a start point and end point ( example 29.04.2014 till 09.6.2014)

Events are shown from this date range:

AND DATE(event_from) >= DATE(?) //29.04.2014
AND DATE(event_to) <= DATE(?) //09.6.2014

But if event starts 28.04 and ends 10.6.2014 it want shown in calendar

How can I show this event if it exceeds the range, but also remains in range

Thanks to klin:

All together

AND (daterange(DATE(event_from), DATE(event_to)) && daterange(DATE(?), DATE(?)) OR( DATE(event_from) >= DATE(?) AND DATE(event_to) <= DATE(?) ) )

Upvotes: 1

Views: 1067

Answers (1)

klin
klin

Reputation: 121604

Use the type daterange and overlap operator:

with ranges(event_from, event_to) as (
values 
    ('2016-04-01'::date, '2016-06-01'::date),
    ('2016-01-01', '2016-03-01'),
    ('2016-05-01', '2016-05-15')
)

select *
from ranges
where daterange(event_from, event_to) && daterange('2016-05-01', '2016-05-31');

 event_from |  event_to  
------------+------------
 2016-04-01 | 2016-06-01
 2016-05-01 | 2016-05-15
(2 rows)

Upvotes: 5

Related Questions