Calculate Working Hours in interval
Posted on 2010-11-23
I have a Postgres DB with some tables with columns of timestamp type without time zone
like: Creation_Date_UTC and Closure_date_UTC
ie: 2010-11-05 07:42:00
What I want is maybe a function able to calculate "Working Hours" [09:00:00 to 18:00:00] from the interval in those two columns.
In this function I should also be able to specify the "working hours" interval, that the function will use for calculation. The result will be the number of working hours between the two dates [columns] calculated based on specified working hours interval.
ie: get_working_hours(Creation_Date_UTC, Closure_date_UTC, Work_Period_start, Work_Period_end)
ie: get_working_hours(2010-11-05 10:00:00, 2010-11-05 17:00:00, 09:00:00, 18:00:00)
In this case the result should be 7. [hours]
My date format in Postgres is DD/MM/YYYY and time format 24h.
Thanks so much!