Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Working with Time Frames in T-SQL


Working with Time Frames in T-SQL

Author
Message
jlion
jlion
Forum Newbie
Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)

Group: General Forum Members
Points: 2 Visits: 53
Comments posted here are about the content posted at http://www.sqlservercentral.com/columnists/jLynds/3136.asp



Colin Bickford
Colin Bickford
SSC Veteran
SSC Veteran (207 reputation)SSC Veteran (207 reputation)SSC Veteran (207 reputation)SSC Veteran (207 reputation)SSC Veteran (207 reputation)SSC Veteran (207 reputation)SSC Veteran (207 reputation)SSC Veteran (207 reputation)

Group: General Forum Members
Points: 207 Visits: 41
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!



Confucius247
Confucius247
Grasshopper
Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)

Group: General Forum Members
Points: 16 Visits: 40
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.
whos on
whos on
Grasshopper
Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)

Group: General Forum Members
Points: 11 Visits: 39
To piss off snoody Eureopean wannabes.
RyanRandall
RyanRandall
SSCommitted
SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)

Group: General Forum Members
Points: 1761 Visits: 4652

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.
Richard Gardner-291039
Richard Gardner-291039
SSC Veteran
SSC Veteran (231 reputation)SSC Veteran (231 reputation)SSC Veteran (231 reputation)SSC Veteran (231 reputation)SSC Veteran (231 reputation)SSC Veteran (231 reputation)SSC Veteran (231 reputation)SSC Veteran (231 reputation)

Group: General Forum Members
Points: 231 Visits: 421

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....


RyanRandall
RyanRandall
SSCommitted
SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)

Group: General Forum Members
Points: 1761 Visits: 4652

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.
RyanRandall
RyanRandall
SSCommitted
SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)

Group: General Forum Members
Points: 1761 Visits: 4652

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://sqlserver2000.databases.aspfaq.com/why-should-i-consider-using-an-auxiliary-calendar-table.html

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.
PaulBarbin
PaulBarbin
Grasshopper
Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)

Group: General Forum Members
Points: 13 Visits: 15
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 1:00 PM AND -- true for all except case 6
[EndingTime] >= 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.



Robert Davis
Robert Davis
SSCommitted
SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)

Group: General Forum Members
Points: 1702 Visits: 1623

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
Twitter: @SQLSoldier
My book: Pro SQL Server 2008 Mirroring
Microsoft Certified Master, SQL Server MVP
Database Engineer at BlueMountain Capital Management
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