How to Get Record Count Between Two Date-Times in MySQL?

Getting Record Count Between Two Date-Times in MySQL

In a MySQL database, retrieving the count of records whose creation dates fall within a specified date-time range can be achieved using various methods. One common approach utilizes the comparison statements in the WHERE clause. Suppose you have a table with a 'created' column of datetime datatype, and you wish to count records created between "TODAY'S 4:30 AM" and the "CURRENT DATE TIME."

To accomplish this, you could use the following query:

SELECT count(*) FROM `table` 
WHERE 
    created_at > '2023-03-08 04:30:00' AND created_at <= '2023-03-08 07:42:50';

In this query, '2023-03-08 04:30:00' represents the lower bound, while '2023-03-08 07:42:50' represents the upper bound of the date-time range. You can adjust these values as per your requirement.

Alternatively, you could use the BETWEEN operator in your WHERE clause, as shown below:

SELECT count(*) FROM `table` 
WHERE 
    created_at BETWEEN '2023-03-08 04:30:00' AND '2023-03-08 07:42:50';

In this case, the BETWEEN operator ensures that only records whose creation dates fall within the specified range are included in the count.

To dynamically obtain the current date and time for the lower and upper bounds of your range, you can utilize MySQL's CURDATE() and NOW() functions. Here's an example:

SELECT count(*) FROM `table` 
WHERE 
    created_at > CURDATE() AND created_at < NOW();

This query will count records created between today's date and the current time.

The above is the detailed content of How to Get Record Count Between Two Date-Times in MySQL?. For more information, please follow other related articles on the PHP Chinese website!