query for time in mysql between hours

Question

how to get a list of rows that is between 17:30 till tomarrow 8:30.

--------------------------------------
| id | user_id | action |     time    |
--------------------------------------
| 1  |   25    | enter  | 1512459905  
| 2  |   19    |  exit  | 1512125105  
| 3  |   31    | enter  | 1514581905  |
--------------------------------------

mysql table have a time column with unix timestamp and i want get a list every day that between 17:30 till 8:30

$t1 = "17:30"; $time1 = strtotime($t1); $t2 = "8:30"; $time2 = strtotime($t2); $sql = "SELECT id,user_id,action from table WHERE time BETWEEN $time1 AND $time2"; — pawan sen, Dec 05 '17 at 08:01
Possible duplicate of [MYSQL query between two timestamps](https://stackoverflow.com/questions/21032335/mysql-query-between-two-timestamps) — Tomin B Azhakathu, Dec 05 '17 at 08:13

score 0 · Answer 1 · answered Dec 05 '17 at 08:01

0

SELECT TIME_TO_SEC(TIMEDIFF(FROM_UNIXTIME('time 1'), FROM_UNIXTIME('time 2')) AS 'time_diff_in_sec' FROM 'your_table';

This can help: Mysql Get Time Diff

answered Dec 05 '17 at 08:01

GarbageCollector

124
1
6

score 0 · Answer 2 · answered Dec 05 '17 at 08:03

$dateStart=strtotime(date("Y-m-d 17:30:00"));
$dateEnd=strtotime(date("Y-m-d 20:30:00"));

This is how you can convert your date into unix timestamp in php.

Then in your query:

SELECT * FROM my_table WHERE time BETWEEN '$dateStart' AND '$dateEnd'

If you don't want to execute your script manually every day you can set up a cronjob to do it for you.

score 0 · Answer 3 · answered Dec 05 '17 at 08:07

0

You can use the TIME() function to extract the time part of the datatime, So you can do something like this: select * FROM table t where TIME(f.time) between '17:30:00' AND '18:30:00'

answered Dec 05 '17 at 08:07

Momen Shaker

141
1
9

query for time in mysql between hours

3 Answers3