How to implement PHP database statistical timestamp to group and output data by day

This article mainly introduces the method of PHP to implement database statistical timestamp grouping and outputting data by day, involving PHP time-based operations and database query-related operating skills. Friends in need can refer to the example of this article

Describes how PHP implements database statistical timestamps to output data in groups by day. Share it with everyone for your reference. The details are as follows:

For example, to count the number of user registrations every day, the database table stores a user registration record table:

create table table_name(id int primary key,register_time int(10));

register_time records the timestamp. The previous method was to receive the query start time and query end time, and then loop to query the daily registration number. Code:

/* 查询2015-12-01 至 2015-12-14 */
// 开始的时间戳
$startUnix = 1448899200; // 2015-12-01 00:00:00
// 结束的时间戳
$endUnix = 1450108800; // 2015-12-15 00:00:00
for($i = $startUnix; $i < $endUnix; $i += 86400){ // 86400为1天的秒数
 // 查询
 $sql = 'select count(*) from table_name where register_time>= '.$i.' and register_time < '.$i + 86400;
 // 执行查询
}

This method The disadvantage is that the database is queried and retrieved as many times as there are days between the start and end dates of the query.

Optimization method:

/* 查询2015-12-01 至 2015-12-14 */
// 开始的时间戳
$startUnix = 1448899200; // 2015-12-01 00:00:00
// 结束的时间戳
$endUnix = 1450108800; // 2015-12-15 00:00:00
$sql = 'select count(id) as register_count, FROM_UNIXTIME(register_time, '%Y-%m-%d') as datetime from table_name where register_time>= '.$startUnix.' and register_time < '.$endUnix group by datetime;
// 执行查询
...

The above is the detailed content of How to implement PHP database statistical timestamp to group and output data by day. For more information, please follow other related articles on the PHP Chinese website!