Get the date interval from table rows, based on a start stop column

Question

I have a table returning the following result set:

  mydate    | startstop
------------+----------
 2018-02-07 | start
 2018-02-14 | stop
 2017-02-06 | start
 2017-02-12 | stop
 2016-02-05 | start 
 2016-02-12 | stop 

I need to know if my current date is in one of the intervals, for example if I query the table from the current date '2017-02-07', I need to get 'TRUE'.

I know it looks like that simple but it is not being simple!

The best that I found is this:

       select true 
        where '2017-02-06'>=
         (select mydate from mytable where starstop='start' order by id limit 1) 
        and '2017-02-06' <= 
         (select mydate from mytable where startstop='stop' order by id limit 1);

It works returning TRUE if the date is in one of each intervals, but only if the table does not have intervals in the future, and as you can see my table has intervals in the future.

Note: The database management system is PostgreSQL 9.1

Answer 1

It's actually very simple. Start from the wanted date and descend the dates until you find the first row. If it's 'start' then you are inside an interval. If it's 'stop', you are not:

select 
  ( select startstop
    from mytable 
    where mydate = '2017-02-07' and startstop = 'start'
       or mydate < '2017-02-07'
    order by mydate desc
    limit 1
  ) = 'start'
  as result ;

The complicated WHERE is for dealing with the inclusive date ranges. If inclusive-exclusive were used, the condition would be simper.

A similar way - perhaps a bit more clear - would be:

with ct as 
  ( select mydate, startstop
    from mytable 
    where mydate <= '2017-02-07'
    order by mydate desc
    limit 1
  )
select 
       ('start') = (select startstop from ct) 
    or ('2017-02-07', 'stop') = (table ct) 
  as result ;

Answer 2

So it looks like you need to transform this into something that looks like:

startdate   |   stopdate
2016-02-06  |   2016-02-12
2017-02-06  |   2017-02-12

after that, you can then do a query that looks like

select startdate, stopdate
from mytransform
where startdate <= '2017-02-06'
and stopdate >= '2017-02-06'

which will probably require some sort of cursor to parse through and match up start and stop dates to build your interval.