converting HH:MM to a decimal
I have 3 pairs of time in/out (24 our clock) that I need to calculate the decimal equivalent of the total time worked for that day. example: time in/out 08:00 10:00, 10:15 12:45, 13:45 16:50 if I use a simple select of time_out 'time_ in I get 2:00, 2:30, 3:10 respectfully, or 7:35 total hours. That is 7.58 in decimal. Now how do I change the total hours of 7:35 to a decimal of 7.58 in a single SQL statement? Or will I have to write a proc that does this?
John David Adamski wrote:
> I have 3 pairs of time in/out (24 our clock) that I need to calculate
> the decimal equivalent of the total time worked for that day.
>
> example: time in/out 08:00 10:00, 10:15 12:45, 13:45 16:50
> if I use a simple select of time_out 'time_ in I get 2:00,
> 2:30, 3:10 respectfully, or 7:35 total hours.
> That is 7.58 in decimal.
>
> Now how do I change the total hours of 7:35 to a decimal of 7.58 in a
> single SQL statement? Or will I have to write a proc that does this?
You will have to obtain or write a procedure to do it; there isn't any
way to coerce an interval into a decimal without using a stored procedure.
create procedure ivhm_decimal(i interval hour(7) to minute)
returning decimal; define im interval minute(9) to minute;
define str char(10);
define d decimal;
let im = i;
let str = im;
let d = str / 60.0;
return d;
end procedure;
Untested. You have to go through a string. You might return ROUND(d,
2) to get 2 decimal places of hours. I'm not sure about the first
assignment (let im = i); you might be able to alter the type in the
signature to interval minute(9) to minute and still invoke it with:
ivhm_decimal(t_out1 - t_in1) +
ivhm_decimal(t_out2 - t_in2) +
ivhm_decimal(t_out3 - t_in3)
Or equivalents. Also poke around the IIUG archives - there's code
there to do the job in ESQL/C (iv_hours for your case). It could be
adapted to work in SPL instead of ESQL/C.
--
Jonathan Leffler #include <disclaimer.h>
Email: jleffler@earthlink.net, jleffler@us.ibm.com
Guardian of DBD::Informix v2003.04 -- http://dbi.perl.org/
Jonathan Leffler wrote:
> John David Adamski wrote:
>> I have 3 pairs of time in/out (24 our clock) that I need to calculate
>> the decimal equivalent of the total time worked for that day.
>>
>> example: time in/out 08:00 10:00, 10:15 12:45, 13:45 16:50
>> if I use a simple select of time_out 'time_ in I get 2:00,
>> 2:30, 3:10 respectfully, or 7:35 total hours.
>> That is 7.58 in decimal.
>>
>> Now how do I change the total hours of 7:35 to a decimal of 7.58 in a
>> single SQL statement? Or will I have to write a proc that does this?
>
> You will have to obtain or write a procedure to do it; there isn't any
> way to coerce an interval into a decimal without using a stored procedure.
Correction - a procedure is the neatest way to do it. You should be
able to do a sequence of casts (assuming IDS 9.x) that will achieve
the same result:
ROUND((t_out1 - t_in1)::INTERVAL MINUTE(9) TO
MINUTE::CHAR(20)::DECIMAL(16) / 60.0, 2)
But having even one of those in a select statement doesn't bear much
thinking about, let alone three (or using it as an argument to SUM).
> create procedure ivhm_decimal(i interval hour(7) to minute)
> returning decimal;> define im interval minute(9) to minute;
> define str char(10);
> define d decimal;
> let im = i;
> let str = im;
> let d = str / 60.0;
> return d;
> end procedure;
>
> Untested. You have to go through a string. You might return ROUND(d,
> 2) to get 2 decimal places of hours. I'm not sure about the first
> assignment (let im = i); [...]
--
Jonathan Leffler #include <disclaimer.h>
Email: jleffler@earthlink.net, jleffler@us.ibm.com
Guardian of DBD::Informix v2003.04 -- http://dbi.perl.org/