Thread: work hour calculations
Hello All, SELECT notification_time, finished_time, sum(finished_time - notification_time) as actual FROM log GROUP BY notification_time, finished_time; gives me: notification_time | finished_time | actual ------------------------+------------------------+----------------- 2007-07-06 15:50:00+10 | 2007-07-09 07:10:00+10 | 2 days 15:20:00 2007-07-07 12:30:00+10 | 2007-07-09 07:20:00+10 | 1 day 18:50:00 How can write a query to calculate the duration using custom work hours which is Monday 7am / Friday 5pm? 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 | 00:20:00 2007-07-07 12:30:00+10 | 2007-07-09 07:20:00+10 | 00:20:00 Thanks.
correction: > 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 >
correction: > 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
2007/9/5, Raj A <raj.ayappan@gmail.com>: > correction: > > > 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 Could you please show the data structures ( create table commmands + ane constraints you have )? If you do this, you have much bigger chance of getting an answer :) -- Filip Rembiałkowski
On 07/09/2007, Filip Rembiałkowski <plk.zuber@gmail.com> wrote: > 2007/9/5, Raj A <raj.ayappan@gmail.com>: > > correction: > > > > > 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 > > Could you please show the data structures ( create table commmands + > ane constraints you have )? > > If you do this, you have much bigger chance of getting an answer :) sure 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;
2007/9/9, novice <user.postgresql@gmail.com>: > > > > 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 to write 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 trust people, they know best) maybe the application you use for entering data could give some "hint" basing on above algo. good luck, -- Filip Rembiałkowski