At 19:40 2/10/2008, you wrote:

>Hi!
>
>What is the proper way to calculate a time difference in minutes
>(seconds, hours, years...) between two timestamps? I am using Firebird
>1.5 and I would very much like to manage this task just by using PL/SQL,
>without resorting to UDFs.
>
>Originally I wrote this procedure:
>
>CREATE PROCEDURE diff (time1 TIMESTAMP, time2 TIMESTAMP)
>RETURNS (min_diff INTEGER)
>AS BEGIN
> time1 = time1-time2;
> min_diff = time1*60*24;
> SUSPEND;
>END
>
>It does not work, complains when I try to compile it:
> "SQL error code = -607
> Array/BLOB/DATE data types not allowed in arithmetic"

Of course it doesn't work! PSQL is not BASIC, where you can switch a variable's data type at will!

time1 is a timestamp. When you subtract a timestamp from a timestamp, the result is numeric(18,9), in days. You cannot make time1 accept a numeric(18,9) or a string or anything but a compatible date/time type.

>So I modified it with CAST to DOUBLE PRECISION:
>
>...
> min_diff = CAST(time1 AS DOUBLE PRECISION)*60*24;
>...
>
>This compiles OK, but when I try to execute the procedure:
> SELECT * FROM diff(field1, field2)
>I get an error message:
> "conversion error from string "0.013194444""

That is not a solution. Create a numeric(18,9) variable for the result of the timestamp difference and then you will have a type on which arithmetic can operate.

./heLen