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

问题描述：

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 . .

我有一个函数可以获取所有新注册的用户，并根据过滤器显示它是否是新的按月或按年注册。 p>

在这个例子中，我使用的是年份，我有这个sql p>

从date_registered的用户中选择date_registered ＆GT; '2011-12-31 12：59：59'AND_registered＆lt; '2013-01-01 12:00:00' code> p>

因此，这将导致2012年注册的所有 date_registered code> p>

问题是如何更加动态，我可以应用过滤器，例如 where date_registered＆gt; $ dynamic_date和date_registered＆lt; $ dynamic_date code> p>

或任何好的建议如何更好地实现这一目标。 p>


谢谢。  。   p> 
  div>

答

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'";

答

try this code

$sql = "SELECT date_registered FROM users WHERE date_registered > '$dateStart' AND date_registered < '$dateEnd'";

where $dateStart and $dateEnd are your dynamic date variables

A pair of SINGLE quotes around your variables should be enough. All the statement string has to be in DOUBLE quotes as you see.

答

I believe this is what you are looking for:

$sql = "SELECT date_registered 
       FROM users
       WHERE YEAR(date_registered) = YEAR(NOW())";

check this out for more information

Since you also want the right week, also look at the function YEARWEEK(). It works in the same way and returns the number of the week as well. See here

答

You should use BETWEEN, it's more readable.

MySQL has a great list of date and time functions

Basically, what you want to do is this:

SELECT *
FROM Store_Information
WHERE Date BETWEEN '$start' AND '$end'

I don't know what data you're starting from, or where you get your $start and $end inputs. I'm going to assume you're using unix timestamps, generated from time(), and then you can do something like this:

SELECT *
FROM Store_Information
WHERE Date BETWEEN FROM_UNIXTIME('$start') AND FROM_UNIXTIME('$end')

If this does not suffice, please be clearer how you format your indata ($start, $end).

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

相关推荐