Login ProductsSalesSupportDownloadsAbout |
Home » Technical Support » ElevateDB Technical Support » Support Forums » ElevateDB SQL » View Thread |
Messages 1 to 4 of 4 total |
Get difference between two time values |
Tue, Feb 21 2012 5:40 AM | Permanent Link |
Laszlo Szabo | 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 |
Tue, Feb 21 2012 5:58 AM | Permanent Link |
AdamBrett Fullwell Mill | 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 |
Tue, Feb 21 2012 6:29 AM | Permanent Link |
Laszlo Szabo | 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 |
Tue, Feb 21 2012 7:05 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | 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 |
This web page was last updated on Sunday, May 19, 2024 at 08:46 AM | Privacy PolicySite Map © 2024 Elevate Software, Inc. All Rights Reserved Questions or comments ? E-mail us at info@elevatesoft.com |