Tags: calculate, calculating, current, database, date, db2, differences, field, mysql, oracle, sql, time, timestamp

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

On Database » DB2

6,918 words with 16 Comments; publish: Fri, 30 Nov 2007 22:23:00 GMT; (25078.13, « »)

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/library/techarticle/0211yip/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.

All Comments

Leave a comment...

  • 16 Comments
    • 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,

      #1; Tue, 11 Dec 2007 17:42:00 GMT
    • 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

      #2; Tue, 11 Dec 2007 17:43:00 GMT
    • 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,

      #3; Tue, 11 Dec 2007 17:44:00 GMT
    • Thanks Bruce
      #4; Tue, 11 Dec 2007 17:45:00 GMT
    • 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

      #5; Tue, 11 Dec 2007 17:46:00 GMT
    • 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?

      #6; Tue, 11 Dec 2007 17:47:00 GMT
    • try

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

      #7; Tue, 11 Dec 2007 17:48:00 GMT
    • For completeness...

      ((DAYS(CURRENT TIMESTAMP)*24 )+(MIDNIGHT_SECONDS(CURRENT TIMESTAMP)/3600 ))

      -

      ((DAYS('YOURDATE')*24)+(MIDNIGHT_SECONDS('YOURDATE ')/3600))

      #8; Tue, 11 Dec 2007 17:49:00 GMT
    • 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

      #9; Tue, 11 Dec 2007 17:50:00 GMT
    • 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.

      #10; Tue, 11 Dec 2007 17:51:00 GMT
    • 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'))

      )

      #11; Tue, 11 Dec 2007 17:52:00 GMT
    • 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?

      #12; Tue, 11 Dec 2007 17:53:00 GMT
    • 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.

      #13; Tue, 11 Dec 2007 17:54:00 GMT
    • Right!

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

      #14; Tue, 11 Dec 2007 17:55:00 GMT
    • Phew!

      I meant hours and minutes (not minutes and seconds) in my previous post if you hadn't already spotted my deliberate mistake ;-)

      #15; Tue, 11 Dec 2007 17:56:00 GMT
    • I had to go back and re-read it to see the mistake - I plugged your query into my table, saw the results and started dancing.

      Very, very sweet.

      #16; Tue, 11 Dec 2007 17:57:00 GMT