选择在一个给定的日期创建的所有记录

Given a table orders:

给定一个表的订单:

+-----+---------+-------------------------+
| id  |  price  |         created_at      |
+-----+---------+-------------------------+
|  1  |  16.50  | 2017-02-28 12:52:00.824 |
|  2  |  22.00  | 2017-10-03 15:12:39.107 |
|  3  |  50.00  | 2017-12-03 12:54:42.658 |
|  4  |  12.00  | 2018-01-02 07:21:47.808 |
|  .  |   .     |             .           |
|  .  |   .     |             .           |
|  .  |   .     |             .           |
+-----+---------+-------------------------+

and current date:

当前日期:

+---------------------+
| NOW()               |
+---------------------+
| 2018-01-03 10:33:14 |
+---------------------+

I'd like to select all records that were created on current day any months ago. So for above data my query should return:

我想选择在任何一个月前的当天创建的所有记录。因此，对于以上数据，我的查询应该返回:

+-----+---------+-------------------------+
| id  |  price  |         created_at      |
+-----+---------+-------------------------+
|  2  |  22.00  | 2017-10-03 15:12:39.107 |
|  3  |  50.00  | 2017-12-03 12:54:42.658 |
+-----+---------+-------------------------+

But there are some edge cases for the last day of month:

但是在这个月的最后一天，也有一些边缘病例:

if it's 31-days month, it's trivial
如果是一个月31天，那是微不足道的
if it's 30-days month, the query should return all records created on 30th and 31st day of month
如果是30天月，查询应该返回在30天和31天创建的所有记录
if it's February in a leap year, the query should return all records created on 29th, 30th and 31st day of month
如果是闰年的2月，查询应该返回在29、30和31日创建的所有记录
if it's February in a normal year, the query should return all records created on 28th, 29th, 30th and 31st day of month
如果是正常年份的2月，查询应该返回在28、29、30和31日创建的所有记录

What I have tried is something like this:

我试过的是这样的:

SELECT * FROM orders
JOIN (
  SELECT id, PERIOD_DIFF(
    DATE_FORMAT(NOW(), "%Y%m"),
    DATE_FORMAT(created_at, "%Y%m")
  ) AS diff
  FROM orders
) AS periods
ON orders.id = periods.id
WHERE DATEDIFF(created_at + INTERVAL diff MONTH, NOW()) = 0;

But it doesn't cover the edge cases and I believe there is a smarter way (maybe without a subquery) to achieve the expected results.

但它并没有涵盖边缘情况，我相信有一种更聪明的方法(可能没有子查询)来实现预期的结果。

EDIT: To give you more context - what I need is a kind of a loop. I have a cron job scheduled to run once a day at midnight. This job should select all ids of orders that were created on this day any months ago and then refresh some other data associated with those ids. The important part is to refresh this data exactly once every month - that's why the last day of months is so crucial.

For example, given following creation dates:

例如，给定以下创建日期:

DATES = [
  2015-05-30, 2016-02-29, 2016-10-03,
  2016-12-31, 2017-05-28, 2018-01-03
]

+---------------+------------------------------------+
|     NOW()     |         SHOULD BE INCLUDED         |
+---------------+------------------------------------+
|  2018-01-03   | 2016-10-03, 2018-01-03             |
|  2018-02-28   | 2016-02-29, 2016-12-31, 2017-05-28 |
|  2018-04-30   | 2015-05-30, 2016-12-31             |
|  2018-10-31   | 2016-12-31                         |
+---------------+------------------------------------+

3 个解决方案

#1

Can I suggest a slight simplification Walerian?

我能建议一个稍微简化一下的Walerian式吗?

SELECT 
    *

FROM 
    orders

WHERE 
    (
        DAYOFMONTH(created_at) = DAYOFMONTH( NOW() ) --monthdays that match
    )
    OR
    (
        (  DAYOFMONTH(  LAST_DAY( NOW() )  ) = DAYOFMONTH( NOW() )  ) --base date is end of month
        AND
        (  DAYOFMONTH(created_at) > DAYOFMONTH( NOW() )  ) --target monthdays are beyond base monthday
    )

Incidentally I don't have a MySQL environment, so I'm just taking it on trust that these are the correct functions in MySQL.

顺便说一句，我没有MySQL环境，所以我相信这些是MySQL中正确的函数。

#2

Use DAYOFMONTH() function to compare the day of the NOW() and the created_at. Like this:

使用DAYOFMONTH()函数比较NOW()和created_at的日期。是这样的:

SELECT * FROM ORDERS
WHERE (DAYOFMONTH(NOW() < LAST_DAY(NOW()) -- if not last day of month 
        AND DAYOFMONTH(created_at) = DAYOFMONTH(NOW())
OR (LAST_DAY(NOW()) = DAYOFMONTH(NOW()) -- if last day of month
     AND DAYOFMONTH(NOW()) BETWEEN DAYOFMONTH(created_at) AND LAST_DAY(created_at)) --

#3

Inspired by suresubs's answer, I figured out how the query should look like.

受到suresubs回答的启发，我想出了这个查询应该是什么样子。

SELECT * FROM orders
-- (1)
WHERE (DAYOFMONTH(NOW) < DAYOFMONTH(LAST_DAY(NOW()))
  AND DAYOFMONTH(created_at) = DAYOFMONTH(NOW()))
-- (2)
OR (DAYOFMONTH(LAST_DAY(NOW())) = DAYOFMONTH(NOW())
  AND DAYOFMONTH(created_at) BETWEEN DAYOFMONTH(NOW()) AND DAYOFMONTH(LAST_DAY(created_at)));

It's using DAYOFMONTH() and LAST_DAY() functions and it's divided into two cases:

它使用DAYOFMONTH()和LAST_DAY()函数，分为两种情况:

Today is not the last day of current month.
今天不是这个月的最后一天。
Today is the last day of current month.
今天是这个月的最后一天。

#1