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

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.