Getting DB time results when times on different days

[JasonDFR]

sql column's start_time and end_time in the format: 00:00:00 The field type is TIME

I want to select rows where the current time is between start and end times.

When start_time and end_time are on the same day, you can:



SELECT * FROM table WHERE start_time < CURRENT_TIME() AND end_time > CURRENT_TIME();

The problem is when the start_time is 23:00:00 and the end_time is 03:00:00 .

I would like to get the result directly from a sql query, but I'm not sure it is possible. If it is, then lets try that.

Otherwise I'll need to use PHP to end up with the right query.

Any ideas?

[Snookerman]

Try:

SELECT * FROM table WHERE start_time < CURRENT_TIME() OR end_time > CURRENT_TIME();

Good luck!

[JasonDFR]

Good idea, but not successful.

The problem is, start_time 23:00:00 and end_time 03:00:00 .

If the current time is 01:00:00


SELECT * FROM time WHERE 23:00:00 < 01:00:00 AND 03:00:00 > 01:00:00 ;

23:00 is never less than 01:00:00, thus no rows are returned.

Now, 23:00:00 is less than 01:00:00 the next day. However when only storing time in the db there is no date associated with it. This is the problem.

[Snookerman]

Yes, that isn't successful, that is why you should use the one I suggested:

SELECT * FROM time WHERE 23:00:00 < 01:00:00 OR 03:00:00 > 01:00:00 ;

Note the OR instead of the AND.

Good luck!

[JasonDFR]

Right,

The last query I posted wasn't yours. Although when I said, "but not successful," I was referring to your query.

SELECT * FROM time WHERE 23:00:00 < 01:00:00 AND 03:00:00 > 01:00:00 ; is what I want, but when the current time is between 23:00 and 03:00, this query returns 0 rows.

[Snookerman]

Try this then:

SELECT * FROM table WHERE (start_time < CURRENT_TIME()) OR (end_time > CURRENT_TIME());

Good luck!

[JasonDFR]

You may be right! I've got to test this again. I'll let you know.

[JasonDFR]

Nope. Your query returns rows outside of what I need.

if start_time is 21:00 and end_time is 03:00, then this row should only be returned is when the time is between 9PM and 3AM.

My original query is "correct", however when dealing with times only in SQL, the problem arrives when your query involves a different day than the current one.

Good thought, but it isn't going to work.

[Schmoopy]

It's so weird, I had exactly this sort of problem today with one of my scripts :p

But luckily for me, it's for a workplace so it only needs to look for times within 8:00 and 18:00, otherwise it would output an error.

Maybe something like

SELECT * FROM table WHERE (start_time < CURRENT_TIME()) OR (end_time > CURRENT_TIME() AND `date` = $nextday);

Then setup a $nextday variable to add 1 to the day your currently on.

I haven't thought about this a lot so I may be going off on a huuge tangent but try it anyway.

[Snookerman]

I don't really see why my suggestion wouldn't work. You could see the time period as two per day. The first one would be from the beginning of the day until the end_time and the second period would be after the start_time and until the end of the day. Therefore, this should return either if the current time is less than the end_time or greater than the start_time. So only one condition must be met.

If you use this:

SELECT * FROM table WHERE (start_time < CURRENT_TIME()) OR (end_time > CURRENT_TIME());
and your start_time is 21:00:00 and your end_time is 03:00:00 then it will be:

SELECT * FROM table WHERE (21:00:00 < CURRENT_TIME()) OR (03:00:00 > CURRENT_TIME());
You can then look at some of the different possibilities for current_time:

01:00:00 - returns because second criterion is met (03:00:00 > 01:00:00)
02:00:00 - returns because second criterion is met (03:00:00 > 02:00:00)
02:59:59 - returns because second criterion is met (03:00:00 > 02:59:59)
03:00:00 - does not return because no criterion is met
04:00:00 - does not return because no criterion is met
05:00:00 - does not return because no criterion is met
06:00:00 - does not return because no criterion is met
07:00:00 - does not return because no criterion is met
08:00:00 - does not return because no criterion is met
09:00:00 - does not return because no criterion is met
10:00:00 - does not return because no criterion is met
11:00:00 - does not return because no criterion is met
12:00:00 - does not return because no criterion is met
13:00:00 - does not return because no criterion is met
14:00:00 - does not return because no criterion is met
15:00:00 - does not return because no criterion is met
16:00:00 - does not return because no criterion is met
17:00:00 - does not return because no criterion is met
18:00:00 - does not return because no criterion is met
19:00:00 - does not return because no criterion is met
20:00:00 - does not return because no criterion is met
21:00:00 - does not return because no criterion is met
21:00:01 - returns because first criterion is met (21:00:00 < 21:00:01)
22:00:00 - returns because first criterion is met (21:00:00 < 22:00:00)
23:00:00 - returns because first criterion is met (21:00:00 < 23:00:00)
24:00:00 - returns because first criterion is met (21:00:00 < 24:00:00)
00:00:00 - returns because second criterion is met (03:00:00 > 00:00:00)

I wasn't sure if it's 24:00:00 or 00:00:00 but as you can see, this should work as you need it to work.

[Schmoopy]

I don't really see why my suggestion wouldn't work. You could see the time period as two per day. The first one would be from the beginning of the day until the end_time and the second period would be after the start_time and until the end of the day. Therefore, this should return either if the current time is less than the end_time or greater than the start_time. So only one condition must be met.

If you use this:

