当前位置: 动力学知识库 > 问答 > 编程问答 >

mysql - SQL Select data from previous month

问题描述:

I am able using the code below to select data from any number of days in the past but what if I only want the data from the previous month, e.g. from 60 days ago to 30 days ago.

I thought I might be able to use INTERVAL 60 - 30, but I am not sure that is working...

SELECT

product,

COUNT(OrderNumber) AS CountOf

FROM

orders

WHERE

STATUS = 'booking' AND

Date(OrderDate) <= CURDATE() AND

Date(OrderDate) > DATE_SUB(CURDATE(),INTERVAL 30 DAY)

GROUP BY

product

ORDER BY CountOf DESC

Thoughts?

网友答案:

This is too long for a comment:

Date(OrderDate) < DATE_SUB(CURDATE(), INTERVAL 30 DAY) AND 
Date(OrderDate) >= DATE_SUB(CURDATE(), INTERVAL 60 DAY)

Note: if OrderDate is already a date, then don't use the DATE() function. It can prevent the use of indexes.

Even if OrderDate has a time component, you probably still don't need the DATE() function.

网友答案:

Do you know about the MySQL functions MONTH() and NOW()? You can use these in your where clause:

MONTH(OrderDate) = MONTH(NOW()) - 1

Of course, this needs to be fine tuned for example for years (to get December if it is January).

网友答案:

I believe that the DATE_SUB() function supports the use of MONTH as an interval, so you should be able to use that if you wanted the data for the previous month from the current date :

Date(OrderDate) > DATE_SUB(CURDATE(),INTERVAL 1 MONTH)
网友答案:

You can use something like this

SELECT
    product,
    COUNT(OrderNumber) AS CountOf
FROM
    orders
WHERE
    STATUS = 'booking' AND
    YEAR(orderdate) = YEAR(CURRENT_DATE - INTERVAL 1 MONTH)
    AND MONTH(orderdate) = MONTH(CURRENT_DATE - INTERVAL 1 MONTH)   
GROUP BY
    product
ORDER BY CountOf DESC
分享给朋友:
您可能感兴趣的文章:
随机阅读: