On Fri, Sep 14, 2018 at 2:42 PM Steven Lembark <lembark@xxxxxxxxxxx> wrote: > > 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. > 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
