Mumpo Mumpo - 4 months ago 13
SQL Question

MySQL Select from last 30 days not selecting today's results

I hope my question has a very dumb solution, but I'm stuck.

I have this query to get the orders from the last 30 days grouped by date.

SELECT
DATE_FORMAT(order_date, '%Y-%m-%d') as day,
COUNT(*) AS num
FROM
orders
WHERE
(order_date >= CURDATE() - INTERVAL 30 DAY)
GROUP BY
DAY(order_date)


This query though only includes results until yesterday, not today's. What am I doing wrong?

Useful information:


  • order_date
    has a
    DATETIME
    format.

  • I've checked and the database has orders with today's date.

  • I have tried changing
    CURDATE()
    with
    NOW
    , or using
    BETWEEN
    with no success.


Answer

Try this:

SELECT
    DATE_FORMAT(order_date, '%Y-%m-%d') as day,
    COUNT(*) AS num
FROM
    orders
WHERE
    (order_date >= CURDATE() - INTERVAL 30 DAY)
GROUP BY day;

Note: In your query you were grouping by DAY(date). Day function returns the day number in a month. So, it's likely the case that the same day of two different months aggregated together in a single slot. That's why you were missing today's result.

Comments