Complex datetime differences in PostgreSQL

Hello, I’m working on a PostgreSQL server. I have events which have a start time (timestamp) and an end time (timestamp). I’d like to get a plpgsql function + trigger which computes the elapsed time between those 2 dates, but only counting the BUSINESS HOURS. For this, I have an helper table with a list of days and the relevant business hours. Example: dim_date date opening_hour_start opening_hour_stop 2014-01-01 NULL NULL 2014-02-01 08:00 20:00 2014-03-01 08:00 12:00 2014-04-01 NULL NULL 2014-05-01 NULL NULL 2014-06-01 NULL NULL […] 2014-12-31 NULL NULL When I insert the following events, I expect the following duration (in minutes) will be computed: start_time end_time duration reason None None None Not enough data to compute… 2014-01-01 14:00 None None Not enough data to compute… 2014-01-01 14:00 2014-01-02 05:00 0 The event is during non-business-hours 2014-01-01 14:00 2014-01-02 10:00 02:00:00 2014-01-01 23:59 2014-01-02 10:00 02:00:00 2014-01-02 00:00 2014-01-02 10:00 02:00:00 2014-01-01 14:00 2014-03-01 10:00 14:00:00 2014-01-01 14:00 2014-03-01 23:00 16:00:00 2014-01-01 14:00 2014-12-31 23:00 16:00:00 Those are just some test cases - obviously the function must work for an arbitrary number of entries in dim_date and for an arbitrary duration between start_time and end_time. The deliverables are the SQL code to: - create the table dim_date and its data as defined above - create the table events as defined above - create a function that computes “duration” (plpgsql only please - if you need another language please) - create a trigger which does the job requested. It must only be triggered when start_time or end_time change - run the tests above by inserting new events in the “events” table - of course the results need to be correct ;) - run the tests above by creating an empty event and then doing updates on this event based on the test data above If you feel you need another language than plpgsql, please tell me so. I’m open to all questions of course