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
}>
<<sniped>>
}> Much Appreciated
}> Bill Katelis
}> bkatelis@vibupls1.telecom.com.au
}
<sniped.>
}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
}
}
As I undestood your column duration is an "interval hour to second" ...
Try this:
create procedure intvl2sec (in_intvl interval hour to second)
returning interval second(9) to second;
define nbr_scnds interval second(9) to second;
let nbr_scnds = in_intvl - "00:00:00";
return nbr_scnds;
end procedure
and do the following select
select ... intvl2sec(duration) from ...
Don't forget ... another way is doing that via ESQL/C
HTH ...
Roberto Trevisol
rtrevisol@bamerindus.com.br