Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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?

like image 612
user1292656 Avatar asked Dec 06 '13 14:12

user1292656


People also ask

How do I select a specific date range in SQL?

SELECT * FROM YourTable. WHERE [dateColumn] >DATEADD(day,1,'4/25/2022') AND [dateColumn] <= DATEADD(day,1,'4/26/2022') AND DATEPART(hh,[dateColumn]) >= 7 AND DATEPART(hh,[dateColumn]) <= 19.

How do I get all the dates between datetime ranges in SQL query?

To find the difference between dates, use the DATEDIFF(datepart, startdate, enddate) function. The datepart argument defines the part of the date/datetime in which you'd like to express the difference. Its value can be year , quarter , month , day , minute , etc.

How can I check data between two dates in SQL?

SELECT * FROM ATM WHERE TRANSACTION_TIME BETWEEN '2005-02-28 21:00:00' AND '2008-12-25 00:00:00';


1 Answers

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
like image 76
Mureinik Avatar answered Oct 21 '22 14:10

Mureinik