RE: SQL timestamp duration can't be trusted?

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


_______________________________________________
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.