Question 112686 in Mysql Question posted in
The official MySQL documentation can be found here.

Mysql – How to filter only finished months on SQL query

I would like to filter my query with only closed months, so today (17/11/2022) would return only 31/10/2022 and before. I have tried the following:

WHERE EXTRACT(MONTH FROM dc.data) <= (EXTRACT(MONTH FROM CURDATE()) - 1)

So I get the current date, extract the month and subtract 1, then filter <= that month right? Also, dc.data is on the right date format, and it’s working properly

But here is the problem, it’s filtering past Years with months 1-10 too, that filter should be applied only to current year, and still show past years with all months, how could I do this?

This query is being written on Apache Superset SQL editor, so I have some limitations on functions I think… the database is MySQL (edited)

PS: I was also wondering if there is a way to optimize this query, not sure if this is a good way of handling dates

Tags:

3

Answers


  1. Chosen as BEST ANSWER
    0

    MySQL equivalent to TRUNC/DATE_TRUNC is

    DATE_FORMAT(CURDATE(), '%Y-%m-01')

    So my final result was

    WHERE dc.data < DATE_FORMAT(CURDATE(), '%Y-%m-01')

  2. 0

    You need also to comapre the years, if you want the actual year

    WHERE (EXTRACT(YEAR FROM dc.data) = EXTRACT(YEAR FROM CURDATE()) 
      AMD EXTRACT(MONTH FROM dc.data) <= (EXTRACT(MONTH FROM CURDATE()) - 1)) OR EXTRACT(YEAR FROM dc.data) < EXTRACT(YEAR FROM CURDATE())

    or use the last day of the previous moth to get the l

    WHERE dc.data <= (date_trunc('MONTH', now()) + INTERVAL '1 MONTH - 1 day')::date;

    you should have an INDEX on data to get the data faster

    Login or Signup to reply.
  3. 0

    Postgres provides a convent function date_trunc (see documentation). One the options is truncating to the month, which will return the first day of the parameter. So

    where dc.data < date_trunc('month', current_date)
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search