Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

MySQL Select: WHERE (time now) = BETWEEN tablevalue AND tablevalue

I'm looking for a way to select the row in which the current time is between two set values in the row. I've set up a table with 3 columns, 2 of them hold a timestamp (HH:MM:SS), the other one a string. Is there a way I can get the string corresponding to the current time? To put it in a more abstract way:

SELECT String FROM TableName WHERE (Current Time) BETWEEN (Lower Limit Time Value) AND (Upper Limit Time Value);

alt text

So basically, based on the current time, my script should output the correct string.

How do I go about this? Thanks!

like image 216
Chris Avatar asked Jan 21 '11 11:01

Chris


People also ask

How can I get date between two dates in MySQL?

To count the difference between dates in MySQL, use the DATEDIFF(enddate, startdate) function. The difference between startdate and enddate is expressed in days.

How to query time in MySQL?

The CURRENT_TIMESTAMP function in the MySQL database returns the current date and time (i.e. the time for the machine running that instance of MySQL). It is given as a value in the 'YYYY-MM-DD hh:mm:ss' format.

How do I select a date range in MySQL?

How to Select rows from a range of dates with MySQL query command. If you need to select rows from a MySQL database' table in a date range, you need to use a command like this: SELECT * FROM table WHERE date_column >= '2014-01-01' AND date_column <= '2015-01-01';

How to write SELECT query for TIMESTAMP in SQL?

To get a day of week from a timestamp, use the DAYOFWEEK() function: -- returns 1-7 (integer), where 1 is Sunday and 7 is Saturday SELECT dayofweek('2018-12-12'); -- returns the string day name like Monday, Tuesday, etc SELECT dayname(now()); To convert a timestamp to a unix timestamp (integer seconds):


2 Answers

In MySQL, timestamp is quite a confusing word.

If they are lowerlimit and upperlimit are TIME columns from 00:00:00 to 23:59:59:

SELECT  String
FROM    TableName
WHERE   CURTIME() BETWEEN lowerlimit AND upperlimit
        OR CURTIME() BETWEEN SUBTIME(upperlimit, '24:00:00') AND lowerlimit
        OR SUBTIME(CURTIME(), '24:00:00') BETWEEN SUBTIME(upperlimit, '24:00:00') AND lowerlimit

This will handle midnight transitions correctly.

like image 172
Quassnoi Avatar answered Sep 24 '22 19:09

Quassnoi


SELECT string_col
FROM your_table
WHERE CURTIME() BETWEEN lower_limit_col AND upper_limit_col
like image 43
LukeH Avatar answered Sep 21 '22 19:09

LukeH