CODEWITHSUNDEEP
javamysqlsqldatabase
Madan Madan
Madan Madan

Reputation: 684

MYSQL using TIME Between in TimeRange

I am facing a weird problem related with MySQL time that I am not able to solve at all. 

Table:landing
landingPage (varchar) - mylanding.html
starttime (time): 18:00:00
endTime (time) : 04:00:00

The requirement is to display the landingPage between 18:00:00 and 04:00:00 which is 6 pm to 4 am. I am using following query 

select landingPage from landing where currentTime between starttime and endtime.

The above query works from 18:00:00 to 23:59:59. But after midnight it doesn't show any values. For an instance 

select landingPage from landing where '02:00:00' between starttime and endtime.

I have even tried the following query but after midnight the same issue. 

select landingPage from landing where currentTime>starttime AND currentTime<endtime

It's a Java application and should I use AM/PM logic here ?

Upvotes: 0

Views: 112

Answers (1)

Tunaki
Tunaki

Reputation: 137309

The problems comes from the fact that after midnight, the hour goes back to 00:00:00 so the interval [18:00:00, 04:00:00] is empty: when the days are not considered, no hour can be greater than 18h while being lower than 4h on the same day.

You could implement the following logic: show the landing page

  • if the hour is lower than 04:00:00: it would mean that the hour is between midnight and 04:00:00;
  • or if the hour is greather than 18:00:00: it would mean that the hour is between 18:00:00 and midnight.

The query becomes:

select landingPage from landing where currentTime < endTime OR currentTime > startTime

Upvotes: 1

Related Questions