CODEWITHSUNDEEP
mysqlsql
Yanis Boucherit
Yanis Boucherit

Reputation: 714

Find all rows between two hours of the day

Here's my problem :

I have a table like this :

id  date                name
543 2012-07-30 09:49:08 test
544 2012-07-30 10:54:12 test2
545 2012-08-01 10:54:12 test3

The thing is, I want to select all the records that were registred today between (for exemple) 9 and 12. How can I achieve this ?

I know how to do it like "HOURS(date) between 9 and 12" but I want MySQL to take in consideration the date of the day.

Thanks !

Upvotes: 1

Views: 2619

Answers (3)

pQd
pQd

Reputation: 126

update - this should be even better provided that you have the date column indexed:

 SELECT * FROM table WHERE 
  date>=str_to_date(concat("2012-02-03"," 09"),"%Y-%m-%d %h") 
 AND 
  date<=str_to_date(concat("2012-02-03"," 12"),"%Y-%m-%d %h")

old version:

SELECT * FROM table WHERE DATE(date)=DATE(NOW()) AND HOUR(date)>=9 AND HOUR(date)<=12

this is not very optimal. ideally add separate [indexed] column that will contain the date-part of your timestamps.

alternatively build in your code the query using current date concatenated with lower/upper hour boundaries.

Upvotes: 1

Madhivanan
Madhivanan

Reputation: 13700

If the date column is index, this will give you better performance

where   date_col>=date_add(current_date,interval 9 hour)
    date_col<=date_add(current_date,interval 12 hour)

Upvotes: 0

Jason Squires
Jason Squires

Reputation: 27

If you have sql 2008 you can do the following:

select 
ID
,cast([Date] as Date) as dt
,cast([Date] as Time) as tm
,name
from table
where cast([Date] as Time) BETWEEN '09:00:00' AND '12:00:00'

Upvotes: 0

Related Questions