I need to write this kind of code

BETWEEN '2022-02-01' AND '2022-02-28' 

but to be relative and selecting last month

I tried it this way

BETWEEN TO_CHAR(current_date - interval '1' month, 'yyyy-mm-01')::date
AND TO_CHAR(TO_CHAR(current_date, 'yyyy-mm-01')::date - interval '1' day, 'yyyy-mm-dd')::date

but I think there might be better solution to this.

CodePudding user response：

Don't convert date values to strings for comparisons. Always use "real" date values.

To get the start of the last month, you can use a combination of date_trunc() and subtracting an interval

The start of the last month is the start of "this month" minus one month:

date_trunc('month', current_date) - interval '1 month'

And the end of the last month is the start of this month minus one day:

date_trunc('month', current_date) - interval '1 day'

You didn't tell us which data type your column has that you want to compare, but typically it's better to use >= and < rather than between - especially if that column is a timestamp.

where the_column >= date_trunc('month', current_date) - interval '1 month'
  and the_column  < date_trunc('month', current_date)