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

Working with Time Frames in T-SQL Expand / Collapse
Author
Message
Posted Friday, July 27, 2007 12:02 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, June 28, 2013 8:25 AM
Points: 2, Visits: 42
Comments posted here are about the content posted at http://www.sqlservercentral.com/columnists/jLynds/3136.asp


Post #385945
Posted Wednesday, August 15, 2007 1:28 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Tuesday, January 19, 2010 6:37 AM
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!





Post #390804
Posted Wednesday, August 15, 2007 2:09 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, March 27, 2009 4:01 AM
Points: 14, 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.
Post #390814
Posted Wednesday, August 15, 2007 4:30 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, February 10, 2011 8:20 AM
Points: 11, Visits: 39
To piss off snoody Eureopean wannabes.
Post #390834
Posted Wednesday, August 15, 2007 4:55 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Tuesday, May 29, 2012 11:22 AM
Points: 1,755, Visits: 4,652

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.
Post #390840
Posted Wednesday, August 15, 2007 4:59 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: 2 days ago @ 3:29 AM
Points: 238, 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....

Post #390842
Posted Wednesday, August 15, 2007 5:13 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Tuesday, May 29, 2012 11:22 AM
Points: 1,755, Visits: 4,652

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.
Post #390845
Posted Wednesday, August 15, 2007 5:19 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Tuesday, May 29, 2012 11:22 AM
Points: 1,755, Visits: 4,652

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.
Post #390846
Posted Wednesday, August 15, 2007 8:15 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, October 16, 2008 10:10 AM
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.



Post #390898
Posted Wednesday, August 15, 2007 1:26 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Yesterday @ 10:29 AM
Points: 1,592, Visits: 1,488

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 2008
Principal DBA: Outerwall, Inc.
Also available for consulting: SQL DBA Master
Post #391050
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse