CODEWITHSUNDEEP
mysql
Mumpo
Mumpo

Reputation: 548

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:

Upvotes: 1

Views: 61

Answers (2)

Faizan Younus
Faizan Younus

Reputation: 803

Try this. 

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

Hope this helps.

Upvotes: 0

1000111
1000111

Reputation: 13519

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.

Upvotes: 3

Related Questions