Question 63808 in PhpMyAdmin Question posted in
The official documentation can be found here.

Count total for today report case query php – Phpmyadmin

I’ve 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) ASs total_today_case
FROM report
WHERE report_detection_date = CURRENT_DATE();

DB Structure

Tags:

3

Answers


  1. 0

    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.

    Login or Signup to reply.
  2. 0

    You might want to 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();
    Login or Signup to reply.
  3. 0

    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)
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search