--- In firebird-support@yahoogroups.com, "Jeff Lynn" <jmlynn@...> wrote:

>
> I watched all these interchange regarding how to compute elased time
> as well as truncation with interest.
>
> I understand the need of the posts trying to use only SQL solutions.
> But my own past experiences that products migrate from database to
> database usually with many different non-SQL 92 standards functions, I
> tends to mix SQL and programming languages whenever I generate reports.
>
> Using a programming language to invoke ODBC, JDBC or native CLI
> (call-level interface) to select and iterate thru a result set, I then
> use programming language such as VB, Delphi, C++ or Java to do the
> computation, be it elapsed time calculation, data truncations, mapping
> and transformation, and then programmatically spit out the PDF, Excl
> or Crystal Report file.
>
> This way, I can take full advantage of SQL-92 conformance as well as
> the full power of a programming language to generate a very fancy
> reports using open-source libraries that do PDF, Excel, Word, with

charts.

>
> So if you have resource to add programming to your reporting, you can
> take advantage of the full power of both worlds.

I think the issue at hand is that some folk seem to want to perform
data formatting on the database server. Calculating the elapsed time
between two timestamps is trivial

[Elapsed time in days] = [End Timestamp] - [Start Timestamp];

The result returned will be the number of days between them, which
will include fractions of a day. For example, a 6 hour gap would be
returned as 0.25

Our software deals with time based data frequently, so we don't but
the onus on the server to do the formatting required. The math is
pretty simple though, and depends on the grain you require. We work in
minutes, so for us the algorithm looks like this.

We multiply the resulting number by 1440 in the select as we are not
interested in the second components, if you want seconds then it is
86400 that you must multiply by.

select Cast((1440 * (EndTime - StartTime)) as Integer) as Length
from .....

Assume we have 1814 minutes returned.

The hour component is

H = Value div 60;
M = Value mod 60;

Then you just need to possibly pad M with a leading 0 if it is less
than 10 and concatinate the strings.

I am sure this is possible using pure PSQL, but frankly why bother?
Let the database store and retrieve data, and your client take care of
the formatting as appropriate for a situation.

Adam