September 14, 2012 at 8:45 am
I have two fields of type time. StartTime and EndTime. I want to see if Now is between these two times. For most of the records in this table, there's no problem
DECLARE @Now time = CAST(GetDate() as time)
SELECT LoadNumber FROM LoadTimes WHERE @Now >=StartTime AND @Now < EndTime
I'm having a problem with one row though.
CREATE TABLE LoadTimes(
LoadNumber tinyint,
StartTime time,
EndTime time
)
INSERT INTO LoadTimes(LoadNumber, StartTime, EndTime)
VALUES(1, '18:00', '21:00'),
(2, '21:00', '1:00'),
(3, '1:00', '18:00')
The period for Load Number 2 starts at 9PM and ends at 1AM. How do I search for that? If I state
SELECT LoadNumber FROM LoadTimes WHERE @Now >= StartTime OR @Now < EndTime
I end up with the whole table in my results. I can't add 24 to the end time or to Now because the time data type doesn't accept 24:00 and up. My values are all whole hours now, but might not be in the future, so I can't just strip to integers and I want to know if there's a way to do this with time anyway. I suppose I could figure make it integers, but still I'd like to know if there's a way to do it with the time type.
--------------------------------------
When you encounter a problem, if the solution isn't readily evident go back to the start and check your assumptions.
--------------------------------------
It’s unpleasantly like being drunk.
What’s so unpleasant about being drunk?
You ask a glass of water. -- Douglas Adams
September 14, 2012 at 8:56 am
If EndTime < StartTime, subtract 12 hours from both.
DECLARE @Time1 TIME = '23:00', @Time2 TIME = '01:00';
SELECT DATEADD(hour, -12, @Time1), DATEADD(hour, -12, @Time2);
Edit: You'll have to subtract 12 hours from the time you want to test against them too, of course.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
September 14, 2012 at 9:02 am
GSquared (9/14/2012)
If EndTime < StartTime, subtract 12 hours from both.
DECLARE @Time1 TIME = '23:00', @Time2 TIME = '01:00';SELECT DATEADD(hour, -12, @Time1), DATEADD(hour, -12, @Time2);
Edit: You'll have to subtract 12 hours from the time you want to test against them too, of course.
Or I could add 12 to both, good idea, thanks. Time knows it is a circular base 24, but won't accept negative or over 24. I'll have to see if there are hour combinations where this won't work.
--------------------------------------
When you encounter a problem, if the solution isn't readily evident go back to the start and check your assumptions.
--------------------------------------
It’s unpleasantly like being drunk.
What’s so unpleasant about being drunk?
You ask a glass of water. -- Douglas Adams
September 14, 2012 at 9:17 am
Don't use it on AM starts with PM ends that are lower. 10:00 start, 13:00 end, would end up with the same problem you're trying to solve here. You have to use both.
select *
from MyTable
where MyTimeColumn between @StartTime and @EndTime
or dateadd(hour, -12, MyTimeColumn) between dateadd(hour, -12, @StartTime) and dateadd(hour, -12, @EndTime); -- second computation for times across midnight barrier
Include some documentation in the code like that, so future devs don't have to scratch their heads and wonder what you were smoking when you wrote it. I've seen that one generate some serious confusion, since it's counter-intuitive.
The problem with it is that you end up with an index scan, instead of a seek, because of the DateAdd. Here's a workaround for that kind of thing:
CREATE TABLE #TimesTest
(TimeColumn TIME NOT NULL);
CREATE CLUSTERED INDEX IDX_TimesTest ON #TimesTest (TimeColumn);
INSERT INTO #TimesTest
(TimeColumn)
SELECT DATEADD(SECOND, Number, 0)
FROM Common.dbo.Numbers;
GO
ALTER TABLE #TimesTest
ADD TimeColumn2 AS DATEADD(HOUR, -12, TimeColumn) PERSISTED;
CREATE INDEX IDX_TimeTest2 ON #TimesTest (TimeColumn2);
SET NOCOUNT ON;
GO
-- check the execution plan on this: Index -Seek-
SELECT *
FROM #TimesTest
WHERE TimeColumn2 = '13:15:45';
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
September 15, 2012 at 10:39 am
Unless there is some performance issue that I haven't spotted, it should be sensible to do this the simple and obvious way:
SELECT LoadNumber
FROM LoadTimes
WHERE (@Now >= StartTime AND (@Now < EndTime OR StartTime >= EndTime))
OR
(@Now <= EndTime AND EndTime < StartTime)
I can't see that adding or subtracting 12 hour chunks buys you anything useful, the calculation is just as complex if you do that.
Tom
September 16, 2012 at 3:16 pm
Stefan Krzywicki (9/14/2012)
I have two fields of type time. StartTime and EndTime. I want to see if Now is between these two times. For most of the records in this table, there's no problem
DECLARE @Now time = CAST(GetDate() as time)SELECT LoadNumber FROM LoadTimes WHERE @Now >=StartTime AND @Now < EndTime
I'm having a problem with one row though.
CREATE TABLE LoadTimes(LoadNumber tinyint,
StartTime time,
EndTime time
)
INSERT INTO LoadTimes(LoadNumber, StartTime, EndTime)
VALUES(1, '18:00', '21:00'),
(2, '21:00', '1:00'),
(3, '1:00', '18:00')
The period for Load Number 2 starts at 9PM and ends at 1AM. How do I search for that? If I state
SELECT LoadNumber FROM LoadTimes WHERE @Now >= StartTime OR @Now < EndTimeI end up with the whole table in my results. I can't add 24 to the end time or to Now because the time data type doesn't accept 24:00 and up. My values are all whole hours now, but might not be in the future, so I can't just strip to integers and I want to know if there's a way to do this with time anyway. I suppose I could figure make it integers, but still I'd like to know if there's a way to do it with the time type.
Do the start and end times have dates associated with them?
--Jeff Moden
Change is inevitable... Change for the better is not.
September 17, 2012 at 6:35 am
Jeff Moden (9/16/2012)
Stefan Krzywicki (9/14/2012)
I have two fields of type time. StartTime and EndTime. I want to see if Now is between these two times. For most of the records in this table, there's no problem
DECLARE @Now time = CAST(GetDate() as time)SELECT LoadNumber FROM LoadTimes WHERE @Now >=StartTime AND @Now < EndTime
I'm having a problem with one row though.
CREATE TABLE LoadTimes(LoadNumber tinyint,
StartTime time,
EndTime time
)
INSERT INTO LoadTimes(LoadNumber, StartTime, EndTime)
VALUES(1, '18:00', '21:00'),
(2, '21:00', '1:00'),
(3, '1:00', '18:00')
The period for Load Number 2 starts at 9PM and ends at 1AM. How do I search for that? If I state
SELECT LoadNumber FROM LoadTimes WHERE @Now >= StartTime OR @Now < EndTimeI end up with the whole table in my results. I can't add 24 to the end time or to Now because the time data type doesn't accept 24:00 and up. My values are all whole hours now, but might not be in the future, so I can't just strip to integers and I want to know if there's a way to do this with time anyway. I suppose I could figure make it integers, but still I'd like to know if there's a way to do it with the time type.
Do the start and end times have dates associated with them?
No, they're start and end for every day. The problem is that the business day ends at 1 AM the following calendar day.
--------------------------------------
When you encounter a problem, if the solution isn't readily evident go back to the start and check your assumptions.
--------------------------------------
It’s unpleasantly like being drunk.
What’s so unpleasant about being drunk?
You ask a glass of water. -- Douglas Adams
September 17, 2012 at 6:36 am
L' Eomot Inversé (9/15/2012)
Unless there is some performance issue that I haven't spotted, it should be sensible to do this the simple and obvious way:
SELECT LoadNumber
FROM LoadTimes
WHERE (@Now >= StartTime AND (@Now < EndTime OR StartTime >= EndTime))
OR
(@Now <= EndTime AND EndTime < StartTime)
I can't see that adding or subtracting 12 hour chunks buys you anything useful, the calculation is just as complex if you do that.
Thanks, I'll give this a shot too. Once I parse the Wheres in my head. 🙂
--------------------------------------
When you encounter a problem, if the solution isn't readily evident go back to the start and check your assumptions.
--------------------------------------
It’s unpleasantly like being drunk.
What’s so unpleasant about being drunk?
You ask a glass of water. -- Douglas Adams
September 17, 2012 at 6:46 am
L' Eomot Inversé (9/15/2012)
Unless there is some performance issue that I haven't spotted, it should be sensible to do this the simple and obvious way:
SELECT LoadNumber
FROM LoadTimes
WHERE (@Now >= StartTime AND (@Now < EndTime OR StartTime >= EndTime))
OR
(@Now <= EndTime AND EndTime < StartTime)
I can't see that adding or subtracting 12 hour chunks buys you anything useful, the calculation is just as complex if you do that.
Turns it all into scans instead of seeks. May not matter in this case.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
September 17, 2012 at 10:42 am
GSquared (9/17/2012)
L' Eomot Inversé (9/15/2012)
Unless there is some performance issue that I haven't spotted, it should be sensible to do this the simple and obvious way:
SELECT LoadNumber
FROM LoadTimes
WHERE (@Now >= StartTime AND (@Now < EndTime OR StartTime >= EndTime))
OR
(@Now <= EndTime AND EndTime < StartTime)
I can't see that adding or subtracting 12 hour chunks buys you anything useful, the calculation is just as complex if you do that.
Turns it all into scans instead of seeks. May not matter in this case.
But it has the advantage that it works as long as end is not more than 24 hours after start, which is quite a useful advantage - better performance but getting an incorrect result doesn't, as I said, buy you anything.
Subtracting 12 fails for for every case where start and end are both pm or both am, end is on the day after start, and end is less that 24 hours after star - eg start = 11:00, end = 10:00, or start = 22, end = 14 - it only works when start and end are both on the same day or start is pm and end is am. Won't the changes needed to make it work for all intervals under 24 hours make it do scans?
Tom
September 17, 2012 at 10:54 am
L' Eomot Inversé (9/17/2012)
GSquared (9/17/2012)
L' Eomot Inversé (9/15/2012)
Unless there is some performance issue that I haven't spotted, it should be sensible to do this the simple and obvious way:
SELECT LoadNumber
FROM LoadTimes
WHERE (@Now >= StartTime AND (@Now < EndTime OR StartTime >= EndTime))
OR
(@Now <= EndTime AND EndTime < StartTime)
I can't see that adding or subtracting 12 hour chunks buys you anything useful, the calculation is just as complex if you do that.
Turns it all into scans instead of seeks. May not matter in this case.
But it has the advantage that it works as long as end is not more than 24 hours after start, which is quite a useful advantage - better performance but getting an incorrect result doesn't, as I said, buy you anything.
Subtracting 12 fails for for every case where start and end are both pm or both am, end is on the day after start, and end is less that 24 hours after star - eg start = 11:00, end = 10:00, or start = 22, end = 14 - it only works when start and end are both on the same day or start is pm and end is am. Won't the changes needed to make it work for all intervals under 24 hours make it do scans?
All intervals are under 24 hours, this is time only, not datetime.
Performance seems decent and this is only run against the current time to determine what queries to run at that period, so there's no chance of a scaling problem.
--------------------------------------
When you encounter a problem, if the solution isn't readily evident go back to the start and check your assumptions.
--------------------------------------
It’s unpleasantly like being drunk.
What’s so unpleasant about being drunk?
You ask a glass of water. -- Douglas Adams
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply