从时间戳中获得正确的一周和一年

Question

i have a function which gets all the newly registered users, and displays it depending on the filter given whether it is newly registered by month or by year.

on this example i am using year, i have this sql

Select date_registered from users where date_registered > '2011-12-31 12:59:59' AND date_registered < '2013-01-01 12:00:00'

so this will result in all date_registered registered for the year 2012

the question is how to be more dynamic that i may apply a filter for example where date_registered > $dynamic_date and date_registered < $dynamic_date

or any good advise how to achieve this better.

thanks . .

Answer 1

If it's a small data set, I would go the pure SQL route that Tikkes' answer shows; however, for index purposes, calculating the bounds may be better.

mktime and date can be used for this:

$lowerBound = date('Y-m-d H:i:s', mktime(0, 0, -1, 1, 1, date('Y')); //1 second before 00:00:00 1 Dec <current year>
$upperBound = date('Y-m-d H:i:s', mktime(0, 0, -1, 1, 1, date('Y') + 1); //1 second before 00:00:00 1 Dec <current year + 1>

So then you just put them in a query:

$sql = " ... WHERE d > '$lowerBound' AND d < '$upperBound'";