CODEWITHSUNDEEP
mysqldatebetween
Karuthedan
Karuthedan

Reputation: 93

mysql query with between date with time interval

I have a table with created_time field. There is a requirement to find out all entries between two dates, between a particular time interval. For example, if I want to find all entries between April 1st 2018 to April 30th 2018 with time interwal between 2.30 PM to 4.30 PM, what would be the ideal query ?

select * from my_table where created_time between '2018-04-01
14:30:00' and '2018-04-30 16:30:00'

Upvotes: 1

Views: 620

Answers (1)

D-Shih
D-Shih

Reputation: 46249

You need to split compare Date value and Time value.

You can try this query.

TestDLL

CREATE TABLE my_table (
    created_time  DATETIME
);

INSERT INTO my_table VALUES ('2018-04-01 14:00:00');
INSERT INTO my_table VALUES ('2018-04-01 14:50:00');
INSERT INTO my_table VALUES ('2018-04-04 10:00:00');
INSERT INTO my_table VALUES ('2018-04-01 15:00:00');

Query

select * from my_table 
where 
    (created_time between '2018-04-01' and '2018-04-30') 
AND 
    (CAST(created_time AS time) > CAST('14:30:00' AS time) AND CAST(created_time AS time) < CAST('16:30:00' AS time))

[Results]:

|         created_time |
|----------------------|
| 2018-04-01T14:50:00Z |
| 2018-04-01T15:00:00Z |

SQLFiddle

Upvotes: 2

Related Questions