Calculate minutes between two time strings
Company: SIG (Susquehanna)
Role: Software Engineer
Category: Data Manipulation (SQL/Python)
Difficulty: medium
Interview Round: Online Assessment
Given two times t1 and t2 in "HH:MM" 24-hour format on the same day with t2 > t1, compute the difference in minutes. Show how you parse the strings, convert to minutes since midnight, and subtract. Example: t1 = "12:23", t2 = "13:24" -> 61.
Overview: This question evaluates proficiency with string parsing and time arithmetic, assessing the ability to interpret HH:MM formatted timestamps and work with temporal values in minute units.
You are given a table of time intervals. Each row has two times t1 and t2 in 24-hour "HH:MM" string format, representing times on the same day with t2 > t1. Write an SQL query to compute the difference between t2 and t1 in minutes for each row.
Your query should:
- Parse the hour and minute components from the strings.
- Convert each time into "minutes since midnight".
- Subtract to get the difference in minutes.
Example: t1 = "12:23", t2 = "13:24" should give 61 minutes.
Tables
time_intervals(id INT, start_time VARCHAR(5), end_time VARCHAR(5))
Hints
- Use SUBSTRING to extract the hour (characters 1-2) and minute (characters 4-5) from each time string.
- Convert hours to minutes (hours * 60), add minutes, and subtract the two totals to get the difference.