CODEWITHSUNDEEP
sqlpostgresql
ST-DDT
ST-DDT

Reputation: 2717

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

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:

Upvotes: 2

Views: 209

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1271191

I would expect a query such as this:

SELECT mail
FROM users
WHERE lastLogin  < current_date AND
      extract(day from (current_date - date_trunc('day', lastLogin) ))::int % 14 = 0;

Upvotes: 1

Thorsten Kettner
Thorsten Kettner

Reputation: 95101

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;

Upvotes: 4

Related Questions