Convert interval to hours

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
6 messages Options
Reply | Threaded
Open this post in threaded view
|

Convert interval to hours

David Gauthier
Hi:

In perl/DBI, I have code that's getting me an "age" which returns something like... "-17 days -08:29:35".  How can I convert that to a number of hours (as a float I would presume) ?

Thanks




Reply | Threaded
Open this post in threaded view
|

Re: Convert interval to hours

Peter Kleiner
On Fri, Sep 14, 2018 at 11:51 AM David Gauthier
<[hidden email]> wrote:

>
> Hi:
>
> In perl/DBI, I have code that's getting me an "age" which returns something like... "-17 days -08:29:35".  How can I convert that to a number of hours (as a float I would presume) ?
>
> Thanks
>
>
>
>

I've done this as
select extract(epoch from '-17 days -08:29:35'::interval)/3600 as hours;

hours
-------------------
 -416.493055555556
(1 row)

Others might have a better way, though.

Pete

Reply | Threaded
Open this post in threaded view
|

Re: Convert interval to hours

Steven Lembark
On Fri, 14 Sep 2018 11:55:18 -0400
Peter Kleiner <[hidden email]> wrote:

> On Fri, Sep 14, 2018 at 11:51 AM David Gauthier
> <[hidden email]> wrote:
> >
> > Hi:
> >
> > In perl/DBI, I have code that's getting me an "age" which returns
> > something like... "-17 days -08:29:35".  How can I convert that to
> > a number of hours (as a float I would presume) ?

Suggest using one of the date modules. One issue is that not all
days are 86400s long: "leap second" is used to keep atomic clocks
in sync with siderial time so that telescopes report consistent
values over time. Catch is that simply dividing by 3600 doesn't
always work if the times fall across the wrong days.

You would normally want an integer for these rather than float
as the result to avoid rounding issues on extract or with comparisions
in or out of the database. You will normally not have more than one
second precision on times (timestamps are a different matter).

Q: What database are you using?

Postgres makes it easy enough to cast the values or compute the
difference is seconds.




--
Steven Lembark                                     3920 10th Ave South
Workhorse Computing                               Birmingham, AL 35222
[hidden email]                                    +1 888 359 3508

Reply | Threaded
Open this post in threaded view
|

Re: Convert interval to hours

Ron-2
On 09/14/2018 11:10 AM, Steven Lembark wrote:

> On Fri, 14 Sep 2018 11:55:18 -0400
> Peter Kleiner <[hidden email]> wrote:
>> On Fri, Sep 14, 2018 at 11:51 AM David Gauthier
>> <[hidden email]> wrote:
>>> Hi:
>>>
>>> In perl/DBI, I have code that's getting me an "age" which returns
>>> something like... "-17 days -08:29:35".  How can I convert that to
>>> a number of hours (as a float I would presume) ?
> Suggest using one of the date modules. One issue is that not all
> days are 86400s long: "leap second" is used to keep atomic clocks
> in sync with siderial time so that telescopes report consistent
> values over time. Catch is that simply dividing by 3600 doesn't
> always work if the times fall across the wrong days.

Can you give us a hard example of when this won't work?

select extract(epoch from '-17 days -08:29:35'::interval)/3600 as hours;

hours
-------------------
  -416.493055555556
(1 row)


--
Angular momentum makes the world go 'round.

Reply | Threaded
Open this post in threaded view
|

Re: Convert interval to hours

Steven Lembark
In reply to this post by Steven Lembark
On Fri, 14 Sep 2018 12:21:14 -0400
David Gauthier <[hidden email]> 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.

--
Steven Lembark                                     3920 10th Ave South
Workhorse Computing                               Birmingham, AL 35222
[hidden email]                                    +1 888 359 3508

--
Steven Lembark                                     3920 10th Ave South
Workhorse Computing                               Birmingham, AL 35222
[hidden email]                                    +1 888 359 3508

Reply | Threaded
Open this post in threaded view
|

Re: Convert interval to hours

Peter Kleiner
On Fri, Sep 14, 2018 at 2:42 PM Steven Lembark <[hidden email]> wrote:

>
> On Fri, 14 Sep 2018 12:21:14 -0400
> David Gauthier <[hidden email]> 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