Re: Sum(time) possible? - Mailing list pgsql-general

Hi

Here are some of my preferances, that I have found to be easier to work with doing
this.

"Command Prompt, Inc." wrote:

> On Sat, 3 Nov 2001, Konstantinos Agouros wrote:
> >On Sat, Nov 03, 2001 at 09:33:35AM -0800, Andrew Gould wrote:
> >>Are you trying to sum times or lengths of time?
> >Yup. A little background the column hold the time someone works on a project.
> >At the end of the month I want to see the total time. If time is not the right
> >column type for this please let me know.
>
> Sounds like you want an interval data type, not time. Interval describes a
> discrete length of time in temporal units, and you can perform a sum() on
> its values.
>

Yuk. Use reltime it has better conversions.
"reltime" converts seconds to "int4" and vice versa.

 select reltime('-3600'::int4);
  reltime
-----------
 01:00 ago
(1 row)

select int4('1 day'::reltime);
 int4
-------
 86400
(1 row)

If you use intervals you first need to convert them to reltime.


>
> You could possibly instead have a start_timestamp column and an
> end_timestamp column, and do a sum(end_timestamp - start_timetstamp), if
> you need to track more than just the interval; subtracting a timestamp
> from another timestamp will yield an interval.
>

select 'now'::timestamp - 'Oct 31 13:24:45 2001'::timestamp;
  ?column?
------------
 5 23:52:52
(1 row)

I prefer to use abstime rather than timestamp, if the data is to used in a spread
sheet integer based seconds are easier to deal with than the reltime format.

select 'now'::abstime - 'Oct 31 13:24:45 2001'::abstime;
 ?column?
----------
   517929
(1 row)


>
> Regards,
> Jw.
> --
> jlx@commandprompt.com
> by way of pgsql-general@commandprompt.com
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
>     (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)

Of course there are alternatives and I am not saying that the writer of this
message is wrong in any way.

I have found these to be adequate and simple for accounting radius session
information and other things over the last 5 years. I had a lot of trial & error
at the begining and had to write some of my own functions to do what I needed.
Scouring the huge amount of data in the manuals over the last 5 years as new
functions became available I no longer need my own conversion functions. I am
running 7.0.2 and there may be new conversions of which I am unaware. Also when
using copy to|from files for import into other applications reltime and interval
are not available but integers should be available.

Guy