CODEWITHSUNDEEP
mysqlsqlselecttime
user1292656
user1292656

Reputation: 2560

Select between date range and specific time range

Is there a way to Select records between dates and specific time range. For example all the records from 2013-11-01 to 2013-11-30 between hours 05:00 to 15:00. Here what i made until now.

    select count(*) as total from tradingaccounts accounts
inner join tradingaccounts_audit audit on audit.parent_id = accounts.id
where date(audit.date_created) between date('2013-11-01 00:00:00') AND date('2013-11-01 23:59:59')

But how am I going to set the specific time range?

Upvotes: 8

Views: 19049

Answers (5)

cccandy
cccandy

Reputation: 52

If you use date(audit.date_created), the index on date_created field could not take effect.

Just simply use where audit.date_created >= 'xx-xx-xx' and audit.date_created < 'xx-xx-xx'

Upvotes: 0

Jason Heo
Jason Heo

Reputation: 10246

As others answered, HOUR() could help you.

But HOUR() or DATE() cannot use INDEX. To make query faster, I suggest that add time_created TIME column and save only TIME part. after that ADD INDEX(date_created, time_created). finally with below query, you can retrieve rows with high speed.

where audit.date_created between '2013-11-01 00:00:00' AND '2013-11-01 23:59:59'
  AND audit.time_created BETWEEN '05:00:00' AND '15:00:00'

Upvotes: 2

kashan
kashan

Reputation: 101

add following line in query and set your hour in BETWEEN

and time(audit.date_created) between '00:00:01' AND '01:00:00'

Upvotes: 0

Mureinik
Mureinik

Reputation: 312219

You can use the HOUR function to add an additional condition on the hours:

select count(*) as total from tradingaccounts accounts
inner join tradingaccounts_audit audit on audit.parent_id = accounts.id
where date(audit.date_created) between date('2013-11-01 00:00:00') AND date('2013-11-01 23:59:59') 
AND HOUR (audit.date_created) BETWEEN 5 AND 15

Upvotes: 10

Alex Pliutau
Alex Pliutau

Reputation: 21937

Replace your DATE function, it skips time part. Use TIMESTAMP instead.

Upvotes: 0

Related Questions