Tags: calculate, current, database, db2, field, mysql, newb, oracle, sql, time, totally

Newb ?: Calculate Hour/Minute Difference

On Database » DB2

5,518 words with 6 Comments; publish: Fri, 30 Nov 2007 11:44:00 GMT; (25063.48, « »)

Totally new to DB2, so this is probably an easy one.

I need to calculate the Hour and Minute difference between a field and the current time.

I tried:

select (current time - fieldDate) from mytable

But got "The data type, length or value of argument "2" of routine "-" is incorrect.

Any help appreciated.

All Comments

Leave a comment...

  • 6 Comments
    • Heys,

      Whats the Datatype of your field?

      if it is Date you show use "Select (current date - fieldDate) from...", although the Date datatype wont hold the hours/minutes, only the date.

      You could also be using a Timestamp field, use "CURRENT TIMESTAMP" instead of "CURRENT TIME" and select the part of the result that matters to you.

      that could be the reason why youre receiving such an error.

      HTH

      Fernando.

      === Original Words ===

      elomon

      Totally new to DB2, so this is probably an easy one.

      I need to calculate the Hour and Minute difference between a field and the current time.

      I tried:

      select (current time - fieldDate) from mytable

      But got "The data type, length or value of argument "2" of routine "-" is incorrect.

      Any help appreciated.

      #1; Tue, 11 Dec 2007 17:42:00 GMT
    • It's a timestamp, so I tried:

      select (CURRENT TIMESTAMP - fieldDate) from...

      and got a crazy number: 127002523.507551

      So I figure that's the TimeStamp difference, so I tried to extract the minutes:

      select Minute(CURRENT TIMESTAMP - fieldDate) from ...

      And I got 26, which is totally wrong:

      fieldDate=2002-10-08 10:50:01.0

      Current TimeStamp=2002-12-04 11:16:51.738328

      So this difference is almost a month, not 26 minutes; a month of minutes would be daysInMonthx24Hoursx60Minutes, so 26 can't be correct.

      Am I extracting the minutes incorrectly? I want the total hours and total minutes difference between the two dates.

      Thanks for the help!

      #2; Tue, 11 Dec 2007 17:43:00 GMT
    • Heys,

      Actually, the command you tried (MINUTE) just extracts the "minute" part of the timestamp and not the timestamp converted to minutes.

      I found a function called TIMESTAMPDIFF.

      you could call it to return the results in the time unit you want. Ill summarize the syntax for you. In case you need further info, I suggest you look for it at the documentation.

      but here it is:

      TIMESTAMPDIFF(integer u, char(t1-t2)) -> integer

      *where t1 and t2 are timestamp fields

      the first argument, u, is the time unit you want for the return.

      256=years, 128=quarters, 64=months, 32=weeks, 16=days, 8=hours, 4=minutes, 2=seconds and 1=microseconds.

      and it returns you an integer as result of the difference.

      Try that and see if it works.

      HTH

      Fernando

      === Original Words ===

      elomon

      It's a timestamp, so I tried:

      select (CURRENT TIMESTAMP - fieldDate) from...

      and got a crazy number: 127002523.507551

      So I figure that's the TimeStamp difference, so I tried to extract the minutes:

      select Minute(CURRENT TIMESTAMP - fieldDate) from ...

      And I got 26, which is totally wrong:

      fieldDate=2002-10-08 10:50:01.0

      Current TimeStamp=2002-12-04 11:16:51.738328

      So this difference is almost a month, not 26 minutes; a month of minutes would be daysInMonthx24Hoursx60Minutes, so 26 can't be correct.

      Am I extracting the minutes incorrectly? I want the total hours and total minutes difference between the two dates.

      Thanks for the help!

      #3; Tue, 11 Dec 2007 17:44:00 GMT
    • I saw TimeStampDiff at the IBM Website:

      http://www7b.boulder.ibm.com/dmdd/library/techarticle/0211yip/0211yip3.html

      The article states TimeStampDiff is an approximation because it 'assumes' 30 day months and does not calculate for leap years. I can live with leap years but the 30 day months is unacceptable. I have no idea why IBM would build an inaccurate function into the db but there is probably a reason...

      Here's what it looks like I'm stuck with:

      select

      Day(CURRENT TIMESTAMP - fieldDate)As DayDiff,

      Hour(CURRENT TIMESTAMP - fieldDate) As HourDiff,

      Minute(CURRENT TIMESTAMP - fieldDate) As MinDiff

      from

      This gives me Days,Hours and minutes difference. Then I'll have to run another calculation to convert it all to hours/minutes. Seems like a waste - 3 calculation, 3 function calls; in SQL server this is one function:

      select dateDiff(mi,Date1,Date2) gives me the total minutes difference.

      Thanks for the help. I'm just disappointed with DB2 on this matter.

      #4; Tue, 11 Dec 2007 17:45:00 GMT
    • Hi Elomon,

      You were actually on the right track in your Current Timestamp - fieldDate. It didn't give you a crazy number, you just didn't know how to interpret it.

      Your output 127002523.507551 is read as 1 month 27 days 25 min 23 sec and 507551 milsec. So you get your expected almost 2 (not one, right ?) months difference between the timestamp and your date field.

      I recommend that you read on the topic Timestamp Duration.

      HTH,

      Oliver

      #5; Tue, 11 Dec 2007 17:46:00 GMT
    • I'm writing off TimeStampDiff due to it's inherent inaccuracy. Since the aging I'm calculating may be over a month and TimeStamp 'assumes' 30 day months, the results would be incorrect.

      I guess TimeStampDiff is only useful for calculating differences when they occur within hours of each other on the same day.

      I'm new to DB2 so maybe this is normal for the rest of you but it seems really, really weird to build an inherently inaccurate function into a database.

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