0

Is there a straightforward way to find if a time is between two times? (00:00-23:59) I represent the times by the starting time and an DAY TO SECOND interval, because I want to account for the interval overlapping midnight.

For instance: I have a time 15:20 and want to find if it is in these intervals:

10:00, interval 06:00 - yes

15:00, interval 01:00 - yes

23:30, interval 20:00 - yes

16:00, interval 05:00 - no

And for time 21:00:

20:00, interval 06:00

The data is stored in a table:

CREATE TABLE Opening_Interval 
( 
 IntervalID INTEGER  NOT NULL , 
 DayOfWeek INTEGER  NOT NULL , 
 OpenFrom DATE  NOT NULL , 
 OpenLength INTERVAL DAY (1) TO SECOND (2)  NOT NULL ,  
);

First thing that comes to mind is 

1) Check if wanted_time > beginning_time
2) If yes, check if wanted_time < beginning_time + interval

But I don't think this would work, because in the last scenario the condition would be 

1) 21:00 > 20:00? OK
2) 21:00 < 02:00? FAIL

So what would be the correct way to do this?

Edit: related question: when comparing like this: TO_DATE('14:00', 'HH24:MI') >= OPENFROM, and OPENFROM is of DATE type, does the comparison ignore everything except hours and minutes ?

Martin Melka
  • 7,177
  • 16
  • 79
  • 138
  • `I think it is clear`. It would be much more clear if you provided information about where those data are coming from? Column(s) of what data type they are stored in? `I have a time 15:20`. And this *time* of what data type, `varchar2`? – Nick Krasnov Nov 25 '13 at 16:14
  • If you compare only hours, you'll never get to compare a range between two days. You must include a date along with the time to compare intervals greater than one day. Add any date, even though 1900 as the year and month such as January. Meanwhile add the desired number of days and then the hours you want. So you can compare with the start date and end date. – Andrew Paes Nov 25 '13 at 16:27

1 Answers1

0

You can try:

to_date(to_char(OpenFrom , 'yyyymmdd') || <<your_time>>, 'yyyymmddhh24:mi')
between OpenFrom and OpenFrom + OpenLength

EDIT:

Try:

to_date(to_char(OpenFrom , 'yyyymmdd') || <<your_time>>, 'yyyymmddhh24:mi')
between OpenFrom and OpenFrom + OpenLength
or 
to_date(to_char(OpenFrom + OpenLength , 'yyyymmdd') || <<your_time>>, 'yyyymmddhh24:mi')
between OpenFrom and OpenFrom + OpenLength

Here is a sqlfiddle demo

A.B.Cade
  • 16,735
  • 1
  • 37
  • 53
  • That doesn't work, let's have `[my_time]=='02:00'`, `OpenFrom==10:00`, `OpenLength==17 hours`. (Open 10:00-03:00). Now, when I apply your suggestion, I am comparing this, for instance: `[1.1.2013 02:00] between [1.1.2013 10:00] and [2.1.2013 03:00]` - which obviously is not. I think that if i compared both `[1.1.2013 10:00]` and `[2.1.2013 10:00]` with the interval (using OR), it might work. Or at least I can't think of a situation where it wouldn't. – Martin Melka Nov 25 '13 at 19:19