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);
So basically, based on the current time, my script should output the correct string.
How do I go about this? Thanks!
To count the difference between dates in MySQL, use the DATEDIFF(enddate, startdate) function. The difference between startdate and enddate is expressed in days.
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 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';
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):
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.
SELECT string_col
FROM your_table
WHERE CURTIME() BETWEEN lower_limit_col AND upper_limit_col
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With