Hi!

I want to make a function that make diff. between two timestamps and show this value controlled by third param (in: 1. hours, 2.minutes, 3. seconds)...

But I blocked by at first that I cannot make operation on two timestamps... The demo...

SCRIPT
BEGIN
  declare t1, t2, t3 timestamp;
  declare it interval msecond;
  set t1 = current_timestamp + interval '1' hour;
  set t2 = current_timestamp;
  set it = t1 - t2; // THIS IS...
or
  set t3 = t1 - t2; // THIS IS...

END

In SQL I also failed because I cannot convert interval to anything...

select cast(timestamp '01-01-01 02:02:02' - interval '0' minute as float)
from configuration.databases
range 1 to 1

or
select cast(current_timestamp as float)
from configuration.databases
range 1 to 1

except varchar...

What I do wrong?

Thanks:
  dd

Dear Laszlo

Here is a script I have written which works. I hope it is useful. It doesn't do exactly the same thing as your, but it is close.

SCRIPT
BEGIN
 declare t1, t2 timestamp;
 declare it1, it2 interval msecond;
 declare crsr cursor for stmt;

PREPARE Stmt FROM
'SELECT
 DateCreated,
 DateSent,
 DateCreated - DateSent as "TimeTaken"

FROM Communications WHERE DateCreated >= Current_Date - INTERVAL ''2'' DAY

ORDER BY TimeTaken DESC';
                                                                  
OPEN Crsr;
FETCH FIRST FROM Crsr('DateCreated') INTO t1;
FETCH FIRST FROM Crsr('DateSent') INTO t2;
FETCH FIRST FROM Crsr('TimeTaken') INTO it1;

SET it2 = CAST(t1 - t2 as INTERVAL msecond);

END

AdamBrett wrote:
>SET it2 = CAST(t1 - t2 as INTERVAL msecond);
>

Dear Brett!

This was the seed of my problem. Thanks!

Test:
select cast(current_timestamp() + interval '1' minute - current_timestamp as interval second)
from configuration.databases
range 1 to 1

Good work to you:
 dd

Laszlo


CREATE FUNCTION "GapTime" (IN "Time1" TIMESTAMP, IN "Time2" TIMESTAMP, IN "Control" INTEGER)
RETURNS VARCHAR(50)
BEGIN
DECLARE Interim FLOAT;
DECLARE Outbound VARCHAR;
                     
SET Interim = ABS(CAST(((Time2 - Time1) MSECOND) AS INTEGER)) / 1000;
CASE
 WHEN Control = 2 THEN
  SET Interim = Interim / (60); -- Minutes
 WHEN Control = 3 THEN
  SET Interim = Interim / (60*60); -- Hours
END CASE;

CASE
 WHEN Control = 2 THEN
  SET Outbound = ' Minutes';
 WHEN Control = 3 THEN
  SET Outbound = ' Hours';
 ELSE SET Outbound = ' Seconds';
END CASE;

SET Outbound = CAST(CAST(Interim AS INTEGER) AS VARCHAR )+Outbound;
RETURN Outbound;

END

Roy Lambert