Working with Time Frames in T-SQL

  • jlion

    SSC Journeyman

    Points: 82

    Comments posted here are about the content posted at http://www.sqlservercentral.com/columnists/jLynds/3136.asp

  • Colin Bickford

    Say Hey Kid

    Points: 663

    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

    SSC-Addicted

    Points: 424

    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

    SSC Journeyman

    Points: 95

    To piss off snoody Eureopean wannabes.

  • RyanRandall

    SSChampion

    Points: 13623

    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

    Hall of Fame

    Points: 3603

    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

    SSChampion

    Points: 13623

    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

    SSChampion

    Points: 13623

    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

    SSC Veteran

    Points: 269

    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.

  • Robert Davis

    One Orange Chip

    Points: 28027

    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]

  • jlion

    SSC Journeyman

    Points: 82

    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.

  • Robert Davis

    One Orange Chip

    Points: 28027

    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]

  • Jeff Moden

    SSC Guru

    Points: 996858

    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".
    "If "pre-optimization" is the root of all evil, then what does the resulting no optimization lead to?"

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Jeff Moden

    SSC Guru

    Points: 996858

    quote 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".
    "If "pre-optimization" is the root of all evil, then what does the resulting no optimization lead to?"

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • RyanRandall

    SSChampion

    Points: 13623

    Thanks Jeff

    Ryan Randall

    Solutions are easy. Understanding the problem, now, that's the hard part.

Viewing 15 posts - 1 through 15 (of 20 total)

You must be logged in to reply to this topic. Login to reply