Re: Sum(time) possible?
От | Guy Fraser |
---|---|
Тема | Re: Sum(time) possible? |
Дата | |
Msg-id | 3BE84D1E.23C58E5@incentre.net обсуждение исходный текст |
Ответ на | Re: Sum(time) possible? ("Command Prompt, Inc." <pgsql-general@commandprompt.com>) |
Список | 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
В списке pgsql-general по дате отправления: