Clever Geek Handbook

How to get current current time offset from Mysql database

Here is my table: shift_master

shift(varchar)     | start_time(varchar) | end_time(varchar)    
Morning            | 06:00 AM            | 02:00 PM    
AfterNoon          | 02:00 PM            | 10:00 PM    
Evening            | 10:00 PM            | 06:00 AM

I need one query in which I will pass the current time and it will give the shift record which is currently ongoing.

I really appreciate your help.

+3


source to share


3 answers


First change your datatype for start_time and end_time to a timestamp and enter the time in your format and then

SELECT shift FROM shift_master WHERE start_time <= NOW() AND end_time > NOW();


Difficult to compare time stored as varchar with current_time. If possible, follow the steps above to make your job easier.

0


source


Try using this



SELECT shift FROM shift_master WHERE TIME(NOW()) BETWEEN TIME(STR_TO_DATE(start_time,'%h:%i %p')) AND TIME(STR_TO_DATE(end_time,'%h:%i %p'))
0


source


Try this query

set @now_time = UNIX_TIMESTAMP( now() ) % 86400;

SELECT shift
FROM ( SELECT shift, UNIX_TIMESTAMP( STR_TO_DATE(start_time,'%h:%i %p') ) % 86400 as start, UNIX_TIMESTAMP( STR_TO_DATE(end_time,'%h:%i %p') ) % 86400 as end
FROM shift_master ) M
WHERE ( start < end AND @now_time BETWEEN start AND end - 1 ) OR
( start > end AND ( @now_time > start or @now_time < end ) )
0


source






More articles:


All Articles