SELECT * FROM table WHERE (start_time < CURRENT_TIME()) OR (end_time > CURRENT_TIME());
and your start_time is 21:00:00 and your end_time is 03:00:00 then it will be:

SELECT * FROM table WHERE (21:00:00 < CURRENT_TIME()) OR (03:00:00 > CURRENT_TIME());
You can then look at some of the different possibilities for current_time:

01:00:00 - returns because second criterion is met (03:00:00 > 01:00:00)
02:00:00 - returns because second criterion is met (03:00:00 > 02:00:00)
02:59:59 - returns because second criterion is met (03:00:00 > 02:59:59)
03:00:00 - does not return because no criterion is met
04:00:00 - does not return because no criterion is met
05:00:00 - does not return because no criterion is met
06:00:00 - does not return because no criterion is met
07:00:00 - does not return because no criterion is met
08:00:00 - does not return because no criterion is met
09:00:00 - does not return because no criterion is met
10:00:00 - does not return because no criterion is met
11:00:00 - does not return because no criterion is met
12:00:00 - does not return because no criterion is met
13:00:00 - does not return because no criterion is met
14:00:00 - does not return because no criterion is met
15:00:00 - does not return because no criterion is met
16:00:00 - does not return because no criterion is met
17:00:00 - does not return because no criterion is met
18:00:00 - does not return because no criterion is met
19:00:00 - does not return because no criterion is met
20:00:00 - does not return because no criterion is met
21:00:00 - does not return because no criterion is met
21:00:01 - returns because first criterion is met (21:00:00 < 21:00:01)
22:00:00 - returns because first criterion is met (21:00:00 < 22:00:00)
23:00:00 - returns because first criterion is met (21:00:00 < 23:00:00)
24:00:00 - returns because first criterion is met (21:00:00 < 24:00:00)
00:00:00 - returns because second criterion is met (03:00:00 > 00:00:00)

I wasn't sure if it's 24:00:00 or 00:00:00 but as you can see, this should work as you need it to work.

You are still focusing on one day, as Jason said - it spans across multiple days and that is where the problem arises in the MySQL query.

[Snookerman]

Thinking about it as spanning over two days is what creates the problem. The solution is to realize, as I have done, that this can be thought about using just one day. The two time periods are from midnight to 3am and from 9pm to midnight. That is why my code has two criteria, because I split the time period into two parts.

Maybe if I use a concrete example you will understand. Imagine a club that opens at 9pm and closes at 3am every night. You are in town for just one day, you arrive at midnight and leave at midnight. That means that in this day, you have two possibilities to go to the club, first one just after you arrived until 3am when it closes, and the second possibility is after 9pm when it opens again, but you must leave at midnight. Now when you think about it, you have been to the club two times in one day.

Since the query cannot know it's a different day, this way of thinking solves the problem.

[Schmoopy]

But he's already tried the code you posted before and it didn't work so something must be going wrong somewhere...

[JasonDFR]

Here are some rows in the time table:


+------------+----------+
| start_time | end_time |
+------------+----------+
| 00:00:00 | 00:00:00 |
| 18:30:00 | 03:30:00 |
| 12:00:00 | 15:00:00 |
| 09:00:00 | 12:00:00 |
| 03:00:00 | 06:00:00 |
| 08:00:00 | 09:30:00 |
+------------+----------+


I only want rows returned when the current time falls between the start_time and end_time.

The problem with OR is that when the current_time is 08:00:00 for example, every row with a start_time earlier than 08:00:00 is going to be returned and every row with an end_time greater than 08:00:00 will be returned. I only want rows to be returned when the current time falls in between the start and end times.

Here is the OR query and the resulting rows from the table above. Current time is 08:10:00.


mysql> SELECT *
-> FROM `time`
-> WHERE start_time < current_time
-> OR end_time > current_time;
+------------+----------+
| start_time | end_time |
+------------+----------+
| 00:00:00 | 00:00:00 |
| 12:00:00 | 15:00:00 |
| 09:00:00 | 12:00:00 |
| 03:00:00 | 06:00:00 |
| 08:00:00 | 09:30:00 |
+------------+----------+
4 rows in set (0.00 sec)


Same query using AND:


SELECT *
FROM time
WHERE start_time < CURRENT_TIME()
AND end_time > CURRENT_TIME();
+------------+----------+
| start_time | end_time |
+------------+----------+
| 08:00:00 | 09:30:00 |
+------------+----------+
1 row in set (0.00 sec)


This works great when the start time and end time are on the same day.

Below should return one row, 18:30:00 - 03:00:00, but because 03:00:00 is the end_time and is not greater than 20:00:00 ( on the same day ), it does not get returned.


mysql> SELECT *
FROM time
WHERE start_time < '20:00:00'
AND end_time > ' 20:00:00';
Empty set (0.00 sec)


If the query could somehow "know" that 03:00:00 is the next day, then 3AM would be greater than 20:00:00. This is the problem.

Sorry Snookerman, I should have explained better in my first post.

[Snookerman]

Aha, now I understand, from what you posted before, I understood that all time periods go over midnight but since you have both cases you will need a hybrid of AND and OR. I have no idea if this works but try it:

SELECT *
FROM table
WHERE (start_time < CURRENT_TIME() AND start_time > end_time)
OR (end_time > CURRENT_TIME() AND start_time > end_time)
OR (start_time < CURRENT_TIME() AND end_time > CURRENT_TIME() AND start_time < end_time);

Good luck!