CODEWITHSUNDEEP
mysql
AnchovyLegend
AnchovyLegend

Reputation: 12538

MySQL searching timestamp columns by date only

I am building out a query to search a table by a timestamp column value. An example of the format I am passing to the api is 2018-10-10. The user has the ability to select a date range. Often times the date range start date is 2018-10-10 and end date is the same day, 2018-10-10. The below doesn't seem to do the trick. What is the simplest way to accomplish this without having to specify the time? Obviously, I'd like to query for the entire day of 2018-10-10 from start to end of day.

SELECT
    count(*)
FROM
    contact
WHERE
    created_at >= '2018-10-10'
    AND created_at <= '2018-10-10';

Upvotes: 0

Views: 1089

Answers (1)

Madhur Bhaiya
Madhur Bhaiya

Reputation: 28834

The problem here is that Timestamp datatype will have HH:MM:SS (time) values also. While comparing a datetime with date, MySQL would automatically assume 00:00:00 as HH:MM:SS for the date value.

So, 2018-10-10 12:23:22 will not match the following condition: created_at <= '2018-10-10'; since it would be treated as: 2018-10-10 12:23:22 <= '2018-10-10 00:00:00, which is false

To handle this, you can add one day to the date (date_to in the filter), and use < operator for range checking.

SELECT
    count(*)
FROM
    contact
WHERE
    created_at >= '2018-10-10'
    AND created_at < ('2018-10-10' + INTERVAL 1 DAY);

Upvotes: 4

Related Questions