ST-DDT ST-DDT - 1 year ago 76
SQL Question

Remind users that have not logged in every 14 days

I have to send an email to users that have not logged in for at least 14 and remind them every 14 days.
So users will get a reminder email 14, 28, 42, 56, 70... days after their last login.
The query will only be executed once a day, but I can change the interval as well.

Currently I use a query like this to get the users that have not logged in in the last 14 days, but I need to extend it to do it every 14 days.

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

  • mail is varchar(255)

  • lastLogin is timestamp

  • truncate_to_date(timestamp) -> timestamp ( Function that erases the time part from the timestamp (ex: 2016-11-03 14:15:16 -> 2016-11-03 00:00:00))

I thought of a function like this in sql however I'm not sure how to express it properly in sql.

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

Can somebody please give me a clue how i can write the getDaysSinceEpoch(timestamp) -> int function?
It would be nice if i could inline this instead of a separate function.

Alternatives I have considered:

  • Adding a new field for this: Well thats quite overhead for a rarely used case and the value can always be calculated anyway.

Answer Source

The idea to use modulo is fine, but the query should be much simpler:

select mail 
from users 
where lastlogin < current_date
and (current_date - lastlogin::date) % 14 = 0;
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download