sql - 每14天提醒未登录的用户

Question

我必须向至少 14 天未登录的用户发送电子邮件，并每 14 天提醒他们一次。因此，用户将在上次登录后的 14、28、42、56、70... 天后收到一封提醒邮件。查询每天只会执行一次，但我也可以更改间隔。

目前我使用这样的查询来获取过去 14 天内未登录的用户，但我需要将其扩展为每 14 天执行一次。

SELECT mail FROM users
WHERE   lastLogin >= truncate_to_date(now() - 14 days) 
    AND lastLogin <  truncate_to_date(now() - 14 days + 1 day);

• 邮件是 varchar(255)
• lastLogin 是时间戳
• truncate_to_date(timestamp) -> timestamp ( 从时间戳中删除时间部分的函数(例如:2016-11-03 14:15:16 -> 2016-11-03 00:00:00))

我在 sql 中想到了这样的函数，但是我不确定如何在 sql 中正确表达它。

SELECT mail FROM users 
WHERE getDaysSinceEpoch(lastLogin) % 14 = getDaysSinceEpoch(now()) % 14 -- every 14 days
    AND getDaysSinceEpoch(lastLogin) < getDaysSinceEpoch(now()); -- except today

有人可以告诉我如何编写 getDaysSinceEpoch(timestamp) -> int 函数吗？如果我可以内联它而不是单独的函数，那就太好了。

我考虑过的备选方案:

• 为此添加一个新字段:嗯，对于很少使用的情况来说，这是相当大的开销，而且无论如何总能计算出该值。

Answer 1

使用模的想法很好，但查询应该简单得多:

select mail 
from users 
where lastlogin < current_date
and (current_date - lastlogin::date) % 14 = 0;