SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Searching the time data type


Searching the time data type

Author
Message
Sioban Krzywicki
Sioban Krzywicki
SSCarpal Tunnel
SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)

Group: General Forum Members
Points: 4444 Visits: 8096
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
GSquared
GSquared
SSC Guru
SSC Guru (57K reputation)SSC Guru (57K reputation)SSC Guru (57K reputation)SSC Guru (57K reputation)SSC Guru (57K reputation)SSC Guru (57K reputation)SSC Guru (57K reputation)SSC Guru (57K reputation)

Group: General Forum Members
Points: 57435 Visits: 9730
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
Sioban Krzywicki
Sioban Krzywicki
SSCarpal Tunnel
SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)

Group: General Forum Members
Points: 4444 Visits: 8096
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
GSquared
GSquared
SSC Guru
SSC Guru (57K reputation)SSC Guru (57K reputation)SSC Guru (57K reputation)SSC Guru (57K reputation)SSC Guru (57K reputation)SSC Guru (57K reputation)SSC Guru (57K reputation)SSC Guru (57K reputation)

Group: General Forum Members
Points: 57435 Visits: 9730
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
Tom Thomson
Tom Thomson
One Orange Chip
One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)

Group: General Forum Members
Points: 25903 Visits: 12494
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

Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (214K reputation)SSC Guru (214K reputation)SSC Guru (214K reputation)SSC Guru (214K reputation)SSC Guru (214K reputation)SSC Guru (214K reputation)SSC Guru (214K reputation)SSC Guru (214K reputation)

Group: General Forum Members
Points: 214026 Visits: 41979
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 < 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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Sioban Krzywicki
Sioban Krzywicki
SSCarpal Tunnel
SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)

Group: General Forum Members
Points: 4444 Visits: 8096
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 < 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
Sioban Krzywicki
Sioban Krzywicki
SSCarpal Tunnel
SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)

Group: General Forum Members
Points: 4444 Visits: 8096
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
GSquared
GSquared
SSC Guru
SSC Guru (57K reputation)SSC Guru (57K reputation)SSC Guru (57K reputation)SSC Guru (57K reputation)SSC Guru (57K reputation)SSC Guru (57K reputation)SSC Guru (57K reputation)SSC Guru (57K reputation)

Group: General Forum Members
Points: 57435 Visits: 9730
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
Tom Thomson
Tom Thomson
One Orange Chip
One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)

Group: General Forum Members
Points: 25903 Visits: 12494
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

Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search