MySQL Query GROUP BY day / month / year

datedatetimegroup-byMySQLsql

Is it possible to make a simple query to count how many records I have in a determined period of time like a year, month, or day, having a TIMESTAMP field, like:

SELECT COUNT(id)
FROM stats
WHERE record_date.YEAR = 2009
GROUP BY record_date.YEAR

Or even:

SELECT COUNT(id)
FROM stats
GROUP BY record_date.YEAR, record_date.MONTH

To have a monthly statistic.

Thanks!

Best Answer

GROUP BY YEAR(record_date), MONTH(record_date)

Check out the date and time functions in MySQL.

Related Solutions

Mysql – Should I use the datetime or timestamp data type in MySQL

Timestamps in MySQL are generally used to track changes to records, and are often updated every time the record is changed. If you want to store a specific value you should use a datetime field.

If you meant that you want to decide between using a UNIX timestamp or a native MySQL datetime field, go with the native format. You can do calculations within MySQL that way ("SELECT DATE_ADD(my_datetime, INTERVAL 1 DAY)") and it is simple to change the format of the value to a UNIX timestamp ("SELECT UNIX_TIMESTAMP(my_datetime)") when you query the record if you want to operate on it with PHP.

Python – How to subtract a day from a date

You can use a timedelta object:

from datetime import datetime, timedelta
    
d = datetime.today() - timedelta(days=days_to_subtract)

Best Answer

Related Solutions

Mysql – Should I use the datetime or timestamp data type in MySQL

Python – How to subtract a day from a date

Related Topic