SQL Query Counters Daily, Monthly, Yearly and Total Web Visits

Let us understand how to build a query to find the number of network accesses per day, month, year and total in MySQL:

Note:We assume that we have created a database named "DBNAME" and a table named "tableName".

Let us see the MySQL query that can be used to get the daily web visits, month, year and total -

Query

SELECT COUNT(DISTINCT ip)
FROM tableName
WHERE create_at >= LAST_DAY(NOW()) + INTERVAL 1 DAY - INTERVAL 1 MONTH
AND create_at < LAST_DAY(NOW()) + INTERVAL 1 DAY

The above query starts from the current month and Search upto and Until to search for a range of DATETIME values ​​excluding the beginning of the next month.

Next, the composite covering index (create_at, ip) has been created. The above query will give the number of network visits per day, month, and year.

MySQL can scan the index range it needs.

Note: The above query also works for TIMESTAMP data.

The above is the detailed content of SQL Query Counters Daily, Monthly, Yearly and Total Web Visits. For more information, please follow other related articles on the PHP Chinese website!