CODEWITHSUNDEEP
mysql
lbm gplus
lbm gplus

Reputation: 55

Mysql get user list of every week interval

I want to write query to fetch user from table who register before week interval. For ex. todays date is 2017-08-17, then I need user who register on 2017-08-10, 2017-08-03,2017-07-27 and so on. Like this if todays date is 2017-08-20 then user will be register on 2017-08-13, 2017-08-06.

id name date
1 ABC 2018-08-16
2 PQR 2018-08-10
3 LMN 2018-07-27
4 AAA 2018-01-01

Output will be

id name date
2 PQR 2018-08-10
3 LMN 2018-07-27

Upvotes: 0

Views: 424

Answers (2)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521083

One way to express this problem is to recognize that we want to retain dates whose difference from today are multiple of 7 days. We can compare the UNIX timestamps of each record and check to see if the number of seconds, when divided by the number of seconds in 7 days, is zero.

SELECT *
FROM yourTable
WHERE
    MOD(UNIX_TIMESTAMP(CURDATE()) -
        UNIX_TIMESTAMP(DATE(reg_date)), 7*24*60*60) = 0

Demo here:

Rextester

Upvotes: 1

verhie
verhie

Reputation: 1318

SELECT * FROM user WHERE WEEKDAY(`date`) = WEEKDAY(NOW());

This will get you all users that registered 0, 7, 14, 21 etc. days ago.

Upvotes: 0

Related Questions