CODEWITHSUNDEEP
mysqlsql
Zane Claes
Zane Claes

Reputation: 14954

Correct MySQL Structure for a Time Range for Query Optimization?

I have a scenario where I want to be able to SELECT rows from a MySQL table, but exclude rows where the current time-of-day is inside a time-range.

Example: The "quiet" period for one row is 10pm - 8:30am. My SQL SELECT statement should not return that row if the current server time is after 10pm or before 8:30am.

Example 2: The "quiet period" is NULL and ignored.

Example 3: A new row is created with a quiet period from 9:53am to 9:55am. If the current server time is in that 2-minute window, the row is not returned by the SELECT.

My question: What data format would you use in the database, and how would you write the query?

I have thought about a few different approaches (defining start_time as one column and duration as another, defining both in seconds... or using Date stamps... or whatever). None of them seem ideal and require a lot of calculation.

Thanks!

Upvotes: 0

Views: 294

Answers (1)

liquorvicar
liquorvicar

Reputation: 6106

I would store the start and end dates as MySQL native TIME fields. You would need to consider ranges that span midnight as two separate ranges but you would be able to query the table like this, To find all current quiet periods

SELECT DISTINCT name FROM `quiet_periods` 
WHERE start_time<=CURTIME() AND CURTIME()<=end_time

Or to find all non-active quiet periods

SELECT name FROM quiet_periods WHERE name NOT IN (
  SELECT name FROM `quiet_periods` 
  WHERE start_time<=CURTIME() AND CURTIME()<=end_time
)

So with sample data

id  --  name        -- start_time -- end_time
 1  --  late_night  -- 00:00:00   -- 08:30:00
 2  --  late_night  -- 22:00:00   -- 23:59:59
 3  --  null_period --   NULL     --   NULL
 4  --  nearly_10am -- 09:53:00   -- 09:55:00

At 11pm this would return

null_period
nearly_10am

from the second query.

Depending on performance and how many rows you had you might want to refactor the second query into a JOIN and probably add the relevant INDEXes too.

Upvotes: 2

Related Questions