converting HH:MM to a decimal

converting HH:MM to a decimal

Post by John David Adams » Wed, 09 Jul 2003 01:28:49



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?

 
 
 

converting HH:MM to a decimal

Post by Jonathan Leffle » Wed, 09 Jul 2003 17:36:49



> 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>

Guardian of DBD::Informix v2003.04 -- http://dbi.perl.org/

 
 
 

converting HH:MM to a decimal

Post by Jonathan Leffle » Wed, 09 Jul 2003 23:18:42




>> 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).

Quote:> 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>

Guardian of DBD::Informix v2003.04 -- http://dbi.perl.org/
 
 
 

1. Convert mm/dd/yyyy hh:mm:ss AM/PM to mm/dd/yyyy format

Just store the date with time in the table and create the date w/o time when
you need it.


I would like to convert mm/dd/yyyy hh:mm:ss AM/PM format into mm/dd/yyyy
format
within the same table.  So I can have 'date w/ time' field and 'date w/o
time'
field in the same table.  Please help.  Thanks.

2. select middle records

3. Converting seconds in hh:mm:ss

4. BCP PROBLEMS

5. Convert seconds to hh:mm:ss

6. UDF - How to reference a field within the IF EXISTS (SELECT...) statement

7. convert seconds to hh:mm:ss

8. Can you get a table Structure from Query Analyzer ?

9. converting msdb sysjobhistory time to hh:mm:ss format

10. how to convert DATETIME hh:mm to integer

11. Dates in dd/mm/yyy hh:mm:ss format

12. rounding to a quater of an hour (hh:15, hh:30,hh:45,hh:00)

13. mm/dd/yy to dd/mm/yy using CONVERT