How can I limit query results to a certain time range each day?

Question

I have an app that displays ads from campaigns. I limit display of each campaign's set of ads to a specific date range per campaign. My "campaigns" table looks like this:

campaigns
  id : integer
  start_date : date
  end_date : date

I now need to be able to optionally limit the display of campaign ads to a specific time range each day. So now my table looks like

campaigns
  id : integer
  start_date : date
  end_date : date
  start_time : time, default: null
  end_time : time, default: null

And so my [MySQL] query looks like this:

SELECT
  ads.*
FROM
  ads
  INNER JOIN campaigns ON campaigns.id = ads.campaign_id
WHERE
  campaigns.start_date <= "2014-08-05" AND
  campaigns.end_date >= "2014-08-05" AND
  campaigns.start_time <= "13:30"
  campaigns.end_time >= "13:30";

(Dates and times are actually injected using the current date/time.)

This works fine. However, because I store start_time and end_time in UTC time, sometimes end_time is earlier than start_time; for example, in the database:

start_time = 13:00 (08:00 CDT)
end_time = 01:00 (20:00 CDT)

How can I adjust the query (or even use code) to account for this?

where is start_time and end_time coming from? - meaning how do you have UTC and CDT? where is the comparrison or different time zones coming from — John Ruddell, Aug 05 '14 at 13:38
@Strawberry Nope. I need the time to be for every day. Putting the start date and start time together would signify the campaign should start on a certain date and time instead of every day in the date range. — Chad Johnson, Aug 05 '14 at 13:54
@John Ruddell Sorry, I made a typo. Should have inserted the current time into the query. It's fixed. — Chad Johnson, Aug 05 '14 at 14:03

score 3 · Accepted Answer · answered Aug 05 '14 at 13:54

The problem is harder than it looks because either the campaigns or the comparison time span can go over the date boundary. Then, there is an additional complication. If the campaign says that it is running until 1:00 a.m., is the end date on the current date or the next date? In other words, for your example, 1:00 a.m. on 2014-08-06 should really be counted as 2014-08-05.

My recommendation, then, is to switch to local time. If your campaigns don't span midnight, then this should solve your problem.

If you only care about the campaigns themselves spanning midnight, you can do something like:

WHERE campaigns.start_date <= '2014-08-05' AND
      campaigns.end_date >= '2014-08-05' AND
      ((campaigns.start_time <= campaigns.end_time and
        campaigns.start_time >= '13:00' and
        campaigns.end_time <= '18:00'
       ) or
       (campaigns.start_time >= campaigns.end_time and
        (campaigns.start_time >= '13:00' or
         campaigns.end_time <= '18:00'
        )
       )

Note that when the end_time is greater than the start time, then you want times greater than the start time or less than the end time. In the normal case, you want times greater than the start time and less than the end time.

You can do something similar if you only care about the comparison time period. Combining the two seems quite complicated.

@JohnRuddell . . . Yes and no. As I think about it, the problem is that the database is storing dates in UTC format but the business requirement is for calendar dates, presumably on the central time zone. There is a fundamental problem, because the date portions don't line up. `convert_tz()` might be able to help with that problem. — Gordon Linoff, Aug 05 '14 at 15:09
without some data it's hard to tell whats the difference between the table and the parameter.. but it would be interesting to see how that would work.. — John Ruddell, Aug 05 '14 at 15:17
@Gordon Linoff A variation of your solution worked! Thank you much. I will post an answer here explaining, but I'll give credit to you. — Chad Johnson, Aug 05 '14 at 20:54

JCalcines · Answer 2 · 2014-08-05T14:12:28.177

You just can check the both ways.

 SELECT
      ads.*
    FROM
      ads
      INNER JOIN campaigns ON campaigns.id = ads.campaign_id
    WHERE "2014-08-05" BETWEEN campaigns.start_date AND campaigns.end_date
      AND ("13:30" BETWEEN campaigns.start_time AND campaigns.end_time
        OR "13:30" BETWEEN campaigns.end_time AND campaigns.start_time)

If your range is 08:00 to 16:00 the first part will find your results and the second one none because the range is wrong.

If your range is 16:00 to 08:00 the first part won't find any result and the second one will give them you.

score 0 · Answer 3 · answered Aug 05 '14 at 20:58

So, Gordon Linoff's answer sent me in the right direction. Here's the query that worked:

SELECT
  ads.*
FROM
  ads
  INNER JOIN campaigns ON campaigns.id = ads.campaign_id
WHERE
    "2014-08-05" BETWEEN campaigns.start_date AND campaigns.end_date AND

    (campaigns.start_time IS NULL OR campaigns.end_time IS NULL) OR (
      ((campaigns.start_time < campaigns.end_time AND
        campaigns.start_time <= "13:30:00" AND campaigns.end_time >= "13:30:00") OR

       (campaigns.start_time > campaigns.end_time AND
        ((campaigns.start_time <= "13:30:00" AND campaigns.end_time <= "13:30:00") OR
         (campaigns.start_time >= "13:30:00" AND campaigns.end_time >= "13:30:00"))))
    );

How can I limit query results to a certain time range each day?

3 Answers3