Selecting items created within the current month in MySQL

Created:22 Jan 2022 19:08:58 , in  Web development

Here is a quick query, which helps to find all the items added to a MySQL database table in the current month. Suppose today is January 18, 2022, and you want to get all the blog posts stored in Posts table, which were created within the last 18 days.

Assuming that the table has a column called created_at, which stores date and time of when each post was added, here is one way how to achieve this:

SELECT * Posts WHERE `created_at` > date_sub(curdate(), INTERVAL day(curdate()) DAY);

Give the query a go before you move on to a bonus query described below ;).

If you need to get items created within the last month, it is likely you will also need to find items, which were not created this month, that is, all the items added before the current month started. Here is the bonus MySQL query which does just that. For simplicity it uses the same Posts table and relies on it having created_at column.

SELECT * FROM Posts WHERE `created_at` <= date_sub(curdate(), INTERVAL day(curdate()) DAY);

Obviously, nothing should stop you from replacing DAY with MONTH or YEAR in either of the queries. Have a go and see what you will get then. Have fun!

This post was updated on 22 Jan 2022 19:20:48

Tags:  mysql