CODEWITHSUNDEEP
mysqlsql
yokoyoko
yokoyoko

Reputation: 265

MySQL : Calculate business day difference between two dates column

My sql query returns back two columns, first column is "date created" and second column is "date updated", first column has a prior timestamp with respect to second column.

I need to add a third column which can display business day hrs (9:00am to 5:00pm) response i.e. if date created is 2012-01-04 09:00:20 and "dated updated" is 4:00pm same day then third column should display 7 hrs

If date created is 2012-01-04 16:00:20 (4:00pm) and "date updated" is 10:00m on 2012:01:05 (2nd Jan) then third column should display 2 hrs.

It should exclude Saturday and Sunday.

Can you please suggest appropriate SQL query for this.

Upvotes: 2

Views: 1498

Answers (1)

eggyal
eggyal

Reputation: 125955

As @Gordon Linoff commented, you need to create a table containing your business hours:

CREATE TABLE business_days (
  start DATETIME NOT NULL,
  end   DATETIME NOT NULL
);

INSERT INTO `business_days`
  (start, end)
VALUES
  ('2011-12-30 09:00', '2011-12-30 15:00'),  -- short day
  ('2012-01-03 09:00', '2012-01-03 17:00'),  -- 31st, 1st were Sat/Sun
  ('2012-01-04 09:00', '2012-01-04 17:00'),  -- 2nd was a public holiday
  ('2012-01-05 09:00', '2012-01-05 17:00'),
  -- etc.

Then you can do:

SELECT   m.*,
         SEC_TO_TIME(SUM(TIME_TO_SEC(TIMEDIFF(
           LEAST(m.updated, b.end),
           GREATEST(m.created, b.start)
         ))))
FROM     my_table m JOIN business_days b
           ON b.start < m.updated AND m.created < b.end
GROUP BY m.created, m.updated  -- or some other key into my_table

See it on sqlfiddle.

Upvotes: 1

Related Questions