How can I get 7 days before last 7 days using UNIX_TIMESTAMP?

Question

I use the code below to return results from a mySQL field called registered_at so I can get the users that registered on the site the last 7 days and it works fine:

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

My problem is that I need also to get the users that registered on the site 7 days BEFORE the last 7 days. The logic is to display the registered users of the last 2 weeks so next I can output the difference between those 2 weeks in percentage, eg: +10% more users this week.

I tried something like:

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

but didn't work.

Answer 1

You can use:

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

This will produce query which gets registered_at for the last 14 days but will also trim registered_at for the last 7 days, so you stay with 7-14 days interval.

If we transform it to the "raw" SQL query it will be:

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