PHP MySQL组由unix Timestamp提供报告

Question

I have a table tbl_news, and I wanted to create several reports such us today, this week, this month.

I have stored time in unix timestamp at "news_adate". For this week report I am using the following query.

$first = date('Y-m-d',strtotime("monday this week"));
$last = date('Y-m-d',strtotime("sunday this week"));

$first = $first ." 00:00:01";
$last = $last ." 23:59:59";

$query = "SELECT count(nid), news_adate FROM tbl_news WHERE (news_adate between '".$first."' AND '".$last."') AND tbl_news.news_status=1 GROUP BY DATE(FROM_UNIXTIME(tbl_news.news_adate))" ;

I got the following output.

25-Oct 2015     1

27-Oct 2015     45

But actually it should be

25-Oct 2015     1

26-Oct 2015     35

27-Oct 2015     10

Don't know where is the error?

Answer 1

Just a shot in the dark but you're missing the midnight hour by saying between 00:00:01 and 23:59:59 right? What prompted you to use the Unix timestamp rather than the MySQL now() function to store your date time values?I find querying against the MySQL date format substantially easier and more manipulatable with MySQL functions like trunc(). Typically I store both but when querying against dates I prefer the MySQL date time column as opposed to an integer representing a time. So my first suggestion is to check this output:

 $first = date('Y-m-d',strtotime("monday this week"));
$last = date('Y-m-d',strtotime("sunday this week"));

$first = $first ." 00:00:01";
$last = $last ." 23:59:59";

$query = "SELECT count(nid), news_adate FROM tbl_news WHERE (news_adate between '".$first."' AND '".$last."') AND tbl_news.news_status=1 GROUP BY DATE(FROM_UNIXTIME(tbl_news.news_adate))" ;

Echo $query;

Anytime you're constructing a query you should check the value of the string to ensure it truly is what you want it to query.

You can also use a SQL tool like toad to help you construct valid queries and check their output before replacing hard values with variables.