Return all data from the first to the last day of the month with mysql

Navigation
3

I'm doing a search on a table in my database that needs to return all the data that was created in the current month (I'll run a cron on the last day of the month at 11:00 PM). I have in my table a created_at field that stores the date entered in the base record.

What I did was the following: 

SELECT *
FROM minha_tabela
WHERE created_at >= date_add(last_day(date_sub(curdate(), interval 1 month)), interval 1 day) 
AND created_at <= last_day(curdate());

It's working, but I'd like to know if I should leave anyway or if there is any better way to do it, thinking about performance and maintenance.

    
asked by anonymous 22.08.2014 / 18:10

2 answers

4

You can only select the month and year, rather than selecting for a range of days. 

SELECT * FROM minha_tabela
WHERE MONTH(created_at)=MONTH(CURDATE()) AND YEAR(created_at)=YEAR(CURDATE())

This way, you select all records in the current month.

    
22.08.2014 / 18:46
0

Doing the way you explained, you have a one-hour window, and logs created in this period will never go through this process ...

I suggest that you create cronjob in the first minute of every first day of the month, and the query selects all records from the previous month: 

SELECT *
FROM minha_tabela
WHERE YEAR(created_at) = YEAR(CURRENT_DATE - INTERVAL 1 MONTH)
AND MONTH(created_at) = MONTH(CURRENT_DATE - INTERVAL 1 MONTH)

See working on SQL Fiddle

    
23.08.2014 / 13:44
How to index an iframe in Google? What is Reduced Motion Media Query?