SQL - Split open & Close time Into intervals of 30 minutes

Question

Purpose: I work in Hospitality Industry. I want to understand at what time the Restaurant is full and what time it is less busy. I have the opening and closing times, I want to split it 30 minute interval period.

I would really appreciate if you could ease help me.

Thanking you in advance

Table

Check# Open CloseTime
25484   17:34   18:06
25488   18:04   21:22

Output

Check# Open Close   Duration
25484   17:34   18:00   0:25
25484   18:00   18:30   0:30
25488   18:08   18:30   0:21
25488   18:30   19:00   0:30
25488   19:00   19:30   0:30
25488   19:30   20:00   0:30
25488   20:00   20:30   0:30
25488   20:30   21:00   0:30
25488   21:00   21:30   0:30

I am new to SQL. I am good at Excel, but due to its limitations i want to use SQL. I just know the basics in SQL. I have tried on the google, but could not find solution to it. All i can see use of Date Keywords, but not the Field name in the code, hence i am unable to use them.

Did you google this? There are a lot of questions and answers concerning this topic, as example https://stackoverflow.com/questions/27046814/get-30-minutes-interval-data-between-start-and-end-time-in-mysql — Jonas Metzler, Apr 23 '22 at 10:47
You must tell us which make and version of SQL you use. The date and time manipulation functions are not standardized. Please [edit] your tags — O. Jones, Apr 23 '22 at 11:08

score 0 · Answer 1 · answered Apr 23 '22 at 21:54

0

Could you try this, it works in MySQL 8.0:

WITH RECURSIVE times AS (
  SELECT time '0:00' AS `Open`, time '0:30' as `Close`
   UNION ALL
  SELECT addtime(`Open`, '0:30'), addtime(`Close`, '0:30')
    FROM times
   WHERE `Open` < time '23:30'
)
SELECT c.`Check`,
       greatest(t.`Open`, c.`Open`) `Open`,
       least(t.`Close`, c.`CloseTime`) `Close`,
       timediff(least(t.`Close`, c.`CloseTime`), greatest(t.`Open`, c.`Open`)) `Duration`
  FROM times t
  JOIN checks c ON (c.`Open` < t.`Close` AND c.`CloseTime` > t.`Open`);

| Check | Open     | Close    | Duration |
| ----- | -------- | -------- | -------- |
| 25484 | 17:34:00 | 18:00:00 | 00:26:00 |
| 25484 | 18:00:00 | 18:06:00 | 00:06:00 |
| 25488 | 18:04:00 | 18:30:00 | 00:26:00 |
| 25488 | 18:30:00 | 19:00:00 | 00:30:00 |
| 25488 | 19:00:00 | 19:30:00 | 00:30:00 |
| 25488 | 19:30:00 | 20:00:00 | 00:30:00 |
| 25488 | 20:00:00 | 20:30:00 | 00:30:00 |
| 25488 | 20:30:00 | 21:00:00 | 00:30:00 |
| 25488 | 21:00:00 | 21:22:00 | 00:22:00 |

->Fiddle

answered Apr 23 '22 at 21:54

Kombajn zbożowy

8,755
3
28
60

Thank you for your time. MySQL functions are different to SQL functions, hence I am unable to us it. – Hari Apr 27 '22 at 20:38
Hi Jones, Sorry for the delayed reply. I am using SQL Server Management Studio - 15.0.18386.0 – Hari Apr 27 '22 at 20:41
I was browsing the Net to find the logic, but could not find it. Most of them were hard coded and did not know to substitute the hard coded variable with column name / field. – Hari Apr 27 '22 at 20:44
The output which I need is exactly what Fiddle has mentioned. – Hari Apr 27 '22 at 20:45
I added an SQL Server version as a separate answer (just in case someone finds MySQL useful too...) – Kombajn zbożowy Apr 28 '22 at 07:48

score 0 · Answer 2 · answered Apr 28 '22 at 07:47

This works for SQL Server 2019:

WITH times([Open], [Close]) AS (
  SELECT cast({t'00:00:00'} as time) as "Open",
         cast({t'00:30:00'} as time) as "Close"
   UNION ALL
  SELECT dateadd(minute, 30, [Open]), dateadd(minute, 30, [Close])
    FROM times
   WHERE [Open] < cast({t'23:30:00'} as time)
)
SELECT c.[Check],
       iif(t.[Open] > c.[Open], t.[Open], c.[Open]) as [Open],
       iif(t.[Close] < c.[CloseTime], t.[Close], c.[CloseTime]) as [Close],
       datediff(minute,
                iif(t.[Open] > c.[Open], t.[Open], c.[Open]),
                iif(t.[Close] < c.[CloseTime], t.[Close], c.[CloseTime])) Duration
  FROM times t
  JOIN checks c ON (c.[Open] < t.[Close] AND c.[CloseTime] > t.[Open]);

Check  | Open              | Close             | Duration
25484  | 17:34:00.0000000  | 18:00:00.0000000  | 26
25484  | 18:00:00.0000000  | 18:06:00.0000000  | 6
25488  | 18:04:00.0000000  | 18:30:00.0000000  | 26
25488  | 18:30:00.0000000  | 19:00:00.0000000  | 30
25488  | 19:00:00.0000000  | 19:30:00.0000000  | 30
25488  | 19:30:00.0000000  | 20:00:00.0000000  | 30
25488  | 20:00:00.0000000  | 20:30:00.0000000  | 30
25488  | 20:30:00.0000000  | 21:00:00.0000000  | 30
25488  | 21:00:00.0000000  | 21:22:00.0000000  | 22

->Fiddle

Thank you Kombajn zbożowy for taking time to help me. I am able to modify this code for any time interval - 20 minute interval or hourly, etc. — Hari, Apr 28 '22 at 14:39

SQL - Split open & Close time Into intervals of 30 minutes

2 Answers2