Thread: Sum(time) possible?
Hi, I have a column of times that I would need to sum up for accounting purposes. Since sum(time) does not seem to be defined anybody got an easy clue how to do this? Regards, Konstantin -- Dipl-Inf. Konstantin Agouros aka Elwood Blues. Internet: elwood@agouros.de Otkerstr. 28, 81547 Muenchen, Germany. Tel +49 89 69370185 ---------------------------------------------------------------------------- "Captain, this ship will not survive the forming of the cosmos." B'Elana Torres
Are you trying to sum times or lengths of time? Andrew --- Konstantinos Agouros <elwood@agouros.de> wrote: > Hi, > > I have a column of times that I would need to sum up > for accounting purposes. > Since sum(time) does not seem to be defined anybody > got an easy clue how to > do this? > > Regards, > > Konstantin > -- > Dipl-Inf. Konstantin Agouros aka Elwood Blues. > Internet: elwood@agouros.de > Otkerstr. 28, 81547 Muenchen, Germany. Tel +49 89 > 69370185 > ---------------------------------------------------------------------------- > "Captain, this ship will not survive the forming of > the cosmos." B'Elana Torres > > ---------------------------(end of > broadcast)--------------------------- > TIP 4: Don't 'kill -9' the postmaster __________________________________________________ Do You Yahoo!? Find a job, post your resume. http://careers.yahoo.com
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. Konstantin > > Andrew > > --- Konstantinos Agouros <elwood@agouros.de> wrote: > > Hi, > > > > I have a column of times that I would need to sum up > > for accounting purposes. > > Since sum(time) does not seem to be defined anybody > > got an easy clue how to > > do this? > > > > Regards, > > > > Konstantin > > -- > > Dipl-Inf. Konstantin Agouros aka Elwood Blues. > > Internet: elwood@agouros.de > > Otkerstr. 28, 81547 Muenchen, Germany. Tel +49 89 > > 69370185 > > > ---------------------------------------------------------------------------- > > "Captain, this ship will not survive the forming of > > the cosmos." B'Elana Torres > > > > ---------------------------(end of > > broadcast)--------------------------- > > TIP 4: Don't 'kill -9' the postmaster > > > __________________________________________________ > Do You Yahoo!? > Find a job, post your resume. > http://careers.yahoo.com -- Dipl-Inf. Konstantin Agouros aka Elwood Blues. Internet: elwood@agouros.de Otkerstr. 28, 81547 Muenchen, Germany. Tel +49 89 69370185 ---------------------------------------------------------------------------- "Captain, this ship will not survive the forming of the cosmos." B'Elana Torres
On Sat, 3 Nov 2001, Konstantinos Agouros wrote:
> 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.
Konstantin,
I keep my time records as decimals (to the nearest quarter-hour). Then
it's a simple matter to add 3.25, 7.5, and so on.
If the time is clock time, perhaps you can convert it to decimal hours
before summing.
HTH,
Rich
Dr. Richard B. Shepard, President
Applied Ecosystem Services, Inc. (TM)
2404 SW 22nd Street | Troutdale, OR 97060-1247 | U.S.A.
+ 1 503-667-4517 (voice) | + 1 503-667-8863 (fax) | rshepard@appl-ecosys.com
http://www.appl-ecosys.com
On Sat, Nov 03, 2001 at 09:57:24AM -0800, Rich Shepard wrote: > On Sat, 3 Nov 2001, Konstantinos Agouros wrote: > > > 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. > > Konstantin, > > I keep my time records as decimals (to the nearest quarter-hour). Then > it's a simple matter to add 3.25, 7.5, and so on. > > If the time is clock time, perhaps you can convert it to decimal hours > before summing. Well I got to the point of doing extract (hour)*3600+extract(minute)*60+extract (seconds) but then I have to recalculate this to a decent display. The user will enter the data in the HH:MM:SS format so using intervals (which would let postgres make easy calculations) would lead me to enter intervals 00:00:00- HH:MM:SS or something like that. Maybe that's a better solutions. The question is: Is time meant as Wall clock time because then it would make no sense that it could be summed up. Cheers, Konstantin > > HTH, > > Rich > > Dr. Richard B. Shepard, President > > Applied Ecosystem Services, Inc. (TM) > 2404 SW 22nd Street | Troutdale, OR 97060-1247 | U.S.A. > + 1 503-667-4517 (voice) | + 1 503-667-8863 (fax) | rshepard@appl-ecosys.com > http://www.appl-ecosys.com > -- Dipl-Inf. Konstantin Agouros aka Elwood Blues. Internet: elwood@agouros.de Otkerstr. 28, 81547 Muenchen, Germany. Tel +49 89 69370185 ---------------------------------------------------------------------------- "Captain, this ship will not survive the forming of the cosmos." B'Elana Torres
Good day, Just wondering if anyone knows the reason for the DELIMITER keyword in CREATE TYPE? The documentation states that it sets the value delimiter if you are creating an array data type, but setting this value to something other than a comma neither seems to affect the input nor output representation--that is, a comma still appears to be required on input, and displayed on output, regardless of what I set the DELIMITER to. Is this possibly something that has changed in the last six years? I found docs from '95 stating essentially the same thing. I poked around a little in backend/src/arrayfuncs.c, but I didn't get very far. ;) Regards, Jw. -- jlx@commandprompt.com by way of pgsql-general@commandprompt.com
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. 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. Regards, Jw. -- jlx@commandprompt.com by way of pgsql-general@commandprompt.com
elwood@agouros.de (Konstantinos Agouros) writes:
> I have a column of times that I would need to sum up for accounting purposes.
> Since sum(time) does not seem to be defined anybody got an easy clue how to
> do this?
We don't have addition of times defined at all, so how would you expect
sum() to exist? Try casting the times to some datatype for which
addition makes sense, eg interval.
The reason time addition isn't defined is that it's not clear what
people would want. Is '9:12 PM' + '4:23 AM' even sensible? If
so, does it mean '1:35 AM' or '1 day 1 hour 35 minutes'? Casting
to interval makes clear that you want the latter interpretation.
regards, tom lane
On Sat, 3 Nov 2001, Konstantinos Agouros wrote: >Is time meant as Wall clock time because then it would make no sense that >it could be summed up. Yes; time types describe a specific time of day. I think you want the interval type. ;) Regards, Jw. -- jlx@commandprompt.com by way of pgsql-general@commandprompt.com
"Command Prompt, Inc." <pgsql-general@commandprompt.com> writes:
> Just wondering if anyone knows the reason for the DELIMITER keyword in
> CREATE TYPE? The documentation states that it sets the value delimiter if
> you are creating an array data type, but setting this value to something
> other than a comma neither seems to affect the input nor output
> representation--that is, a comma still appears to be required on input,
> and displayed on output, regardless of what I set the DELIMITER to.
?? What was your test case exactly? A casual perusal of arrayfuncs.c
certainly looks like array_in and array_out use the specified delimiter
character, not a hardwired comma.
regards, tom lane
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
Konstantinos Agouros <elwood@agouros.de> writes:
> what does the @ in the output of [intervals] mean?
AFAICT it's just noise, perhaps once meant to remind you that you're
looking at an interval rather than an absolute time. Thomas, any
recollections here?
The '@' doesn't appear in the ISO datestyle, btw, only Postgres
datestyle.
regards, tom lane
On Sat, 3 Nov 2001, Tom Lane wrote:
>"Command Prompt, Inc." <pgsql-general@commandprompt.com> writes:
>>Just wondering if anyone knows the reason for the DELIMITER keyword in
>>CREATE TYPE? The documentation states that it sets the value delimiter if
>>you are creating an array data type, but setting this value to something
>>other than a comma neither seems to affect the input nor output
>>representation--that is, a comma still appears to be required on input,
>>and displayed on output, regardless of what I set the DELIMITER to.
>?? What was your test case exactly? A casual perusal of arrayfuncs.c
>certainly looks like array_in and array_out use the specified delimiter
>character, not a hardwired comma.
That's what it looked like to me as well (assuming that the typdelim is
what I think it is), so I was a bit perplexed. For my test, I created an
extremely bogus type called "zero" which is just an integer which is
always set to zero. Here's what I tried:
------------------------------------------------------------------------
Built bogus "zero" type:
------------------------------------------------------------------------
lx=# CREATE FUNCTION zero_out(opaque) RETURNS opaque AS '/tmp/zero.so' LANGUAGE 'C';
CREATE
lx=# CREATE FUNCTION zero_in(opaque) RETURNS zero AS '/tmp/zero.so' LANGUAGE 'C';
NOTICE: ProcedureCreate: type 'zero' is not yet defined
lx=# CREATE TYPE zero (internallength = 16, input = zero_in, output = zero_out);
CREATE
lx=# CREATE TABLE the_nothing (nothing zero);
CREATE
lx=# INSERT INTO the_nothing VALUES ('test');
INSERT 3841880 1
lx=# SELECT * FROM the_nothing;
nothing
---------
0
(1 row)
------------------------------------------------------------------------
Built bogus "zero_array" array off the "zero" type, delimited by a pipe:
------------------------------------------------------------------------
lx=# CREATE TYPE zero_array (internallength = 16, input = array_in, output = array_out, ELEMENT = zero, DELIMITER =
'|');
CREATE
lx=# CREATE TABLE na (n zero_array);
CREATE
lx=# INSERT INTO na VALUES ('{0|0}');
INSERT 3841909 1
lx=# SELECT * FROM na;
n
-------
{"0"}
(1 row)
lx=# INSERT INTO na VALUES ('{0,0}');
INSERT 3841910 1
lx=# SELECT * FROM na;
n
-----------
{"0"}
{"0","0"}
(2 rows)
------------------------------------------------------------------------
As you can see, providing a pipe as a delimiter in the INSERT statement
caused the second value to be omitted; using the comma still worked in the
second example, but then displayed the output with a comma-delimiter.
Am I doing something wrong in the array definition possibly?
Regards,
Jw.
--
jlx@commandprompt.com
by way of pgsql-general@commandprompt.com
"Command Prompt, Inc." <pgsql-general@commandprompt.com> writes:
> lx=# CREATE TYPE zero_array (internallength = 16, input = array_in, output = array_out, ELEMENT = zero, DELIMITER =
'|');
Oh, I see the problem. typdelim is actually the delimiter to use when
a type is contained in an array: that is, the array subroutines look to
the element type, not the array type itself, to find the delimiter to
use. So you'd need to have attached the DELIMITER = '|' spec to the
"zero" datatype to make it effective. (This seems reasonable to me,
since it is the element type's formatting that determines what sort
of delimiter is sensible.)
The CREATE TYPE ref page is poorly worded on this point; I'll try to
improve it.
BTW, creation of the "zero" datatype automatically creates an associated
array type (internally named "_zero"), so there's no need for you to
try to create a "zero_array" type separately. Indeed the above spec
will not work, because array_in and array_out are only valid for
variable-length arrays, and you've tried to specify a fixed-length type.
You might reasonably ask "why is there an ELEMENT option in CREATE TYPE,
if the system makes the correct array type automatically?" The only
case where it's useful to use ELEMENT is when you are making a
fixed-length type that happens to be internally an array of N identical
things, and you want to allow the N things to be accessed directly by
subscripting. For example, type "name" allows its constitutent "char"s
to be accessed this way; ditto "oidvector" and its component OIDs.
A 2-D "point" type could allow its two component floats to be accessed
like point[0] and point[1]. Note that what we have here is substructure
of a scalar datatype; it has nothing to do with the ability to assemble
arrays of names or arrays of points.
Now that I look at it, the CREATE TYPE ref page is pretty confusing
on the whole topic of array types. I'll see what I can do with it.
regards, tom lane
On Sat, 3 Nov 2001, Tom Lane wrote: >So you'd need to have attached the DELIMITER = '|' spec to the >"zero" datatype to make it effective. (This seems reasonable to me, >since it is the element type's formatting that determines what sort >of delimiter is sensible.) Ah! Thanks, just tried it, and it works perfectly. >Now that I look at it, the CREATE TYPE ref page is pretty confusing >on the whole topic of array types. I'll see what I can do with it. Wonderful. While you're in there, you might look at the wording on the input and output function descriptions; it says that they must take either one or two arguments of type opaque, but it's not at all clear what the second argument would be for, in either circumstance. Also, the DEFAULT clause was a bit curious; it says you can describe "some specific bit pattern" to mean "data not present"; presumably in place of NULL? I figured this meant it took a bit string for some kind of internal handling, but it seems to accept any arbitrary value I give it (integer, text, though not a b'1001' style bit string!). However, I got some ugly disconnections from the server after experimenting with this value, and attempting to SELECT values that would have otherwise been NULL. ;) I suppose because the output function wasn't set up to handle whatever value was getting set by this clause? Thanks again, Jw. -- jlx@commandprompt.com by way of pgsql-general@commandprompt.com
"Command Prompt, Inc." <pgsql-general@commandprompt.com> writes:
> Wonderful. While you're in there, you might look at the wording on the
> input and output function descriptions; it says that they must take either
> one or two arguments of type opaque, but it's not at all clear what the
> second argument would be for, in either circumstance.
On checking the code, that's actually wrong, as well as insufficient.
Fixed.
> Also, the DEFAULT clause was a bit curious; it says you can describe "some
> specific bit pattern" to mean "data not present"; presumably in place of
> NULL?
This is bogus too. A default value is just a default value supplied at
the datatype level instead of the column level. Basically INSERT does
this for each column:
* Column value given explicitly in INSERT command?
=> use it
* DEFAULT specified for column in table creation command?
=> use that
* DEFAULT specified for column's datatype?
=> use that
* Else, insert a NULL
There isn't anything in there that could allow a datatype-specific
representation of NULL, which is what the existing wording seems to
suggest the clause is for.
> I figured this meant it took a bit string for some kind of internal
> handling, but it seems to accept any arbitrary value I give it (integer,
> text, though not a b'1001' style bit string!). However, I got some ugly
> disconnections from the server after experimenting with this value, and
> attempting to SELECT values that would have otherwise been NULL. ;) I
IIRC, until recently the datatype-level DEFAULT was essentially useless,
because the way it was coded, you'd have to enter a textual string whose
contents are the *internal* representation of the desired default value.
This is pretty silly, so it's been fixed to accept a normal
external-form string that gets fed through the type's input routine.
But I think that may only be in 7.2, not any earlier release.
I'm working on improving the CREATE TYPE ref page now ...
regards, tom lane
> I'm working on improving the CREATE TYPE ref page now ... I've committed some updates. You can read the results at http://candle.pha.pa.us/main/writings/pgsql/sgml/sql-createtype.html regards, tom lane
> AFAICT it's just noise, perhaps once meant to remind you that you're
> looking at an interval rather than an absolute time. Thomas, any
> recollections here?
The leading "@" is by now a historical artifact (and was at the time
PostgreSQL spun out of Berkeley). Inferring anything else is just
guessing, but *my* favorite theory is that it was intended to help
resolve untyped input in some long-ago implementation.
- Thomas
elwood@agouros.de (Konstantinos Agouros) 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.
Well, the TIME field is intended to contain something resembling a
time _stamp_. That's not terribly compatible with your intent.
Trying a little sample:
DROP TABLE samp;
CREATE TABLE samp (
consumed time,
name char(15)
);
insert into samp (consumed, name) values ('11:00', 'chris');
insert into samp (consumed, name) values ('13:00', 'dave');
insert into samp (consumed, name) values ('14:00', 'doug');
select * from samp;
select sum(consumed) from samp;
-->
DROP
CREATE
INSERT 118714 1
INSERT 118715 1
INSERT 118716 1
consumed | name
----------+-----------------
11:00:00 | chris
13:00:00 | dave
14:00:00 | doug
(3 rows)
ERROR: Unable to select an aggregate function sum(time)
Apparently what you want isn't completely well supported :-(.
--
(reverse (concatenate 'string "gro.gultn@" "enworbbc"))
http://www.cbbrowne.com/info/lsf.html
Twice five syllables
Plus seven can't say much but
That's haiku for you.
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
In <3BE84D1E.23C58E5@incentre.net> guy@incentre.net (Guy Fraser) writes:
>Hi
>Here are some of my preferances, that I have found to be easier to work with doing
>this.
>"Command Prompt, Inc." wrote:
>> 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.
Hmmm reltime also gets me seconds. But to get seomthing like:
200:5:2 meaning 200 hours, 5 minutes 2 seconds I have to define my own
function, correct?
Konstantin
--
Dipl-Inf. Konstantin Agouros aka Elwood Blues. Internet: elwood@agouros.de
Otkerstr. 28, 81547 Muenchen, Germany. Tel +49 89 69370185
----------------------------------------------------------------------------
"Captain, this ship will not survive the forming of the cosmos." B'Elana Torres