Time range- Sql

Question

please help me with my problem. So, I have a table named 'RATES' which contains these columns:

id (int) 
rate (money) 
start_time (datetime) 
end_time(datetime)

example data:

1 150 8:00am 6:00pm 
2 200 6:00pm 4:00am
3 250 8:00am 4:00am (the next day)

What I have to do is to select all the id(s) to where a given time would fall.

e.g given time: 9:00 pm, the output should be 2,3

The problem is I got this time range between 8am to 4am the next day and I don't know what to do. Help, please! thanks in advance :D

What is the type of the date fields? If not date you are very very sad. (But @gbn will still show you how.) — Hogan, May 09 '11 at 18:14
@gbn - id (int) rate (money) start_time (datetime) end_time(datetime), but I'm not really sure about these data types. I'm so confused :'( — mmm, May 09 '11 at 18:14
The rows that satisfy the time of 9 **pm** are 2 and 3. The rows 1 and 3 would satisfy the request of 9 **am**, though, if I've understood it right. — Andriy M, May 09 '11 at 18:23
Oh, yes! Sorry, I made a mistake. You're right, it should be 2 and 3. — mmm, May 09 '11 at 18:29
Can you redefine the data in your rates table so that it contains: {1, 150, 08:00, 18:00}, {2, 200, 18:00, 24:00}, {2, 200, 00:00, 04:00}, {3, 250, 08:00, 24:00}, {3, 250, 00:00, 04:00}? Then you have a simple task. If 24:00 won't work but 23:59 will, you'd probably be OK too--as long as you don't get seconds in a value you're looking for. — Jonathan Leffler, May 09 '11 at 21:13

score 3 · Accepted Answer · answered May 10 '11 at 01:19

3

Assuming that @Andriy M is correct:

Data never spans more than 24 hours
if end_time<=start_time then end_time belongs to the next day

then what you're looking for is this:

Declare @GivenTime DateTime
Set @GivenTime = '9:00 PM'
Select ID
  From Rates
 Where (Start_Time<End_Time And Start_Time<=@GivenTime And End_Time>=@GivenTime)
    Or (Start_Time=End_Time And Start_Time=@GivenTime)
    Or (Start_Time>End_Time And (Start_Time>=@GivenTime Or End_Time<=@GivenTime))

answered May 10 '11 at 01:19

Allan W

580
1
3
8

I've no idea what problem this solves, but it is not the one posted above. Start_Time and End_Time are of `datetime` type not `time` type. Unless the table stores all `datetime` values as happening on the same day this will not work. – Hogan May 10 '11 at 10:42
@Hogan: The table seems to describe different rates of something over the day, so the actual date part is irrelevant, but it should be consistently the same for all the values. Consequently, when comparing, the parameter should have the same date part as the stored values. The `datetime` type must have been chosen because `time` is not supported (or wasn't supported at the time the project started). I can't know for sure, but I think that's quite possible. – Andriy M May 12 '11 at 20:58
@Andriy - everything you say is true. It does not change this answer to one which will run correctly against such data. – Hogan May 12 '11 at 20:59
@Hogan: I can't see any problem, to be honest. I may be missing something. If you think that important, please elaborate on what you think is the issue. Maybe you have some scenario in mind which can break this solution. I think the OP would be interested to know that. Well, many other people would, including myself. :) – Andriy M May 12 '11 at 21:02
Ah yes, I think I can now see what can cause a problem. This solution will not work for the OP's example data, if the parameter specifies a time earlier than 8:00 am. – Andriy M May 12 '11 at 21:20
This should probably solve all the issues (assuming all the values have identical date parts): `Where (Start_Time@GivenTime) or (Start_Time>=End_Time And (Start_Time<=@GivenTime Or End_Time>@GivenTime))` – Andriy M May 12 '11 at 21:32
@Andriy M, @Hogan-- Sorry guys, I thought I got it correctly but then after testing the query with different given time I noticed that there was something wrong because it didn't satisfy my requirements anymore. So I think I'll try other queries or think for other alternatives. :) – mmm May 13 '11 at 11:50
@mmm: It's alright. Did you try my suggestion above? Was it unhelpful too? – Andriy M May 13 '11 at 12:25
@Andriy M: I tried it too, but when I set @giventime= '9:00am' or anything within the range of 8am-6pm, it still wouldn't give me the first id (id #1 has 8am-6pm range). – mmm May 13 '11 at 13:15
@mmm: I'd say, strange. It definitely worked for me just now: `Declare @GivenTime DateTime;` `Set @GivenTime = '9:00 AM';` `create table Rates (id int, rate money, start_time datetime, end_time datetime);` `insert into Rates select 1, 150, '8:00am', '6:00pm' union all select 2, 200, '6:00pm', '4:00am' union all select 3, 250, '8:00am', '4:00am';` `Select ID From Rates Where (Start_Time < End_Time And Start_Time <= @GivenTime And End_Time > @GivenTime) or (Start_Time >= End_Time And (Start_Time <= @GivenTime Or End_Time > @GivenTime));` `drop table Rates;` (Sorry for posting it in such a mess.) – Andriy M May 13 '11 at 16:06

