Group by year of Unix timestamp in MySQL
Question:
How to group by year of Unix timestamp in MySQL? Answer:
SELECT FROM_UNIXTIME(event_time, '%Y') AS year, COUNT(id) AS total
FROM events
GROUP BY FROM_UNIXTIME(event_time, '%Y') DESC;Description:
If we want to group records by date in a MySQL table where the value is stored as a Unix timestamp, we can use FROM_UNIXTIME() function in the GROUP BY clause.
If we want to group by date only (without time), we can use the corresponding date formatting string as the second parameter.
Reference:
FROM_UNIXTIME() reference
Share "How to group by year of Unix timestamp in MySQL?"
Related snippets:
- Convert Unix timestamp to human readable date in MySQL
- Group by year of Unix timestamp in MySQL
- Count total rows by year in MySQL
- Convert INT to DATETIME in MySQL
- Select year from a Unix timestamp in MySQL
- Get current date in MySQL
- Get current year in MySQL
- Extract year from a date in MySQL
- Get last 30 days records in MySQL
Tags:
group, unix timestamp, year, month, date, mysql, group by year on timestamp Technical term:
Group by year of Unix timestamp in MySQL