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

Asked

Viewed 5,179 times

3

I am searching a table in my database that needs to return all the data that was created in the current month (I will run a cron on the last day of the month at 23:00). I have in my table a field created_at keeping the date entered on the basis of the record.

Here’s what I did:

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, only I’d like to know if it’s better if I leave anyway or if there’s some better way to do it, thinking about performance and maintenance.

mysql

2 answers

4


You can only select the month and year instead of selecting for an interval 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.

  • A way that at least already facilitates maintenance, and from what I saw neither improves nor worsens the performance. Valew

  • I cannot say in mysql, but in sql server there is a loss of performance if the field uses indexes, as they are disregarded when the where is calculated by means of an expression (month() and year() in that case)

0

Doing as you explained, you are with a one-hour window, and the records created in that period will never go through this process...

I suggest you create the 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

Browser other questions tagged mysql

You are not signed in. Login or sign up in order to post.