CODEWITHSUNDEEP
phpmysqlsql
Raffaele Izzia
Raffaele Izzia

Reputation: 242

Query records of 7/14/21/28... days ago in Mysql

I have a table of users where I'm storing the registration date as UNIX timestamp. I would select all the users registered 7/14/21/28/... days ago. So if I have a situation like this (I'm not using unix timestamp in the example just to be more clear):

+-------------------------------------------------+
| ID | username   | registration_date             |
+----+------------+-------------------------------+
| 1  | user1      | 11-02-2014                    |
| 2  | user2      | 4-02-2014                     |
| 3  | user3      | 28-01-2014                    |
| 4  | user4      | 27-01-2014                    |
+-------------------------------------------------+

The query should return the user #2, #3 but not the number #4 because he signup 15 days ago and 15 is not multiple of 7.

A spaghetti solution could be to write thousand of WHERE statement to select my records but I hope someone will help me finding a better one.

Upvotes: 1

Views: 317

Answers (2)

Vikram Jain
Vikram Jain

Reputation: 5588

Here,

The DATEDIFF() function returns the time between two dates.

SQL FORMAT() Syntax

SELECT FORMAT(column_name,format) FROM table_name;

Your sql query look like:

SELECT ID, username, registration_date   FROM users
        WHERE DATEDIFF(registration_date, NOW()) % 7 = 0

or

 SELECT ID, username, registration_date   FROM users
                WHERE MOD(DATEDIFF(registration_date, NOW()), 7) = 0

Upvotes: 1

Abhik Chakraborty
Abhik Chakraborty

Reputation: 44844

Try this

SELECT * FROM users
WHERE DATEDIFF(registration_date, NOW()) % 7 = 0

Check here datediff and mod operator

http://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html#function_datediff

https://dev.mysql.com/doc/refman/5.1/en/mathematical-functions.html#function_mod

Upvotes: 3

Related Questions