Elapsed Time Between Two Dates for specified time range

Question

I have a MYSQL table with a TIMESTAMP column 'Start' and a TIMESTAMP column 'End'. I want to return the number of minutes between the start and the end (End is always after than Start). Usually I'd just use 'TIMESTAMPDIFF()' but this time I need to get the minutes from 9am until 22pm, of each day in that date range.

If a row has a Start '2017-01-01 07:15:00' and an End of '2017-01-02 11:30:00' - the elapsed time should be 15.5 hours (930 minutes).

I'm having trouble coming up with a decent way of doing this and my searching online hasn't found quite what I'm looking for. Can someone help me along?

Edit:

CREATE TABLE date_ranges (
Start TIMESTAMP,
End TIMESTAMP
);

INSERT INTO date_ranges VALUES('2017-01-01 07:15:00','2017-01-02 11:30:00');

I came up with this:

SELECT Start, End, TIMESTAMPDIFF(MINUTE, Start, End) AS MinutesElapsed
FROM date_ranges;

I'm missing the part where the time in minutes is calculated only in the specified time range (9am until 22pm). Any ideas?

as far as I understand the function must calculate from 9 to 22, and add from 9 to 11, in your example. — McNets, Jan 24 '17 at 21:35

score 0 · Answer 1 · answered Jan 24 '17 at 21:05

Here you go:

SELECT t1, t2, (TIMESTAMPDIFF(MINUTE, t1, t2) - TIMESTAMPDIFF(DAY, t1, t2)*660) FROM

(SELECT CASE WHEN t1 < STR_TO_DATE(concat(date_format(t1, '%Y-%m-%d'), ' 09:00:00'), '%Y-%m-%d %h:%i:%s')
THEN STR_TO_DATE(concat(date_format(t1, '%Y-%m-%d'), ' 09:00:00'), '%Y-%m-%d %h:%i:%s')
ELSE t1
END AS t1 FROM test) test1,

(SELECT CASE WHEN t2 > STR_TO_DATE(concat(date_format(t2, '%Y-%m-%d'), ' 22:00:00'), '%Y-%m-%d %h:%i:%s')
THEN STR_TO_DATE(concat(date_format(t2, '%Y-%m-%d'), ' 22:00:00'), '%Y-%m-%d %h:%i:%s')
ELSE t2
END AS t2 FROM test) test2;

660 = number of minutes between 22:00 and 09:00 (11 hours)

Here's the SQL Fiddle.

The number of minutes between 09:00 and 22:00 is actually 780 (13*60). — Ike Walker, Jan 24 '17 at 21:21

score 0 · Answer 2 · answered Jan 24 '17 at 21:23

It's not very concise, but this should give you the results you want:

select started_at,ended_at,
  (case 
  when date(ended_at) = date(started_at)
  then 
    timestampdiff(
      minute,
      greatest(started_at,concat(date(started_at),' 09:00:00')),
      least(ended_at,concat(date(ended_at),' 22:00:00')) 
    )
  else
    timestampdiff(
      minute,  
      least(greatest(started_at,concat(date(started_at),' 09:00:00')),concat(date(started_at),' 22:00:00')),
      concat(date(started_at),' 22:00:00')
    )
    + 
    timestampdiff(
      minute,  
      concat(date(ended_at),' 09:00:00'),
      greatest(least(ended_at,concat(date(ended_at),' 22:00:00')),concat(date(ended_at),' 09:00:00'))
    )    
    + ((datediff(ended_at,started_at)-1)*780) 
  end) as total_minutes
from your_table;

score 0 · Answer 3 · edited May 23 '17 at 12:08

--Generating all dates in 2017.   

