CODEWITHSUNDEEP
mysqltimestamp
Bilal Alizai Sadik
Bilal Alizai Sadik

Reputation: 13

Querying MySQL Table for Records where Timestamp is X Weeks After Another

I have a MySQL table with two different Timestamp columns: created and lastSent. I'm looking to query the table and only retrieve records whose lastSent timestamp is a certain whole number of weeks ahead of created.

╔════╦═════════╦══════════╗
║ ID ║ created ║ lastSent ║
╠════╬═════════╬══════════╣
║ 1  ║ Aug 18  ║ Aug 25   ║
╠════╬═════════╬══════════╣
║ 2  ║ Aug 11  ║ Aug 25   ║
╠════╬═════════╬══════════╣
║ 3  ║ Aug 19  ║ Aug 25   ║
╠════╬═════════╬══════════╣
║ 4  ║ Aug 20  ║ Aug 25   ║
╠════╬═════════╬══════════╣
║ 5  ║ Aug 3   ║ Aug 24   ║
╚════╩═════════╩══════════╝

For example, the query I'm looking for would give me records 1, 2, and 5, but not 3 and 4.

Thank you so much! I hope I explained this well enough.

Upvotes: 0

Views: 36

Answers (4)

Paul Spiegel
Paul Spiegel

Reputation: 31812

You can also write your question as:

Find the rows where created and lastSent have the same weekday.

Then the answer would be obvious:

SELECT *
FROM mytable
WHERE WEEKDAY(created) = WEEKDAY(lastSent);

Upvotes: 0

Barmar
Barmar

Reputation: 781726

Use the modulus operator to see if the number of days in the difference is a multiple of 7.

SELECT *
FROM yourTable
WHERE DATEDIFF(created, lastsent) % 7 = 0

Upvotes: 1

ScaisEdge
ScaisEdge

Reputation: 133380

seems you are looking for the rows with a diff with > 1 week 

select * from my_table  
where ROUND(DATEDIFF(lastSent, created)/7, 0) > 1

Upvotes: 0

Cameron
Cameron

Reputation: 694

You should be able to make use of the datediff function to get the number of weeks between the timestamps.

Upvotes: 0

Related Questions