Selecting items created within the current month in MySQL

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 


Author, Copyright and citation

Author

Sylwester Wojnowski

Author of the this article - Sylwester Wojnowski - is a sWWW web developer. He has been writing computer code for the websites and web applications since 1998.

Copyrights

©Copyright, 2022 Sylwester Wojnowski. This article may not be reproduced or published as a whole or in parts without permission from the author. If you share it, please give author credit and do not remove embedded links.

Computer code, if present in the article, is excluded from the above and licensed under GPLv3.

Citation

Cite this article as:

Wojnowski, Sylwester. "Selecting items created within the current month in MySQL." From sWWW - Code For The Web . https://wojnowski.net.pl//main/index/selecting-items-created-within-the-current-month-in-mysql

Add Comment

Allowed BB Code - style tags: [b][/b], [i][/i], [code=text][/code],[code=javascript][/code],[code=php][/code],[code=bash][/code],[code=css][/code],[code=html][/code]


I constent to processing my data given through this form for purposes of a reply by the administrator of this website.

Recent Comments

Nobody has commented on this post yet. Be first!