Search between two dates with specific time with each date

Question

I have two dates. 2019-01-01(fromDate) and 2019-01-10(toDate). And now I want to search only the 13:00 to 16:00 time of each date. May I ask if is it possible using query only? Any answer is much appreciated

SELECT * 
FROM table 
WHERE fromDate >= 2019-01-01 AND toDate <= 2019-01-10

Answer 1

You can try to use STR_TO_DATE function with the format and HOUR function.

SELECT * 
FROM table 
WHERE 
    fromDate >= STR_TO_DATE('2019-01-01', '%Y-%m-%d') 
AND 
    toDate <= STR_TO_DATE('2019-01-10', '%Y-%m-%d')
AND
    (HOUR(fromDate) BETWEEN 13 AND 16 OR HOUR(toDate) BETWEEN 13 AND 16)

Answer 2

Use separate conditions on the date and on the time:

SELECT * 
FROM table 
WHERE fromDate >= '2019-01-01' AND
      toDate < '2019-01-11' and
      time(fromdate) between '13:00:00' and '16:00:00' and
      time(todate) between '13:00:00' and '16:00:00;

Or, if you don't want 16:00:00 exactly, you can use hour():

SELECT * 
FROM table 
WHERE fromDate >= '2019-01-01' AND
      toDate < '2019-01-11' and
      hour(fromdate) in (13, 14, 15) and
      hour(todate) in (13, 14, 15)