Get the most recent month’s records

Asked

Viewed 7,523 times

1

I have a simple table that stores titles and publication date, this in DATE format, and I need to get all records for the most recent month/year.

select titulo, publicacao FROM teste1 ORDER BY publicacao DESC
mysql

  • I think your answer is here with the use of DATE_SUB

2 answers

6


Considering the most recent month/year within the table, it is possible to use a variable:

SET @ultimaData := (SELECT publicacao FROM teste1 ORDER BY publicacao DESC LIMIT 1);

SELECT titulo, publicacao FROM teste1 
WHERE YEAR(publicacao) = YEAR(@ultimaData) 
AND MONTH(publicacao) =  MONTH(@ultimaData);

If you want the most recent month/year considering today, you can replace the variable by now()

SELECT titulo, publicacao FROM teste1 
WHERE YEAR(publicacao) = YEAR(now()) 
AND MONTH(publicacao) =  MONTH(now());

Example in Sqlfiddle

  • The first one worked. But the second one should also work?

  • @marcelo2605 posted an example, it is working yes, but it seeks month/year in relation to today.

  • So this second query will only work if there are records in the current month?

  • Exactly, only current month. As it was not very clear your doubt includes both forms.

  • Thank you. The first query solves my problem.

  • There are probably other ways to do it. If you think of any other answers.

Show 1 more comment

3

If you want to find all records where the field in question has the same month and year as the current date, this is equivalent to looking for values greater than or equal to the first day of the current month.

We can do it like this:

SELECT
  titulo
, publicacao
FROM teste1
WHERE publicacao >= DATE_SUB( DATE( NOW() ), INTERVAL DAY( NOW() ) -1 DAY )
ORDER BY
  publicacao DESC

Despite the apparent complexity, if the given field is indexed, this filter will be more efficient than one done searching month and year separately, after all this will benefit from the index.

Now explaining by parts.

The expression

DATE_SUB( NOW(), INTERVAL x DAY )

subtracts x current day days.

To achieve the first day of the month we must subtract from the current date the number of the current day minus 1. For example: 08/28/2014 - 27 = 08/01/2014

In this way

DATE_SUB( NOW(), INTERVAL DAY( NOW() ) -1 DAY )

return the first day of the month.

But this still does not serve for the filter we want, in case the field in question is a DATETIME, because the current time may be greater than the time of a record of the first day changing the result we want from the selection.

To avoid this problem we converted NOW() to DATE and arrived at the expression I presented.

DATE_SUB( DATE( NOW() ), INTERVAL DAY( NOW() ) -1 DAY )

  • Well observed the point of the indexes.

Browser other questions tagged mysql

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