从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号会发生什么--也就是说，除非有人有一个超级忍者声明来考虑这些发生的情况。

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))));

Answer 2

也许这就是使用order by desc来获取最近的创建日期？

SELECT DATE_FORMAT(FROM_UNIXTIME(X.created), '%d') 
FROM  (
SELECT CREATED
FROM mytable
WHERE ACCOUNT_ID = ? -- customer id
AND DATE_DIFF(DATE_FORMAT(FROM_UNIXTIME(CREATED),'%Y-%m-%d'), NOW()) MOD 30 = 0 
AND DATE_DIFF(DATE_FORMAT(FROM_UNIXTIME(CREATED),'%Y-%m-%d'), NOW()) / 30 = 1
ORDER BY CREATED DESC LIMIT 1)X;