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

× The internal search function is temporarily non-functional. The current search engine is no longer viable and we are researching alternatives.
As a stop gap measure, we are using Google's custom search engine service.
If you know of an easy to use, open source, search engine ... please contact support@midrange.com.

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 mailing list archive is Copyright 1997-2025 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].

Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.