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

  • Your example results don't show an interval, they show a distinct value set. So are you trying to get the time between the start and stop events or just a list of dates with starts and a list of dates with stops? – CaM Feb 7 '17 at 14:02
  • I need to know if my date is in one of the intervals. That is not ideal but enough. A important thing is that the table has future date intervals, that bug my second trie in the query examples. – Luciano Andress Martini Feb 7 '17 at 14:05
  • Sorry i corrected now you can see the intervals. – Luciano Andress Martini Feb 7 '17 at 14:12
  • If you query for '2018-02-14' should you get true as result or false? – ypercubeᵀᴹ Feb 7 '17 at 14:37
  • It should return true. I think i found a solution but i dont know if it is the best way. I will post in the answers, but please if you have something better. – Luciano Andress Martini Feb 7 '17 at 14:40

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 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
       ('start') = (select startstop from ct) 
    or ('2017-02-07', 'stop') = (table ct) 
  as result ;
  • I will try this. And let you know if it works. – Luciano Andress Martini Feb 7 '17 at 14:42
  • Pretty clever. I was going to approach it using windowing functions to group the rows into pairs, but this is much more elegant. – Spivonious Feb 7 '17 at 14:42
  • For some unknown reason is returning false when the date is the last value of the interval. For exemple if the date is 2017-02-12. It should still return true. – Luciano Andress Martini Feb 7 '17 at 14:47
  • 1
    Yes, it is supposed to do that. I will edit to deal with the different requirements. – ypercubeᵀᴹ Feb 7 '17 at 14:48
  • 1
    Oops. It is correct now. – ypercubeᵀᴹ Feb 7 '17 at 15:04

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.

  • Yes. The problem is that the company has this table in production from years, and i have no idea of how to transform it. And they are not considering to change the table structure. – Luciano Andress Martini Feb 7 '17 at 14:13
  • What database engine is this stored in? That will determine how best to proceed. – CaM Feb 7 '17 at 14:17
  • PostgreSQL 9.1. Looks like simple, but terrible to solve using a single query isn't? I am considering to use a loop with a flag. I cant believe that this will require it. That is disheartening... – Luciano Andress Martini Feb 7 '17 at 14:25

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.