CREATE TABLE CALENDAR AS --Use a different table name if CALENDAR already exists
SELECT '2017-12-31 09:00:00' - INTERVAL c.number DAY AS start_datetime,'2017-12-31 22:00:00' - INTERVAL c.number DAY AS end_datetime
FROM (SELECT singles + tens + hundreds number FROM 
(SELECT 0 singles
UNION ALL SELECT   1 UNION ALL SELECT   2 UNION ALL SELECT   3
UNION ALL SELECT   4 UNION ALL SELECT   5 UNION ALL SELECT   6
UNION ALL SELECT   7 UNION ALL SELECT   8 UNION ALL SELECT   9
) singles JOIN 
(SELECT 0 tens
UNION ALL SELECT  10 UNION ALL SELECT  20 UNION ALL SELECT  30
UNION ALL SELECT  40 UNION ALL SELECT  50 UNION ALL SELECT  60
UNION ALL SELECT  70 UNION ALL SELECT  80 UNION ALL SELECT  90
) tens  JOIN 
(SELECT 0 hundreds
UNION ALL SELECT  100 UNION ALL SELECT  200 UNION ALL SELECT  300
UNION ALL SELECT  400 UNION ALL SELECT  500 UNION ALL SELECT  600
UNION ALL SELECT  700 UNION ALL SELECT  800 UNION ALL SELECT  900
) hundreds
ORDER BY number DESC) c  
WHERE c.number BETWEEN 0 and 364
;
--End of table creation

--Actual query begins here
SELECT D.`START`,
       D.`END`,
       SUM(TIMESTAMPDIFF(MINUTE,GREATEST(D.`START`,C.START_DATETIME), LEAST(D.`END`,C.END_DATETIME))) AS TOTAL_TIME
FROM CALENDAR C
LEFT JOIN DATE_RANGES D ON DATE(C.START_DATETIME) >= DATE(D.`START`)
AND DATE(C.START_DATETIME) <= DATE(D.`END`)
WHERE D.`START` IS NOT NULL
  AND D.`END` IS NOT NULL
GROUP BY D.`START`,
         D.`END`
;

Construct a calendar table with a dates for a specified number of years. Each date having a start time of 09:00 and an end time of 22:00.
Left join on this table to get one row per date from the date ranges table.
Sum up the differences each day to get the total time worked.

Sample Demo

McNets · Answer 4 · 2017-01-24T22:56:21.210

    Day 1         Day 2        Day 3  
|--********--|--********--|--********--|  
    |__________________________|

The question, IMHO is to know how many minutes the first day, and how many minutes the last day, the intermediate days have 780 minutes.

I've used a subquery just to help in the intermediate calculations.

select
    if(hour(t1) < 9, date(t1) + interval 9 hour , t1) as tIni1,
    date(t1) + interval 22 hour as tFin1,
    date(t2) + interval 9 hour as tIni2,
    if(hour(t2) >  22, date(t2) + interval 22 hour, t2) as tFin2,
    TIMESTAMPDIFF(day, date(t1), date(t2)) numDays
from
    tdt

tIni1 and tFin1 is the period of the first day, and tIni2, tFin2 the period of the last day, obviously first and last day can be the same.

Then calculate minutes of first day + minutes of second day + 780 minutes for every intermediate day.

select numDays, tIni1, tFin1, tIni2, tFin2,
    if (numDays = 0,
        TIMESTAMPDIFF(minute, tIni1, tFin2),
            TIMESTAMPDIFF(minute, tIni1, tFin1)
            + TIMESTAMPDIFF(minute, tIni2, tFin2)
            + (numDays - 1) * 780
        ) as Minutes
from (
        select
            if(hour(t1) < 9, date(t1) + interval 9 hour , t1) as tIni1,
            date(t1) + interval 22 hour as tFin1,
            date(t2) + interval 9 hour as tIni2,
            if(hour(t2) >  22, date(t2) + interval 22 hour, t2) as tFin2,
            TIMESTAMPDIFF(day, date(t1), date(t2)) numDays
        from
            tdt
    ) ti
;

Try it here: http://rextester.com/GDHAB78973

Elapsed Time Between Two Dates for specified time range

4 Answers4