Re: AW: SQL - Calculate the number of hours between 2 timestamps

BTW, timestampdiff is not an iSeries exclusive extension - it is part of 
several SQL implementations. Its behavior in other scenarios is probably 
similar, although there is the chance that one vendor does it better than 
another. Is it part of any of the SQL standard specifications?

Vern

-------------- Original message -------------- 
From: rob@xxxxxxxxx 

I agree, it sucks. However, when you think about what you would do with a 
calculating the difference between two timestamps, doesn't the decimal 
answer work "good enough"? The OP wanted to know if the duration was 
greater than 8 hours. We posted something like select ts1-ts2 from ... 
and got a number. And if that number was greater than 80000 then it was 
greater than 8 hours. 

If is not "good enough" and you have a business reason, then see if you 
can find a standard, preferably ISO, that says otherwise. IBM won't 
change timestampdiff because someone is probably using it and is counting 
on it's anomalies. But if you have a strong enough business case, and 
submit a DCR then perhaps they'll come out with timestampdiffiso. Don't 
know what the odds of them ever creating one that says timestampdiffMS for 
MicroSoft. Legal reasons, animosity, and possible confusion with 
milliseconds might intervene. 

Rob Berendt 
-- 
Group Dekko Services, LLC 
Dept 01.073 
PO Box 2000 
Dock 108 
6928N 400E 
Kendallville, IN 46755 
http://www.dekko.com 





Loyd Goodbar 
Sent by: midrange-l-bounces@xxxxxxxxxxxx 
06/27/2006 09:27 PM 
Please respond to 
Midrange Systems Technical Discussion 


To 
Midrange Systems Technical Discussion 
cc 

Subject 
Re: AW: SQL - Calculate the number of hours between 2 timestamps 






Yeah, I saw that too. Frankly, that sucks. 

Does anyone know if this just an iSeries DB2 thing, or does it also 
afflict the "other" DB2? I mean, ILE RPG and even SQL Server can 
calculate accurately durations between two date/time stamps. 

--lg 

On Jun 27, 2006, at 10:59 AM, rob@xxxxxxxxx wrote: 

Study that timestampdiff bif carefully. A lot of assumptions are 
made. 
Which may, or may not, matter for your situation. Straight from 
the book: 

The following assumptions may be used in estimating the difference: 

there are 365 days in a year 
there are 30 days in a month 
there are 24 hours in a day 
there are 60 minutes in an hour 
there are 60 seconds in a minute 
These assumptions are used when converting the information in the 
second 
argument, which is a timestamp duration, to the interval type 
specified in 
the first argument. The returned estimate may vary by a number of 
days. 
For example, if the number of days (interval 16) is requested for a 
difference in timestamps for '1997-03-01-00.00.00' and 
'1997-02-01-00.00.00', the result is 30. This is because the 
difference 
between the timestamps is 1 month so the assumption of 30 days in a 
month 
applies. 


Rob Berendt 

-- 
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing 
list 
To post a message email: MIDRANGE-L@xxxxxxxxxxxx 
To subscribe, unsubscribe, or change list options, 
visit: http://lists.midrange.com/mailman/listinfo/midrange-l 
or email: MIDRANGE-L-request@xxxxxxxxxxxx 
Before posting, please take a moment to review the archives 
at http://archive.midrange.com/midrange-l. 


-- 
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list 
To post a message email: MIDRANGE-L@xxxxxxxxxxxx 
To subscribe, unsubscribe, or change list options, 
visit: http://lists.midrange.com/mailman/listinfo/midrange-l 
or email: MIDRANGE-L-request@xxxxxxxxxxxx 
Before posting, please take a moment to review the archives 
at http://archive.midrange.com/midrange-l.