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

[vhamberg@xxxxxxxxxxx]

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.