mysql - 从 unix 时间戳中选择最近的每月周年纪念日

Question

我有一个在注册时插入 Unix 时间戳的应用程序。我想做的是计算自上个月周年纪念日以来的月份的使用详细信息。所以我需要一个 unix 时间戳来表示最近的周年纪念日。

例如，如果在 1 月 5 日提交注册，则客户的周年纪念日为 5 日。因此，要检查 2 月 15 日的使用情况，我需要从 2 月 5 日以来的日志中检索所有条目。

获取注册日期很容易:

SELECT DATE_FORMAT(FROM_UNIXTIME(created), '%d') FROM  accounts

但是，我找不到基于注册日的最后一个周年纪念日的 unix 时间戳。我该怎么做？澄清一下，我希望返回在最近的周年纪念日或之后创建的所有 action_id。

表格:

accounts
+------------+------------+
| account_id | created    |
+------------+------------+
| 1          | 1321838910 |
+------------+------------+
....

logs
+------------+------------+------------+
| account_id | action_id  | created    |
+------------+------------+------------+
| 1          | 233        | 1348249244 |
+------------+------------+------------+
| 1          | 263        | 1348257653 |
+------------+------------+------------+
....

注意:为简单起见，我将放弃弄清楚如果周年纪念日是 31 日会发生什么 - 也就是说，除非有人有一个 super 忍者声明将这些事件考虑在内。

Answer 1

未经测试。看看你的想法。逻辑是:

1. 获取当月的最后一天。
2. 将帐户创建天数添加到 #1 结果中。
3. 如果当前日期大于创建日期，则从#2 结果中减去 1 个月。否则减去 2 个月。

---

SELECT l.*
FROM accounts a
LEFT JOIN logs l
ON a.account_id = l.account_id 
 AND l.created >= UNIX_TIMESTAMP(
  DATE_SUB(DATE_ADD(LAST_DAY(NOW()), INTERVAL DAY(FROM_UNIXTIME(a.created)) DAY),  
    INTERVAL IF(DAY(NOW()) > DAY(FROM_UNIXTIME(a.created)), 1, 2) MONTH));

---

编辑

我对此进行了更多思考，也许无论周年纪念日是什么时候，下面的查询都可以工作。我假设如果周年纪念日不在特定月份，则应采用该月的最后一天。它很难看，但我加入了一些变量以使其更简洁，一定有更好的方法。无论如何，我没有测试，但逻辑如下。

1. 如果当天 > 周年纪念日，则只需减去天数差即可得到日期。
2. 否则，如果上个月的最后一天小于周年纪念日，则使用上个月的最后一天。
3. 否则从上个月的最后一天减去周年纪念日和上个月最后一天之间的天差。

---

SELECT l.*
FROM accounts a
JOIN logs l
ON a.account_id = l.account_id 
 AND l.created >= UNIX_TIMESTAMP(
   IF(@dNow := DAY(NOW()) >= @dCreated := DAY(FROM_UNIXTIME(a.created)),
     DATE_SUB(NOW(), INTERVAL @dNow - @dCreated DAY),
      IF(DAY(@endLastMonth := LAST_DAY(DATE_SUB(NOW(), INTERVAL 1 MONTH))) <= @dCreated, 
        @endLastMonth,
        DATE_SUB(@endLastMonth, INTERVAL DAY(@endLastMonth) - @dCreated DAY))));