Results 1 to 7 of 7
  1. #1
    Join Date
    Oct 2002
    Posts
    34

    Unanswered: Newb ?: Calculate Hour/Minute Difference

    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.

  2. #2
    Join Date
    Nov 2002
    Location
    Rio de Janeiro - Brazil
    Posts
    78

    Arrow Re: Newb ?: Calculate Hour/Minute Difference

    Heys,

    What´s the Datatype of your field?
    if it is Date you show use "Select (current date - fieldDate) from...", although the Date datatype won´t 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 you´re receiving such an error.

    HTH
    Fernando.

    Originally posted by 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.

  3. #3
    Join Date
    Oct 2002
    Posts
    34
    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!

  4. #4
    Join Date
    Nov 2002
    Location
    Rio de Janeiro - Brazil
    Posts
    78
    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. I´ll 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

    Originally posted by 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!

  5. #5
    Join Date
    Oct 2002
    Posts
    34
    I saw TimeStampDiff at the IBM Website:
    http://www7b.boulder.ibm.com/dmdd/li.../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.

  6. #6
    Join Date
    Jan 2002
    Location
    Manila, Philippines
    Posts
    71

    Timestamp Duration

    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

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

Posting Permissions

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