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
I am working on software development since 1992.
I am expert on database migration.
I can migrate any kind of relational databases with just few SQL sentences.
I can solve problems about replacing or combining data related with plugins, modules, themes or components.
I have a lot of linux server administrator skills.
I have an strong knowledge of server side scripting.