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.
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.
select (current time - fieldDate) from mytable
But got "The data type, length or value of argument "2" of routine "-" is incorrect.
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.
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:
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!
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:
Day(CURRENT TIMESTAMP - fieldDate)As DayDiff,
Hour(CURRENT TIMESTAMP - fieldDate) As HourDiff,
Minute(CURRENT TIMESTAMP - fieldDate) As MinDiff
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.
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.
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.