# Postgresql – How to create month ranges without using a specific date in Postgresql

dategroup byperformancepostgresql

What's the best way to create month ranges without using specific dates?

For example, today is April the 4th and I want to calculate the sales rate of the past 12 months, that I can run every month without updating the date.

I was thinking of working with current_date, but is there a way to create a more efficient query?

select
datetrunc('month',date)
,count(id) filter (where status='sold' and (date between (datetrunc('month','current_date - interval '1 months'))
and datetrunc('month', current_date) - Interval '1 days')) / count(id) filter
(where date between (datetrunc('month','current_date - interval '1 months')) and
datetrunc('month', current_date) - Interval '1 days') as Mar2020_Sales_Rate
,..... as Feb2020_Sales_Rate
,.
,.
,..... as Mar2019_Sales_Rate
from sales
group by 1;


This is the way I chose to get 1st and end of the month, without using current date, but rather the creation date (of an order) in my data. Please suggest any other query that might seem more effective or if you feel there is a flaw in this query:

select distinct creation_date::date,
(creation_date - Interval '1 months')::date as Minus_1M,
(creation_date - Interval '2 months')::date as Minus_2M,
(creation_date - Interval '3 months')::date as Minus_3M,
extract('day' from creation_date) as Day_of_month,
date_trunc('month', (creation_date::date - extract('day' from
creation_date)::int))::date as Beginning_of_month,
creation_date::date - extract('day' from creation_date)::int as End_of_month
from order_data
order by 1 desc;