Home Backend Development PHP Tutorial How to implement PHP database statistical timestamp to group and output data by day

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

Oct 10, 2017 am 09:02 AM
php database output

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));
Copy after login

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;
 // 执行查询
}
Copy after login

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;
// 执行查询
...
Copy after login

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!

Statement of this Website
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn

Hot Article Tags

Notepad++7.3.1

Notepad++7.3.1

Easy-to-use and free code editor

SublimeText3 Chinese version

SublimeText3 Chinese version

Chinese version, very easy to use

Zend Studio 13.0.1

Zend Studio 13.0.1

Powerful PHP integrated development environment

Dreamweaver CS6

Dreamweaver CS6

Visual web development tools

SublimeText3 Mac version

SublimeText3 Mac version

God-level code editing software (SublimeText3)

CakePHP Date and Time CakePHP Date and Time Sep 10, 2024 pm 05:27 PM

CakePHP Date and Time

PHP 8.4 Installation and Upgrade guide for Ubuntu and Debian PHP 8.4 Installation and Upgrade guide for Ubuntu and Debian Dec 24, 2024 pm 04:42 PM

PHP 8.4 Installation and Upgrade guide for Ubuntu and Debian

CakePHP File upload CakePHP File upload Sep 10, 2024 pm 05:27 PM

CakePHP File upload

CakePHP Routing CakePHP Routing Sep 10, 2024 pm 05:25 PM

CakePHP Routing

Discuss CakePHP Discuss CakePHP Sep 10, 2024 pm 05:28 PM

Discuss CakePHP

CakePHP Project Configuration CakePHP Project Configuration Sep 10, 2024 pm 05:25 PM

CakePHP Project Configuration

CakePHP Quick Guide CakePHP Quick Guide Sep 10, 2024 pm 05:27 PM

CakePHP Quick Guide

How To Set Up Visual Studio Code (VS Code) for PHP Development How To Set Up Visual Studio Code (VS Code) for PHP Development Dec 20, 2024 am 11:31 AM

How To Set Up Visual Studio Code (VS Code) for PHP Development

See all articles