Working with Time Frames in T-SQL
-
Comments posted here are about the content posted at http://www.sqlservercentral.com/columnists/jLynds/3136.asp
-
A well constructed article, but can someone explain to me why American database professionals continue to use the ambiguous and confusing mm/dd/yy date format? I can understand them using it in their daily lives - but not when publishing code that is read outside the States!
-
I only ever use yyyymmddhhmmss now, although I get funny looks when someone asks me the time and I tell them it's 20,070,815,090,834. Quite a mouthful too.
-
To piss off snoody Eureopean wannabes.
-
Hi all,
I'd approach it more like this, I think. The code below produces the same results and is safe to run...
DECLARE @MY_EVENTS TABLE (DESCRIPTION VARCHAR(50), STARTING_TIME DATETIME, ENDING_TIME DATETIME)
INSERT @MY_EVENTS
SELECT 'James Bond', '20070701 10:30', '20070701 11:00'
UNION SELECT 'Sally May', '20070701 11:30', '20070701 13:30'
UNION SELECT 'Jerry Jones', '20070701 11:30', '20070701 12:30'
UNION SELECT 'Mike Moxie', '20070701 12:30', '20070701 13:00'
UNION SELECT 'Carl Cluff', '20070701 12:30', '20070701 14:00'
UNION SELECT 'Mad Max', '20070701 14:00', '20070701 14:30'
--a) output is 4
SELECT COUNT(*) FROM @MY_EVENTS
WHERE NOT (ENDING_TIME < '20070701 12:00' OR STARTING_TIME > '20070701 13:00')
--b) output is 150
SELECT SUM(
DATEDIFF(mi,
CASE WHEN '20070701 12:00' < STARTING_TIME THEN STARTING_TIME ELSE '20070701 12:00' END,
CASE WHEN '20070701 13:00' > ENDING_TIME THEN ENDING_TIME ELSE '20070701 13:00' END))
FROM @MY_EVENTS
WHERE NOT (ENDING_TIME < '20070701 12:00' OR STARTING_TIME > '20070701 13:00')
--aside
DECLARE @INTEGERS TABLE (i INT) --It's generally recommended you have a static 'numbers' table somewhere - this is for demo only
INSERT @INTEGERS SELECT DISTINCT colid FROM syscolumns WHERE colid BETWEEN 1 AND 24 ORDER BY colid
--c) output is 10 rows
DECLARE @DAYHOURS TABLE (DESCRIPTION VARCHAR(50), STARTING_TIME SMALLDATETIME, BEGIN_HOUR DATETIME, IDURATION INT)
INSERT @DAYHOURS
SELECT DESCRIPTION, STARTING_TIME, BEGIN_HOUR,
DATEDIFF(mi,
CASE WHEN BEGIN_HOUR < STARTING_TIME THEN STARTING_TIME ELSE BEGIN_HOUR END,
CASE WHEN END_HOUR > ENDING_TIME THEN ENDING_TIME ELSE END_HOUR END) AS IDURATION
FROM @MY_EVENTS, (SELECT DATEADD(hh, i-1, '20070701') BEGIN_HOUR, DATEADD(hh, i, '20070701') END_HOUR FROM @Integers) HOURS
WHERE NOT (ENDING_TIME <= BEGIN_HOUR OR STARTING_TIME >= END_HOUR)
SELECT * FROM @DAYHOURS
--d) output is '12:00'
DECLARE @MAXHOUR DATETIME
SET @MAXHOUR = (SELECT TOP 1 BEGIN_HOUR FROM @DAYHOURS GROUP BY BEGIN_HOUR ORDER BY COUNT(*) DESC)
SELECT @MAXHOUR 'Busiest Hour'
--e) output is 4 rows
SELECT * FROM @DAYHOURS WHERE BEGIN_HOUR = @MAXHOUR
Ryan Randall
Solutions are easy. Understanding the problem, now, that's the hard part.
-
I think that's has beens actually, not wannabes, get your facts straight Mr Schneebaum.
Quite aside from the Imperialist yanks and their impending implosion up into their own cultural vacuum, I thought that was quite a useful article. I do a lot of data work, particularly on scheduling processes, so there's some useful lessons there.
One thing I find quite handy, particularly when shunting data between data sources and making comparisons, is to split the date into mm, dd, yy, mins and stop treating it as a date at all.
Actually the ONLY cursor I've ever implemented has been to solve a similar problem, trying to find the difference between two events - I was getting serious runtime issues doing it Joes way, and cut from 1 minute 30 to 10 seconds using a cursor - won't post the code 'cos it's long and boring....
-
Incidentally, if you're using SQL 2005, you can jump straight to the final result fairly elegantly.
Code is safe to run...
DECLARE @MY_EVENTS TABLE (DESCRIPTION VARCHAR(50), STARTING_TIME DATETIME, ENDING_TIME DATETIME)
INSERT @MY_EVENTS
SELECT 'James Bond', '20070701 10:30', '20070701 11:00'
UNION SELECT 'Sally May', '20070701 11:30', '20070701 13:30'
UNION SELECT 'Jerry Jones', '20070701 11:30', '20070701 12:30'
UNION SELECT 'Mike Moxie', '20070701 12:30', '20070701 13:00'
UNION SELECT 'Carl Cluff', '20070701 12:30', '20070701 14:00'
UNION SELECT 'Mad Max', '20070701 14:00', '20070701 14:30';
--e) output is 4 rows
WITH
INTEGERS AS (SELECT DISTINCT colid i FROM syscolumns WHERE colid BETWEEN 1 AND 24),
HOURS AS (SELECT DATEADD(hh, i-1, '20070701') BEGIN_HOUR, DATEADD(hh, i, '20070701') END_HOUR FROM INTEGERS),
DAYHOURS AS (
SELECT DESCRIPTION, STARTING_TIME, ENDING_TIME, BEGIN_HOUR,
DATEDIFF(mi,
CASE WHEN BEGIN_HOUR < STARTING_TIME THEN STARTING_TIME ELSE BEGIN_HOUR END,
CASE WHEN END_HOUR > ENDING_TIME THEN ENDING_TIME ELSE END_HOUR END) AS IDURATION
FROM @MY_EVENTS, HOURS
WHERE NOT (ENDING_TIME <= BEGIN_HOUR OR STARTING_TIME >= END_HOUR))
SELECT * FROM DAYHOURS WHERE BEGIN_HOUR = (SELECT TOP 1 BEGIN_HOUR FROM DAYHOURS GROUP BY BEGIN_HOUR ORDER BY COUNT(*) DESC)
/*
DESCRIPTION STARTING_TIME ENDING_TIME BEGIN_HOUR IDURATION
-------------------- ----------------------- ----------------------- ----------------------- -----------
Carl Cluff 2007-07-01 12:30:00.000 2007-07-01 14:00:00.000 2007-07-01 12:00:00.000 30
Jerry Jones 2007-07-01 11:30:00.000 2007-07-01 12:30:00.000 2007-07-01 12:00:00.000 30
Mike Moxie 2007-07-01 12:30:00.000 2007-07-01 13:00:00.000 2007-07-01 12:00:00.000 30
Sally May 2007-07-01 11:30:00.000 2007-07-01 13:30:00.000 2007-07-01 12:00:00.000 60
*/
Ryan Randall
Solutions are easy. Understanding the problem, now, that's the hard part.
-
Oh, and here are my favo(u)rite date links (so to speak)...
http://www.sql-server-performance.com/articles/dev/datetime_datatype_p1.aspx
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=64762
I hope someone finds all this stuff useful...
Ryan Randall
Solutions are easy. Understanding the problem, now, that's the hard part.
-
Thanks for the article. Jolly good show man. (c;
We have many intersecting datetime queries and we are usually looking for ANY event that intersects a given time period. That is, even if the process started the day before and finishes the day after, we want to know about it. (If you don't, then ignore this advice). If you do care about those, then the cases 2,3,4,5 can be simplified like this:
timeslice begins before the process ends and
timeslice ends after the process starts
which can be expressed like this:
[StartingTime] = 7/1/2007 12:00 PM -- true for all except case 1
Also, thanks to Ryan on the 2005 Common Table Expression. All of our clients are not yet running 2005 so I haven't gotten to play too much with the CTEs.
-
Why are you defaulting to 59 minutes if the process was running before the hour started and finished after the hour ended?
A process that is already running at 12:00 and is still running at 1:00 was running for 60 minutes from 12:00 to 1:00, not 59 minutes. Likewise, if ti started at 12:30 and was still running at 1:00, then it was running for 30 minutes, not 29 minutes, during that hour.
My blog: SQL Soldier[/url]
SQL Server Best Practices: SQL Server Best Practices
Twitter: @SQLSoldier
My book: Pro SQL Server 2008 Mirroring[/url]
Microsoft Certified Master: SQL Server, Data Platform MVP
Database Engineer at BlueMountain Capital Management[/url] -
Let's there's a process that begins at 1:00 and ends at 3:00.
So 1:00 belongs to hour #1. 60 minutes later it is 2:00. But what about 2:00? it belongs to hour #2.
So there are 59 minutes, 59 seconds in hour #1. The minimum time slice is a whole minute. There's no accommodation in the script for fractional minutes so those extra 59 seconds get tossed out. The same is true for a process that begins at 1:30 and ends at 2:00. There are actually 29 minutes, 29 seconds, 9999 milliseconds and so on consumed in hour 1.
If you round the 59 seconds up to a whole minute then you risk counting more minutes in a day than actually exist, as the last minute in each hour would be counted twice, once at the end of the hour, and then again at the beginning of the next hour.
The solution would be to account for fractional time slices.
-
There are not 59 minutes and 59 seconds in hour #1. There isn't even 59 minutes, 59.9999 seconds. There is 60 seconds in a minute and 60 minutes in an hour. Those are constants.
If you were counting 2:00:00 as being part of hour #1, there would be 60 minutes and 1 second in hour #1.
Test it for yourself. Count from 1:00:00 to 2:00:00 incrementing by 1 second.
Or just run the following:
Select
Hour1 = DateDiff(mi, '1/1/2007 1:00 PM', '1/1/2007 2:00 PM'), Hour2 = DateDiff(mi, '1/1/2007 2:00 PM', '1/1/2007 3:00 PM'), Hour1and2Combined = DateDiff(mi, '1/1/2007 1:00 PM', '1/1/2007 3:00 PM')
Or if that doesn't convince you, run the following. You will see that if you included 2:00 as part of hour #1, it would be the 61st minute, not the 60th.
Declare
@Start datetime, @End datetime
Declare @Minutes Table (MinuteNumber int not null identity(1, 1) primary key, MyTime datetime not null)
Select
@Start = '1/1/2007 1:00 PM', @End = '1/1/2007 2:00 PM'
While
@Start <= @End
Begin
Insert Into @Minutes (MyTime)
Select @Start
Set @Start = DateAdd(mi, 1, @Start)
End
Select
*
From @Minutes
My blog: SQL Soldier[/url]
SQL Server Best Practices: SQL Server Best Practices
Twitter: @SQLSoldier
My book: Pro SQL Server 2008 Mirroring[/url]
Microsoft Certified Master: SQL Server, Data Platform MVP
Database Engineer at BlueMountain Capital Management[/url] -
Heh... for the same reason most British don't measure heat in British Thermal Units?
--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.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Incidentally, if you're using SQL 2005, you can jump straight to the final result fairly elegantly Glad to see that someone finally figured out that there are 30 minutes between 12:30 and 13:00 and not just 29...
Nicely done, Ryan.--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.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Thanks Jeff
Ryan Randall
Solutions are easy. Understanding the problem, now, that's the hard part.
Viewing 15 posts - 1 through 15 (of 19 total)
You must be logged in to reply to this topic. Login to reply