Re: Sql time query
Katelis, Bill wrote:
>
> Platform: Sun sparc 20, sunos 5.3
> Engine: 5.04.uc2
> isql: 411.uc1
>
> Problem: Trying to add duration columns and convert to seconds using sql.
> Duration Event
> 01:05:56 1
> 01:00:23 1
> 00:12:34 1
>
> I can add the duration column using 4gl (converting to char, splitting
> values etc) but I am trying to increase the speed of my application.
> I have tried to do the following:
> select extend(duration,hour to hour)*3600,
> extend(duration, minute to minute)*60,
> extend(duration, second to second)
> from table
> where event = 1
> but it connot convert between the data types. I guess my question is, Is
> there a way to convert the data type resulting from an extend call to an
> integer,
> OR, is there another way of doing it?
>
> Much Appreciated
> Bill Katelis
> bkatelis@vibupls1.telecom.com.au
Hi Bill,
as I see it the only way to do this by using sql is to use a Stored
Procedure.I am not sure though if this method will be faster than doing it
with 4GL but your SQL statement surely looks smarter! :-)
Here is a code example how this SP can look like
CREATE PROCEDURE dt2sec(p_dt DATETIME HOUR TO SECOND)
RETURNING INTEGER ;
DEFINE l_hour DATETIME HOUR TO HOUR;
DEFINE r_hour INTEGER;
DEFINE s_hour CHAR(2);
DEFINE l_min DATETIME MINUTE TO MINUTE;
DEFINE r_min INTEGER;
DEFINE s_min CHAR(2);
DEFINE l_sec DATETIME SECOND TO SECOND;
DEFINE r_sec INTEGER;
DEFINE s_sec CHAR(2);
LET l_hour = EXTEND (p_dt, HOUR TO HOUR);
LET s_hour = l_hour;
LET r_hour = s_hour;
LET r_hour = r_hour * 3600;
LET l_min = EXTEND (p_dt, MINUTE TO MINUTE);
LET s_min = l_min;
LET r_min = s_min;
LET r_min = r_min * 60;
LET l_sec = EXTEND (p_dt, SECOND TO SECOND);
LET s_sec = l_sec;
LET r_sec = s_sec;
LET r_sec = r_sec + r_min + r_hour;
RETURN r_sec;
END PROCEDURE;
Now you can issue a statement like this
select sum(dt2sec(duration))
from table
where event = 1
Regards
Tolis
+---------------------------------------------------------------------+
| V+K Relational Solutions email: tvarnas@compulink.gr |
| Deligiorgi 26 tvarnas@orbit.de |
| 546 42 Thessaloniki Voice: (30) 31 820270 |
| Greece Fax: (30) 31 865463 |
+---------------------------------------------------------------------+