Question 233043 in Mysql Question posted in
The official MySQL documentation can be found here.

mySQL Between Time Range – Where start time can be greater than end time

I tried searching SO for this question and couldn’t find anything related to mySQL.

I have a table that holds various data but for this question, the relevant data is the day of week, start time and end time.

I’m trying to get the records that match a day of the week where now() is within the matching time range. However, the catch is the start time CAN be greater than the end time or vice versa or the time could also just be 00:00:00.

Relevant fields and example data for table page_data:

field_value | start_time | end_time
------------+------------+---------
Monday      | 17:00:00   | 08:00:00
Tuesday     | 17:00:00   | 08:00:00
Wednesday   | 17:00:00   | 08:00:00
Thursday    | 17:00:00   | 08:00:00
Friday      | 17:00:00   | 08:00:00
Saturday    | 00:00:00   | 00:00:00
Sunday      | 00:00:00   | 00:00:00

A few notes:

  • 00:00:00 – 00:00:00 basically means run all day
  • if start time is greater than end time, like 17:00 – 8:00 that would
    mean to run from 5pm to 8am on the next day.

I tried now() between start_time and end_time but that doesn’t cover start_time > end_time.

Below is what I have so far but it returns when it shouldn’t.

10:00:00 is a test time that shouldn’t return anything but it does.

For testing I’m hard coding a time instead of using now()

if I remove or ('10:00:00' between end_time and start_time) then start_time being greater won’t return anything.

select * from page_data
where field_value = 'Friday'
and (
    (start_time = '00:00:00' and end_time = '00:00:00') or
    ('10:00:00' between start_time and end_time) or
    ('10:00:00' between end_time and start_time)
)

I’m stumped on the query that I can use to take into these considerations:

  • Start Time > End Time
  • End Time > Start Time
  • End Time && Start Time = 00:00:00

Thank you

Tags:

2

Answers


  1. 0

    In the situation where start_time > end_time, I’m guessing you only want to match if test_time is greater than start_time, or less than end_time. This is logically equivalent to test_time NOT between end_time and start_time.

    If this logic is correct, this code might be what you need:

    select * from page_data
where field_value = 'Friday'
and (
    (start_time = '00:00:00' and end_time = '00:00:00') or
    (start_time < end_time) and ('10:00:00' between start_time and end_time) or
    (start_time > end_time) and NOT('10:00:00' between end_time and start_time)
)
    Login or Signup to reply.
  2. 0

    Would you prefer this?

    SELECT ...
  FROM easier_to_work_with
 WHERE day_of_week = DAYNAME(CURRENT_DATE)
       AND
       CURRENT_TIME BETWEEN start_time AND end_time;

    It seems to me it’d be easier to work with "notice window" periods specified as time intervals confined to a single day, representing windows that span more than one day as two adjacent windows.

    Then you simply check the current day and current time against the windows.

    You can do this without changing existing application logic or existing table structure:

    CREATE VIEW easier_to_work_with AS
     -- get same day windows
     SELECT ..., field_value as day_of_week, start_time, end_time
       FROM page_data
      WHERE start_time < end_time
      UNION
     -- get all day windows, or partials crossing midnight
     SELECT ..., field_value, start_time, '23:59:59'
       FROM page_data
      WHERE (start_time = '00:00:00' AND end_time = '00:00:00')
              OR
            end_time < start_time
      UNION
     -- get carryover windows from the previous day
     SELECT ...,
            CASE field_value
              WHEN 'Monday'    THEN 'Tuesday'
              WHEN 'Tuesday'   THEN 'Wednesday'
              WHEN 'Wednesday' THEN 'Thursday'
              WHEN 'Thursday'  THEN 'Friday'
              WHEN 'Friday'    THEN 'Saturday'
              WHEN 'Saturday'  THEN 'Sunday'
              WHEN 'Sunday'    THEN 'Monday'
            END, '00:00:00', end_time
       FROM page_data
      WHERE end_time < start_time;
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search