Get the start and end dates of the previous month the query is run with PostgreSQL/intervals

Question

I am looking for a way to get the start and end datetime for the previous month the query is run that I can reuse inside a query.

I was looking at interval but I would like the query to work out that if I run the query in March 2021 that I am looking for data from '2021-02-01 00:00:00' to '2021-02-28 23:59:59'.

Is this possible using interval or something similar or does it need an intermediate step?

Answer 1

Assuming you want to apply a condition on a column in the table you can use:

where timestamp_column >= date_trunc('month', current_timestamp) - interval '1 month'
  and timestamp_column <  date_trunc('month', current_timestamp)

date_trunc('month', current_timestamp) - interval '1 month' gives you the start of the previous month. In March this would be 2021-02-01

date_trunc('month', current_timestamp) gives you the start of "this month" so in March this would be 2021-03-1 as the comparison for the upper limit is done using < it will include everything on the last day of February (including 23:59:59.9999999 which your desired condition would not include).