Subject Re: [firebird-support] time data type and elapsed
Author Helen Borrie
At 07:49 PM 28/08/2003 +0000, you wrote:
>This is a question about calculating elapsed time using two fields
>of type time, when the later once is past midnight.
>I've recently converted tables from Paradox, where I used datetime
>data types. In the firebird tables, I used date and time datatypes,
>thinking there might be some advantage.
>I want to write a stored procedure that calculates the elapsed time.
>I have what amounts to start_time and end_time fields. It seems that
>I need to subtract the milliseconds of the start_time field from the
>milliseconds in a day, and then add to the result the milliseconds
>of the end time, then convert to hours (floating point).
>But I don't know how to do that. I've tried variations on cast() but
>can't get them to work.

No, you don't need a SP or any of that decoding stuff. Simply subtract the
earlier date from the later date to get a double precision number which is
time elapsed in days. It is just an ordinary expression, so you can select
it as a computed output column, fetch it into a variable in a SP, use it to
update a column, etc. Works with DATE, TIME or TIMESTAMP types.