How to filter only finished months on SQL query-CodePudding

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 either on MySQL or PostgreSQL, not 100% sure

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

CodePudding user response：

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

CodePudding user response：

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)