Choose between a date range and a specific time range

Question

Is there a way to select entries between dates and a specific time range. For example, all entries from 2013-11-01 to 2013-11-30 between the hours from 05:00 to 15:00. Here is what I have done so far.

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 will I set a specific time range?

Answer 1

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

 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 

Answer 2

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

Answer 3

add the following line to the request and set your hour BETWEEN

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

Answer 4

Like others, HOUR() can help you.

But HOUR () or DATE () cannot use INDEX . To make the query faster, I suggest adding a time_created TIME column and saving only a portion of TIME. after that ADD INDEX(date_created, time_created) . finally, with the request below, you can get strings at 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'