Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Searching the time data type Expand / Collapse
Author
Message
Posted Friday, September 14, 2012 8:45 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 12:33 PM
Points: 2,766, Visits: 7,244
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 &gt;= StartTime OR @Now &lt; 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
Post #1359352
Posted Friday, September 14, 2012 8:56 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Friday, June 27, 2014 12:43 PM
Points: 15,444, Visits: 9,596
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
Post #1359366
Posted Friday, September 14, 2012 9:02 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 12:33 PM
Points: 2,766, Visits: 7,244
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
Post #1359377
Posted Friday, September 14, 2012 9:17 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Friday, June 27, 2014 12:43 PM
Points: 15,444, Visits: 9,596
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
Post #1359403
Posted Saturday, September 15, 2012 10:39 AM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Today @ 5:07 AM
Points: 8,745, Visits: 9,293
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
Post #1359826
Posted Sunday, September 16, 2012 3:16 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 3:28 PM
Points: 36,995, Visits: 31,521
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 &gt;= StartTime OR @Now &lt; 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.


Do the start and end times have dates associated with them?


--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1359919
Posted Monday, September 17, 2012 6:35 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 12:33 PM
Points: 2,766, Visits: 7,244
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 &gt;= StartTime OR @Now &lt; 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.


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
Post #1360151
Posted Monday, September 17, 2012 6:36 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 12:33 PM
Points: 2,766, Visits: 7,244
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
Post #1360153
Posted Monday, September 17, 2012 6:46 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Friday, June 27, 2014 12:43 PM
Points: 15,444, Visits: 9,596
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
Post #1360163
Posted Monday, September 17, 2012 10:42 AM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Today @ 5:07 AM
Points: 8,745, Visits: 9,293
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
Post #1360326
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse