How to use UNIX_TIMESTAMP to get the 7 days before the last 7 days?

Question

I'm using the code below to return results from a mySQL field called registered_at so that I can get users who have registered on the site in the past 7 days and it works fine:

andWhere('registered_at > UNIX_TIMESTAMP(NOW() - INTERVAL 7 DAY)')

My problem is that I also need to get the users who have registered on the website in the last 7 days. The logic is to display the registered users for the past 2 weeks so that next I can output the difference between these 2 weeks as a percentage, for example: 10% more users this week.

I tried something similar:

andWhere('registered_at > UNIX_TIMESTAMP((NOW() - INTERVAL 14 DAY) - (NOW() - INTERVAL 7 DAY))')

But without success.

Answer 1

you can use it:

andWhere('registered_at > UNIX_TIMESTAMP(NOW() - INTERVAL 14 DAY)')
andWhere('registered_at < UNIX_TIMESTAMP(NOW() - INTERVAL 7 DAY)')

This will generate a query that gets the registered_at for the past 14 days, but also prunes the registered_at for the past 7 days, so you'll keep a 7-14 day interval.

If we convert this into a "raw" SQL query, it would be:

SELECT 
  * 
FROM 
  users 
WHERE 
  registered_at > UNIX_TIMESTAMP(NOW() - INTERVAL 14 DAY)' 
  AND registered_at < UNIX_TIMESTAMP(NOW() - INTERVAL 7 DAY)'