RE: SQL timestamp duration can't be trusted? -- MIDRANGE-L

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