Postgresql date range

Question

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(?) ) )

klin · Accepted Answer · 2016-09-30 08:00:53Z

5

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)

edited Sep 30, 2016 at 8:00

answered Sep 30, 2016 at 7:52

klin

123k15 gold badges240 silver badges262 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

SergkeiM Over a year ago

thank you for solution, but can't change datatype of column

klin Over a year ago

You don't have to. Use daterange only in where clause, just like in the example.

SergkeiM Over a year ago

using daterange(DATE(event_from), DATE(event_to)) && daterange(DATE('2016-05-01'), DATE('2016-05-31')) Solved it but not getting records that is only one day event.

klin Over a year ago

For one day event use one day daterange, e.g. daterange('2016-01-01', '2016-01-01'), this works well.

Collectives™ on Stack Overflow

Postgresql date range

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related