Alex Rivera | Logout

Mysql : how to calculate business hrs between two timestamps and neglect weekends

Asked 2012-11-17T00:01:40.180
18

My table has sample data and I need to calculate business hrs between two timestamps in two columns. Business hrs : 9:00am to 5:00pm and neglect Saturday and Sunday, I am not considering public holidays. Can someone please provide some guidelines on how to achieve this? I want desired output as stated in column 3 , date is in format of : yyyy-mm-dd

    Created date             Updated date         Business hrs
    2012-03-05 9:00am   2012-03-05 3:00pm             6
    2012-03-05 10:00am  2012-03-06 10:00am            9
    2012-03-09 4:00pm   2012-03-19 10:00am            2
mysql sql date
Edit
Report

1 Answer

4

Create a table called BusinessHours, with a single column called Hour containing a date time. Make Hour the primary key. Write a process to populate it with every business hour of the year (2012-01-02 00:90:00, 2012-01-02 00:10:00 etc). This shouldn't be hard as the rules are straightforward. It sounds like a lot of data, but in the grand scheme of things it isn't (there are only 8760 hours in a year - even if your date time takes 8 bytes each, this is a whopping 60kB). Make sure to schedule a job to keep it populated.

Then:

Select
  y.CreatedDate,
  y.UpdatedDate,
  Count(*) as BusinessHours
From
  YourTable y
    Inner Join
  BusinessHours h
    On h.Hour >= y.CreatedDate And h.Hour < y.UpdatedDate
Group By
  y.CreatedDate,
  y.UpdatedDate

This also gives you a fairly straightforward approach if you do ever consider public holidays - just take the rows out of the BusinessHours table.

answered 2012-11-17T00:13:32.920

Your Answer