score 0 · Answer 2 · answered May 09 '11 at 18:42

0

I don't really ever use MS SQL, but maybe this will help.

I was going to suggest something like this, but by the way you have your data set up, this would fail.

SELECT id FROM RATES WHERE datepart(hh, start_time) <= 9 AND datepart(hh, end_time) >= 9;

You'll have you search using the actual date if you expect to get the correct data back.

SELECT id FROM RATES WHERE start_time <= '2011-1-1 9:00' AND end_time >= '2011-1-1 9:00';

This may not be exactly correct, but it may help you look in the right direction.

answered May 09 '11 at 18:42

Bryce Siedschlaw

4,136
1
24
36

SELECT id FROM RATES WHERE start_time <= '2011-1-1 9:00' AND end_time >= '2011-1-1 9:00'; <-- might be the easiest way, but I can't really include the date since only the time is given. I'm not even sure if my data types are correct. Thank you for your answer though, I appreciate it :) – mmm May 09 '11 at 18:51
Ah, I figured you had the date since you mentioned they were datetimes, but I spose this wouldn't work in that case. – Bryce Siedschlaw May 09 '11 at 19:01
Since only the time is given, how do you know that 4am is on the next day (row 3)? If it's because it is before the start time, then how do you know that 6pm is not on the following day (row 1)? – Kit Z. Fox May 09 '11 at 19:03
Hah, I was just about to ask the exact same question. How would you know if it was not two days from the start time? – Bryce Siedschlaw May 09 '11 at 19:05
Also, if a start time was 8am and the end time was 9am (but of the next day) how would you know? – Bryce Siedschlaw May 09 '11 at 19:06
Ok. I'm totally confused. My table includes both the date and the time. But the given time is really just time. – mmm May 09 '11 at 19:31
@mmm: Let me summarise your schema how I understand it (and please correct me if I'm wrong): The time range never spans more than 24 hours. If `end_time` > `start_time`, then `end_time` relates to the same day as `start_time`. If `end_time` <= `start_time` then `end_time` belongs to the next day. Is that right? – Andriy M May 09 '11 at 20:32

score 0 · Answer 3 · edited May 23 '17 at 11:47

I guess @gbn is not going to help you. I will try and fill in.

Given -- a table called timedata that has ranges only going over at most one day

WITH normalized AS
(
   SELECT * 
   FROM timedata
   WHERE datepart(day,start_time) = datepart(day,endtime)
   UNION ALL
   SELECT id, rate, start_time, dateadd(second,dateadd(day,datediff(day,0,end_time),0),-1) as end_time 
   FROM timedata
   WHERE not (datepart(day,start_time) = datepart(day,endtime))
   UNION ALL
   SELECT id, rate,dateadd(day,datediff(day,0,end_time),0) as start_time, end_time
   FROM timedata
   WHERE not (datepart(day,start_time) = datepart(day,endtime))
)
SELECT * 
FROM normalized
WHERE datepart(hour,start_time) < @inhour
  AND datepart(hour,end_time) > @inhour

This makes use of a CTE and a trick to truncate datetime values. To understand this trick read this question and answer: Floor a date in SQL server

Here is an outline of what this query does:

Create a normalized table with each time span only going over one day by

Selecting all rows that occur on the same day.

Then for each entry that spans two days joining in

Selecting the starttime and one second before the next day as the end time for all that span.

and

Selecting 12am of the end_time date as the starttime and the end_time.

Finally you perform the select using the hour indicator on this normalized table.

If your ranges go over more than one day you would need to use a recursive CTE to get the same normalized table.

Time range- Sql

3 Answers3