Introduction

Retrieving data based on time intervals, such as monthly or yearly, is a common requirement in web applications. This article explains how to query a MySQL table to fetch records for a specific month or year using a timestamp column.

✅ Database Structure

sales Table

✅ Querying Monthly Data

To fetch data for a specific month (e.g., January), you can use the MONTH() function.

SELECT * FROM sales
WHERE MONTH(created_at) = 1;

Explanation

1. MONTH(): Extracts the month from the created_at timestamp.

2. The condition = 1 targets January.

✅ Querying Yearly Data

To fetch data for a specific year (e.g., 2024), use the YEAR() function.

SELECT * FROM sales
WHERE YEAR(created_at) = 2024;

Explanation

1. YEAR(): Extracts the year from the created_at timestamp.

2. The condition = 2024 filters records from that year.

✅ Combining Both (Month and Year)

If you want to filter data for January 2024 specifically:

SELECT * FROM sales
WHERE MONTH(created_at) = 1 AND YEAR(created_at) = 2024;

✅ Grouping by Year and Month to Get Count

To get the count of records per month and year:

SELECT YEAR(created_at) AS year, MONTH(created_at) AS month, COUNT(*) AS count
FROM sales
GROUP BY YEAR(created_at), MONTH(created_at)
ORDER BY year, month;

Output

Tips

• Ensure the created_at column is indexed for better performance.

• Use DATE_FORMAT() for more complex date manipulations.

Common Pitfalls

• Using the wrong date format can lead to incorrect results.

• Not accounting for time zones if your application serves multiple regions.

✅ Conclusion

With MySQL functions like MONTH() and YEAR(), you can efficiently filter data based on specific time intervals. This approach is especially useful for generating reports and analyzing trends.