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

1. Registered User
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. Registered User
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,

3. Registered User
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. Registered User
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,

5. Registered User
Join Date
Oct 2002
Posts
34
Thanks Bruce

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:

Last edited by Damian Ibbotson; 12-11-02 at 15:07.

7. Registered User
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. Registered User
Join Date
Feb 2002
Location
Germany
Posts
141
try
select (Days(row_added_dttm)-Days(current timestamp)) * 24

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

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.

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. Registered User
Join Date
Oct 2002
Posts
34

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?

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. Registered User
Join Date
Oct 2002
Posts
34
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
•