Thread: Converting time to float

Hi!


I am needing to convert from TIME type to floats and taking 1h as the
unit, so what I'd like is something that would allow me doing operations
like:

SELECT '00:10:00'::TIME / '1:00:00'::TIME;  -- Answer is: 0.16666666666666666667
SELECT '00:30:00'::TIME / '1:00:00'::TIME;  -- Answer is: 0.5
SELECT '01:10:00'::TIME / '1:00:00'::TIME;  -- Answer is: 1.16666666666666666667

or

SELECT '00:10:00'::TIME / '1 hour'::INTERVAL;  -- Answer is: 0.16666666666666666667
SELECT '00:30:00'::TIME / '1 hour'::INTERVAL;  -- Answer is: 0.5
SELECT '01:10:00'::TIME / '1 hour'::INTERVAL;  -- Answer is: 1.16666666666666666667

i.e., I want to see how many times one time value fits in another.  I'll
have times with just seconds, minutes and seconds and I might have times
with hours, minutes and seconds to make this fit.


My first idea is converting each part of the time individually by diving
it by 1.0 (hours), by 60.0 (minutes) or by 3600.0 (for seconds) and then
adding it all up...  Of course I won't do the division for hours and I'm
using floats here to for a float division instead of an integer
division.


Any hints or a better recipe? :-)

--
Jorge Godoy      <jgodoy@gmail.com>

Jorge Godoy wrote:
> Hi!
>
>
> I am needing to convert from TIME type to floats and taking 1h as the
> unit, so what I'd like is something that would allow me doing operations
> like:
>
> SELECT '00:10:00'::TIME / '1:00:00'::TIME;  -- Answer is: 0.16666666666666666667

But it doesn't make any sense to divide one time by another, does it?
Are you sure it's not intervals you want?

Anyway, try something like this:

SELECT extract(epoch from ('14:02:04'::time)) / extract(epoch from
('01:00:00'::time));
      ?column?
------------------
  14.0344444444444

The "epoch" is in seconds (from midnight 1970-01-01 for timestamps) so
gives you the result you want.

--
   Richard Huxton
   Archonet Ltd

Richard Huxton <dev@archonet.com> writes:

>> SELECT '00:10:00'::TIME / '1:00:00'::TIME;  -- Answer is: 0.16666666666666666667
>
> But it doesn't make any sense to divide one time by another, does it? Are you
> sure it's not intervals you want?

It "doesn't matter", I just used time because I thought it might be
easier or exist something for the same type and that the concept was
easier to understand with it when compared to interval.  If we take the
physical sense of the data, then interval is a more correct type (and is
the one used, but since I could convert from one to the other freely, I
didn't bother with details on my previous message). :-)

The 1 hour time / interval is a reference and I need to get the value of
a time parameter on base 100 instead of base 60.  This is why I'm doing
this operation.

> Anyway, try something like this:
>
> SELECT extract(epoch from ('14:02:04'::time)) / extract(epoch from
> ('01:00:00'::time));
>      ?column?
> ------------------
>  14.0344444444444
>
> The "epoch" is in seconds (from midnight 1970-01-01 for timestamps) so gives
> you the result you want.

It does.  And is prettier. ;-)

The old code was


CREATE OR REPLACE FUNCTION aux.f_v_measured_time_base10(
     p_measured_time INTERVAL, OUT o_measured_time_base10 FLOAT) AS $_$
DECLARE
BEGIN
    o_measured_time_base10:=EXTRACT(HOUR FROM p_measured_time);
    o_measured_time_base10:=o_measured_time_base10 +
                            (EXTRACT(MINUTE FROM p_measured_time) / 60.0);
    o_measured_time_base10:=o_measured_time_base10 +
                            (EXTRACT(SECOND FROM p_measured_time) / 3600.0);
END;
$_$ LANGUAGE plpgsql IMMUTABLE STRICT;


The new one is

CREATE OR REPLACE FUNCTION aux.f_v_measured_time_base10(
     p_measured_time INTERVAL, OUT o_measured_time_base10 FLOAT) AS $_$
DECLARE
BEGIN
    o_measured_time_base10:=(EXTRACT(EPOCH FROM p_measured_time) /
                             EXTRACT(EPOCH FROM '1:00:00'::INTERVAL));
END;
$_$ LANGUAGE plpgsql IMMUTABLE STRICT;



Thanks for your help.

--
Jorge Godoy      <jgodoy@gmail.com>