Ive this query but it works only for DATE type. But my column type is DATETIME. How can i change this query to works on DATETIME type? I need to get output for todays report cases.
SELECT COUNT(report_id) as total_today_case from report where report_detection_date = CURRENT_DATE();
CodePudding user response：
Are you looking to count items that meet a specific condition on a table/view? If so, I don't think the db structure would matter. We'd need to understand what you need counted and the field names. After that it would be a simple SELECT DISTINCT COUNT(*) FROM table_abc WHERE condition situation.
CodePudding user response：
where report_detection_date = CURRENT_DATE();
You're comparing apples and oranges. CURRENT_DATE() has a time value of "00:00:00" (midnight). So it never equals a date and time value, except at midnight
| Column Value | Current_Date() | Is Equal? |
|---|---|---|
| 2022-03-08 00:00:00 | 2022-03-08 00:00:00 | YES |
| 2022-03-08 08:12:14 | 2022-03-08 00:00:00 | NO |
| 2022-03-08 14:15:22 | 2022-03-08 00:00:00 | NO |
| 2022-03-08 18:15:22 | 2022-03-08 00:00:00 | NO |
| 2022-03-08 23:15:22 | 2022-03-08 00:00:00 | NO |
| 2022-03-08 23:59:59 | 2022-03-08 00:00:00 | NO |
To keep the query sargable, the better way to query a datetime field is:
WHERE ColumnName >= {TodaysDateAtMidnight}
AND ColumnName < {TomorrowsDateAtMidnight}
.. or more specifically
WHERE ColumnName >= CURRENT_DATE()
AND ColumnName < Date_Add(CURRENT_DATE(), interval 1 DAY)
CodePudding user response：
You might wanna format the report_detection_date:
SELECT COUNT(report_id) as total_today_case FROM report WHERE DATE_FORMAT(report_detection_date, "%Y-%m-%d") = CURRENT_DATE();