RE: SQL timestamp duration can't be trusted?

[Vern Hamberg <vhamberg@xxxxxxxxxxxxxxxxxxxxxxxxx>]

Hi Eric

The formula is fine for shorter time durations (less than a month). day() 
is a value between 1 and 31, and that's fine for less than 1 month. Once 
you go to one month or beyond, you cannot rely on it, because both 6/15/02 
- 5/15/02 and 5/15/02 - 4/15/02 give you 1 month duration. Trouble is, one 
is 30 days, the other is 31 days.

Interesting, and a hidden gotcha. There is some discussion of things to 
watch for when adding durations to dates. E.g.,

select date('2003-1-31') + 1 month - 1 month from qsqptabl

returns '01/28/03' in MDY format.

Vern

At 10:51 AM 7/15/2003 -0500, you wrote:
> Hi Vern,
>
> Try this on for size and see if it works....
> SELECT ERTIMEST,
>   (   day(current_timestamp - ERTIMEST)*(24*60*60)) +
>   (  hour(current_timestamp - ERTIMEST)*(60*60)) +
>   (minute(current_timestamp - ERTIMEST)*60) +
>    second(current_timestamp - ERTIMEST) as dursecs
> FROM vxperrgeo
>
> Eric DeLong
> Sally Beauty Company
> MIS-Project Manager (BSG)
> 940-898-7863 or ext. 1863
>
>
>
> -----Original Message-----
> From: Vern Hamberg [mailto:vhamberg@xxxxxxxxxxxxxxxxxxxxxxxxx]
> Sent: Tuesday, July 15, 2003 9:35 AM
> To: Midrange Systems Technical Discussion
> Subject: SQL timestamp duration can't be trusted?
>
>
> I want to get the duration in seconds between 2 timestamps in SQL. I have
> looked at the duration from subtracting 2 timestamps. But this does not
> seem a reliable way to do this, when leap years Feb 29 are involved. Or,
> actually, when relatively long periods are involved - 2 months can be
> anything from 59 -62 days.
>
> The result of subtracting 2 dates is a DEC(8,0) formatted as yyyymmdd.
>
> SELECT date('2004-02-29') - date('2003-02-28') FROM qsqptabl
>     gives 00010001 (1 year + 1 day)
>
> SELECT date('2005-02-28') - date('2004-02-28') FROM qsqptabl
>     gives 00010000 (1 year)
>
> SELECT date('2005-02-28') - date('2003-02-28') FROM qsqptabl
>     gives 00020000 (2 years, no accounting for the extra day)
>
> SELECT date('2005-02-28') - date('2004-02-29') FROM qsqptabl
>     gives 00001128 (11 months + 28 days)
>
> Makes me wonder whether durations in RPG suffer from the same problem.
>
> Is there a way (without writing a UDF) to get the exact seconds between
> timestamps, in SQL? In an HLL I could use the CEESECS API.
>
> Thanks
>
> Vern