CODEWITHSUNDEEP
mysqlsql
logic-unit
logic-unit

Reputation: 4303

MySQL SELECT rows WHERE time is within a specified time window

I have a MYSQL database with the column event_time with timestamps in this format: 2012-07-18 12:54:45.

I need to select any rows that fall into a specified time window e.g. 0700 to 1159. How can I do a SELECT that fetches those rows for any date?

This is what I tried:

SELECT count(id) FROM dataset WHERE event_time >= "07:00" AND <= "11:59"

Upvotes: 0

Views: 844

Answers (2)

Nguyễn Văn Quyền
Nguyễn Văn Quyền

Reputation: 350

Here is how to extract time from timestamp:

SELECT count(id) 
FROM dataset 
WHERE TIME(event_time) BETWEEN "07:00" AND "11:59"

or

SELECT count(id) 
FROM dataset 
WHERE cast(event_time as time) BETWEEN "07:00" AND "11:59"

Both of them work totally fine for me! Hope my answer help you with your question.

Upvotes: 1

logic-unit
logic-unit

Reputation: 4303

Okay, I figured it out. I can cast the datetime column to TIME, and then do a BETWEEN.

SELECT count(id) 
FROM dataset
WHERE TIME(event_time) BETWEEN "07:00"
AND "11:59"

Upvotes: 0

Related Questions