Re: Convert interval to hours

On Fri, Sep 14, 2018 at 2:42 PM Steven Lembark <lembark(at)wrkhors(dot)com> wrote:
>
> On Fri, 14 Sep 2018 12:21:14 -0400
> David Gauthier <davegauthierpg(at)gmail(dot)com> wrote:
>
> > I'm using postgres v9.5.2 on RH6.
>
> PG can convert the times for you.
> For times (not timestamps) you are always better off dealing with
> either time or integer seconds. There are a variety of issues with
> rouding that affect repeatability and accuracy of results using
> floats or doubles. Given that 10 and three are both continuing
> fractions in binary (e.g., 1/10 binary is an infinite series)
> division by 3600 will only cause you annoyance at some point.
>
> If you are subtracting times then you will (usually) end up with
> an interval, which can be cast to seconds in the query and give
> you precise, accurate, repeatable results every time.
>
> e.g.,
>
> select
> extract
> (
> epoch from ( time1 - time2 )::interval
> )
> as "seconds",
> ...
>
> is one approach.
>
> In nearly all cases you are better off selecting and converting
> the time in SQL rather than converting the start and end times
> from numeric (time) to string (DBI) and then back from char *
> to float/double or int/unsigned. The charaacter conversion is
> expensive and numeric -> string -> numeric leaes you open to all
> sorts of rouding and conversion issues.
>
> Frankly, if you have to run the query more than once I'd suggest
> adding a view that does the select/convert for you (along with
> dealing with any NULL's that creep into things). PG makes it quite
> easy to add the view and quite in-expensive to apply it.
>

In the original e-mail, the OP said
> I have code that's getting me an "age" which returns something like... "-17 days -08:29:35".

I took that to mean he was beginning with a string, which I suggested
to cast to an interval. If he's starting with a different type, then
of course the fewer castings the better. Also, it seems as though you
two have had private communication, because I don't see an e-mail
where he specified the DB type. Perhaps he also showed more of the
source data there.

Pete