CODEWITHSUNDEEP
mysqlsqlselecttimerange
jmd9qs
jmd9qs

Reputation: 169

MySQL select all records who's timestamps fall within an "hour range", like between 11pm and 4am

I have a table (visits) like this:

+--------------+-------------+------+-----+---------------------+----------------+
| Field        | Type        | Null | Key | Default             | Extra          |
+--------------+-------------+------+-----+---------------------+----------------+
| id           | int(11)     | NO   | PRI | NULL                | auto_increment |
| vis_id       | int(11)     | NO   |     | NULL                |                |
| unit         | int(11)     | NO   |     | NULL                |                |
| time_in      | timestamp   | NO   |     | CURRENT_TIMESTAMP   |                |
| time_out     | timestamp   | NO   |     | 0000-00-00 00:00:00 |                |
| in_username  | varchar(16) | NO   |     | NULL                |                |
| out_username | varchar(16) | NO   |     | NULL                |                |
+--------------+-------------+------+-----+---------------------+----------------+

I would like to be able to select ALL records who's time_in value falls between the hours of 11pm and 4am.

Suggestions?

Upvotes: 0

Views: 2718

Answers (2)

Madhivanan
Madhivanan

Reputation: 13700

select * from visits where hour(in_time)>=23 or hour(in_time)<4

Upvotes: 2

Niels
Niels

Reputation: 49919

You can use the hour method:

SELECT * FROM visits WHERE HOUR(time_in) >= 11 AND HOUR(time_in) < 16

You will need a function to substract the time. If you know the date you should use a between function with date and time. So the MySQL index will be used aswell. With this method HOUR it will not use the index and if the query will be heavy it will take a short period.

Upvotes: 3

Related Questions