CODEWITHSUNDEEP
mysqlsqldatedatetimefiltering
shubham
shubham

Reputation: 457

get data between two dates but I have two separate columns for date and time respectively

I have two columns in my database one for date(receive date) in 'YYYY-MM-DD' format and one column for time(receive time) in 'hours:minutes:seconds' format

table data

I want to query for data where receive_date starts from '2022-02-10' and receive_timegreter than '15:00:00' till receive_date is '2022-02-17' and receive_time is less than '17:00:00'.

Keep in mind that receive_time for date '2022-02-10' is greater than '15:00:00' but for the next date '2022-02-18' the receive_time not matters till the end date, Similarly for the end_date '2022-02-17' I want data till receive_time is '17:00:00'.

Upvotes: 1

Views: 3017

Answers (1)

GigiSan
GigiSan

Reputation: 1272

You could join the two strings with a CONCAT() and then check if the result is between your date/time range. Like this:

SELECT * FROM aeron.Device_200011555585431
WHERE CONCAT(Receive_Date, ' ', Receive_Time)
    BETWEEN '2022-02-10 15:00:00' AND '2022-02-17 17:00:00';

Keep in mind that CONCAT() might be a bit expensive as a comparison term if the table you're querying has millions of records.

UPDATE

An alternative to using CONCAT() might checking every combination of Receive_Date and Receive_Time you need to cover the required time span.

Something like:

SELECT * FROM aeron.Device_200011555585431
WHERE ( Receive_Date = '2022-02-10' AND Receive_Time > '15:00:00' )
   OR ( Receive_Date > '2022-02-10' AND Receive_Date < '2022-02-17' )
   OR ( Receive_Date = '2022-02-17' AND Receive_Time < '17:00:00' );

Should be less expensive than CONCAT() if Receive_Date and Receive_Time are indexed, not sure in the opposite case.

Another alternative would be refactoring the table by fusing the two columns in a proper DATETIME type.

Upvotes: 5

Related Questions