Re: Sum(time) possible?

Konstantinos Agouros <elwood@agouros.de> writes:
> 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.

In that case I'd say you chose the wrong datatype: the semantics you
want are "interval", not "time".

The SQL "time" datatype really means "time of day".  The most obvious
use I can think of for it is in tables depicting schedules:

Flight        From        To        Sched Departure  Sched Arrival

USAir 123    Pittsburgh    New York    11:12 AM    12:15 PM

Here, the departure and arrival times are naturally of type "time"
(not "timestamp", since no specific date is mentioned).  If we are
dealing with flights spanning timezones then we might want type
"time with time zone":

USAir 11    Pittsburgh    Los Angeles    2:45 PM EST    4:55 PM PST

Note that these are times of day, not intervals.  However, if we
subtract departure time from arrival time to get flight duration,
guess what datatype is produced.

For your purposes, it would seem that what you want to store is either a
single interval column representing elapsed time worked during a given
bout of work, or two time (or possibly better, timestamp) columns
representing starting and ending times --- which you could subtract to
produce the elapsed time as an interval, and then sum() that.

            regards, tom lane