0

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.

Designer
  • 875
  • 7
  • 26

1 Answers1

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)'
michal.jakubeczy
  • 8,221
  • 1
  • 59
  • 63
  • Yeah, finally i went this way and then at frontend with php I printed the difference of 2 queries as the week before last week. i just was wondering if we could do that with just one query. Or is it impossible ? – Designer Jan 13 '22 at 10:59
  • 1
    Yes, you can do it with the single query - if you go down to the "raw" SQL query you can write it this way. `SELECT * FROM users WHERE registered_at > UNIX_TIMESTAMP(NOW() - INTERVAL 14 DAY)' AND registered_at < UNIX_TIMESTAMP(NOW() - INTERVAL 7 DAY)'`. I do not know which PHP framework you use, but it should be capable of combining conditions in SQL queries. – michal.jakubeczy Jan 13 '22 at 11:09
  • Thank you mate. I use Yii2 but i am pretty new. Still lot to learn ;) – Designer Jan 13 '22 at 13:08