Search Postgresql Archives

Re: Convert interval to hours

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



On Fri, 14 Sep 2018 12:21:14 -0400
David Gauthier <davegauthierpg@xxxxxxxxx> 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
lembark@xxxxxxxxxxx                                    +1 888 359 3508

-- 
Steven Lembark                                     3920 10th Ave South
Workhorse Computing                               Birmingham, AL 35222
lembark@xxxxxxxxxxx                                    +1 888 359 3508




[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]

  Powered by Linux