work hour calculations

From: novice <user(dot)postgresql(at)gmail(dot)com>
To: pgsql-general(at)postgresql(dot)org, pgsql-sql(at)postgresql(dot)org
Subject: work hour calculations
Date: 2007-09-05 03:45:46
Message-ID: ddcb1c340709042045lbe5d58fode7497c8eb060c97@mail.gmail.com
Views: Whole Thread | Raw Message | Download mbox | Resend email
Thread:
Lists: pgsql-general pgsql-sql

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.

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Raj A 2007-09-05 03:54:41 Re: work hour calculations
Previous Message Alvaro Herrera 2007-09-05 03:32:39 Re: How to 'register' functions, so they can be called (plpythonu)

Browse pgsql-sql by date

  From Date Subject
Next Message Raj A 2007-09-05 03:54:41 Re: work hour calculations
Previous Message Richard Ray 2007-09-04 15:56:04 Re: How to influence the planner