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 4^th 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;

Best Answer

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;

Related Solutions

Postgresql – Count data using multiple date ranges

select  industry_id
,       sum(case when current_date <= date then clicks end) as today 
,       sum(case when current_date-1 <= date and
                      date < current_date then clicks end) as yesterday
,       sum(case when current_date-4 <= date and 
                      date < current_date-1 then clicks end) as last3days
from    phone_clicks
group by
        industry_id

See it in your SQLFiddle.

Mysql – Sorting by month by chronological occurrence when the year doesn’t matter

Try adding a specific field to order by. That field is the value of the month if it is greater than the value of current month, and add 12 if it is less (or equal), then order by that field:

mysql> select now();
+---------------------+
| now()               |
+---------------------+
| 2016-05-30 15:02:45 |
+---------------------+
1 row in set (0.00 sec)

mysql> select * from dates;
+------------+
| d          |
+------------+
| 2012-01-01 |
| 2008-01-01 |
| 2008-02-01 |
| 1987-01-02 |
| 1994-12-28 |
| 1987-05-02 |
| 1994-11-28 |
+------------+
7 rows in set (0.00 sec)

mysql> select d, month(d), if(month(d)>month(now()), month(d), month(d)+12) ord from dates order by ord;
+------------+----------+------+
| d          | month(d) | ord  |
+------------+----------+------+
| 1994-11-28 |       11 |   11 |
| 1994-12-28 |       12 |   12 |
| 2012-01-01 |        1 |   13 |
| 2008-01-01 |        1 |   13 |
| 1987-01-02 |        1 |   13 |
| 2008-02-01 |        2 |   14 |
| 1987-05-02 |        5 |   17 |
+------------+----------+------+
7 rows in set (0.00 sec)

mysql> select d from (select d, month(d), if(month(d)>month(now()), month(d), month(d)+12) ord from dates order by ord)x;
+------------+
| d          |
+------------+
| 1994-11-28 |
| 1994-12-28 |
| 2012-01-01 |
| 2008-01-01 |
| 1987-01-02 |
| 2008-02-01 |
| 1987-05-02 |
+------------+
7 rows in set (0.00 sec)

Best Answer

Related Solutions

Postgresql – Count data using multiple date ranges

Mysql – Sorting by month by chronological occurrence when the year doesn’t matter

Related Question