2007/9/9, novice <user.postgresql@xxxxxxxxx>:> > > > The result I'm expecting for the above to be> > > >> > > > notification_time | finished_time | actual> > > > ------------------------+------------------------+-----------------> > > > 2007-07-06 15:50:00+10 | 2007-07-09 07:10:00+10 | 01:20:00> > > > 2007-07-07 12:30:00+10 | 2007-07-09 07:20:00+10 | 00:20:00> >>> create table log> (> id integer PRIMARY KEY,> notification_time timestamp with time zone,> finished_time timestamp with time zone> );>> INSERT INTO log values (1, '2007-07-06 15:50', '2007-07-09 07:10');> INSERT INTO log values (2, '2007-07-07 12:30', '2007-07-09 07:20');>> SELECT notification_time, finished_time, sum(finished_time -> notification_time) as actual> FROM log> GROUP BY notification_time, finished_time;>
OK. so I have bad news for you: with such structure, you will have towrite some function to calculate work time spent for each task.
general algorithm would be similar to
* take the notification time* take the finished_time* set pointer := notif. time* set actual := 0* while there is any weekend between the pointer and finished_time,do the following** set actual := actual + ( weekend begin - pointer )** move pointer to the next monday morning* set actual := actual + ( finished_time - pointer )
BUT:this is ugly.do you always assume that people are doing ONLY one task at a time?
maybe think of representing work sheets in the database?
maybe think of adding "work_time" field to your table (why not trustpeople, they know best)
maybe the application you use for entering data could give some "hint"basing on above algo.
good luck,
-- Filip Rembiałkowski
---------------------------(end of broadcast)---------------------------TIP 4: Have you searched our list archives?
http://archives.postgresql.org/
