Re: Converting time to float

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>