Page 1 of 2 12 LastLast
Results 1 to 15 of 17
  1. #1
    Join Date
    Oct 2002
    Posts
    34

    Unanswered: Calculating Date/Time Differences Part II (long, good read)

    Ok, a week or so I asked how to get calculate the hours & minutes difference between the current date/time and a timestamp field in a database.
    First suggestion was TimeStampDiff but IBM says this function is an approximation only: http://www7b.boulder.ibm.com/dmdd/li.../0211yip3.html

    So here's what I came up with:
    Select
    Day(current timestamp-row_added_Dttm) as DayDiff,
    Hour(current timestamp-row_added_dttm) as HourDiff,
    Minute(current timestamp-row_added_dttm) as MinDiff

    and then (DayDiff*24) + HourDiff to get the hours.

    This worked great until some of my records were a month old and the DayDiff stayed under 30 - that is, for records a month old, DayDiff was being returned as 3, 4 days. I read up on Day and it extracts the Day portion of a timestamp only and will thus always be 1-31.

    So for records over a month:
    Month(current timestamp-row_added_dttm) As MonthDiff,
    Day(current timestamp-row_added_Dttm) as DayDiff,
    Hour(current timestamp-row_added_dttm) as HourDiff,
    Minute(current timestamp-row_added_dttm) as MinDiff

    and then for each monthdiff returned I'd have to figure out what month was part of the difference so I can:
    (#of days in month*24hours)+(24Hours*dayDiff)+HourDiff=Hour Difference

    Three questions:
    1. So, is this insane - is there not
    an easier way to calculate Hour difference between two dates?

    2. How can I determine from the MonthDiff what month is part of the difference? How can I get #of days in month for that month?

    3. If the field ages over a year, how to determine number of days in year?

    Thanks, I know this was long.

  2. #2
    Join Date
    Nov 2002
    Location
    Ohio
    Posts
    90
    Hi Elomon,

    1. No, you're not crazy.

    2. TBA

    3. TBA

    What language are you using to access DB2 and calculate the difference?

    Good Luck,
    Bruce Baasch

  3. #3
    Join Date
    Oct 2002
    Posts
    34
    I'm using Visual Basic for the proggy that connects to the db. I can calculate the differences in VB but it's just as messy, need to find which month was part of the MonthDiff, find #of days, etc.

    I can do the VB part ok. Can this not be done in DB2 T-SQL?
    thanks

  4. #4
    Join Date
    Nov 2002
    Location
    Ohio
    Posts
    90
    I don't think DB2/T-SQL is going to support the calculations without a lot of trouble.

    It's probably easier to do it in VB or create a class module that handles date calculations if you're going to do this often.

    2. What month is part of the difference: Val(Format(Date1,"mm/dd/yy") will give you the month.

    3. How many days in the year:

    Select Case Year Mod 4
    Case 1 to 3
    Days = 365
    Case Else
    Select Case Year Mod 400
    Case 1 to 399
    Days = 366
    Case Else
    Days = 365
    End Select
    End Select

    Good Luck,
    Bruce Baasch

  5. #5
    Join Date
    Oct 2002
    Posts
    34
    Thanks Bruce

  6. #6
    Join Date
    Jun 2002
    Location
    UK
    Posts
    525
    The DAYS function multiplied by 24 will give you the number of hours for completed days since 1 January 0001 (incl). MIDNIGHT_SECONDS / 3600 will give you the number of hours expired in the current day.

    Check this thread for something similar:

    [url]http://dbforums.com/showthread.php?threadid=559446
    Last edited by Damian Ibbotson; 12-11-02 at 15:07.

  7. #7
    Join Date
    Oct 2002
    Posts
    34
    I must be doing something wrong here:

    select Days(row_added_dttm)-Days(current timestamp) * 24
    and
    select Days(current timestamp)-Days(current timestamp) *24

    and I keep getting -16815997. Not sure what this number is but it's not right, the field is about a month old.

    Thanks for the idea, can u help me flesh it out?

  8. #8
    Join Date
    Feb 2002
    Location
    Germany
    Posts
    141
    try
    select (Days(row_added_dttm)-Days(current timestamp)) * 24
    Rodney Krick

  9. #9
    Join Date
    Jun 2002
    Location
    UK
    Posts
    525
    For completeness...

    ((DAYS(CURRENT TIMESTAMP)*24 )+(MIDNIGHT_SECONDS(CURRENT TIMESTAMP)/3600 ))
    -
    ((DAYS('YOURDATE')*24)+(MIDNIGHT_SECONDS('YOURDATE ')/3600))

  10. #10
    Join Date
    Oct 2002
    Posts
    34
    The query now looks like this:

    select current timestamp As curStamp,row_Added_Dttm as MyDate,
    ((DAYS(CURRENT TIMESTAMP)*24 )+(MIDNIGHT_SECONDS(CURRENT TIMESTAMP)/3600 ))
    -
    ((DAYS(ROW_ADDED_DTTM)*24)+(MIDNIGHT_SECONDS(ROW_A DDED_DTTM)/3600)) As HourDiff

    And I get:
    curStamp = 12/12/2002 8:08:55 AM
    myDate = 10/10/2002 10:14:18 AM
    Hours Diff = 1510

    I confirmed this result using VB:
    MsgBox (DateDiff("h", "10/10/2002 10:14:18 AM", "12/12/2002 8:08:55 AM"))

    and got 1510! So it looks like this works great!

    How could I extract the remaining minutes? The requirement is to show hours and minutes difference.
    Thank you all for all the help so far, esp. Damian
    Last edited by elomon; 12-12-02 at 09:20.

  11. #11
    Join Date
    Jun 2002
    Location
    UK
    Posts
    525
    Hmmm...

    You want the modulus of the midnight_seconds calculation and divide this by 60 to get the minutes.

    i.e.

    (mod(midnight_seconds(current timestamp),3600)/60

    I think that would do. Something's telling me that there's more to it than that but I'm not sure if there is.

    I'll have a think about it. Post on this forum to let me know how you get on and I'll add to it myself if my brain decides to start functioning properly.

  12. #12
    Join Date
    Jun 2002
    Location
    UK
    Posts
    525
    Okay, so we do have a problem...

    The code only accounts for completed hours. If you compared 11:59 with 12:01, you would return a difference of 1 hour.

    I would suggest that you calculate the difference in seconds and work back from that to get hours and minutes.

    e.g.

    INT(
    ((DOUBLE(DAYS(CURRENT TIMESTAMP)) * 86400) + MIDNIGHT_SECONDS(CURRENT TIMESTAMP))
    -
    ((DOUBLE(DAYS('YOURDATE')) * 86400) + MIDNIGHT_SECONDS('YOURDATE'))
    )

  13. #13
    Join Date
    Oct 2002
    Posts
    34
    How about this:

    SELECT
    decimal(double(((DAYS(current timestamp) - DAYS(row_added_dttm)) * 86400) + (MIDNIGHT_SECONDS(current_timestamp) - MIDNIGHT_SECONDS(row_added_dttm))) /3600,8,2) as HourDiff

    This returns hours.decimal like 1510.33, which would be 1510 hours and .33/hr, just multiply .33 * 60 = minutes.

    Does this seem legitimate?

  14. #14
    Join Date
    Jun 2002
    Location
    UK
    Posts
    525
    Looks good to me.

    You could get the minutes and seconds seperately in one SQL as below:

    INT(
    ((DOUBLE(DAYS(CURRENT TIMESTAMP)) * 86400) + MIDNIGHT_SECONDS(CURRENT TIMESTAMP)) -
    ((DOUBLE(DAYS('YOURDATE')) * 86400) + MIDNIGHT_SECONDS('YOURDATE'))
    )/3600
    ,
    MOD(INT(
    ((DOUBLE(DAYS(CURRENT TIMESTAMP)) * 86400) + MIDNIGHT_SECONDS(CURRENT TIMESTAMP)) -
    ((DOUBLE(DAYS('YOURDATE')) * 86400) + MIDNIGHT_SECONDS('YOURDATE'))
    ),3600)/60

    Admittedly, not quite as tidy as your example but you wouldn't need to perform the additional calculation.

  15. #15
    Join Date
    Oct 2002
    Posts
    34

    Thumbs up

    Right!

    Thank you very much for the help, I think I can finally lay this one to rest.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •