Search code examples
postgresqldatedatepart

postgresql - calculating the day of year for the last day of the previous month

I have a query where I'm calculating revenue pace for the year based on revenue so far in the current year. For this calculation, I need to find the doy (day of year) for the last day of the previous month. I know to find the doy for the current date is date_part('doy', current_date). For example, this would be Day 184 for today (July 3, 2023). But I need to calculate for Day 181 (June 30, 2023).

Solution

  • Quick example:

    
select
    date_part('doy',
              date_trunc('month',
                    (current_date - '1 month'::interval)) + ('1 month'::interval - '1 day'::interval));

181

    current_date - '1 month'::interval subtracts one month from the current_date. Then date_trunc truncates that date to beginning of the previous month. 1 month is added to previous month beginning date to get to the first of the current month from which 1 day is subtracted to get the last day of the previous month. at that point date_part('doy' ...) is used to get the doy for that date.

    If you want to find the month end doy for some range of dates:

    select 
    a + ('1 month'::interval - '1 day'::interval) AS month_end, date_part('doy', a + ('1 month'::interval - '1 day'::interval)) AS doy 
from  
    generate_series('01/01/2023'::date, '12/01/2023'::date, '1 month') as t(a);
        month_end        | doy 
-------------------------+-----
 01/31/2023 00:00:00 PST |  31
 02/28/2023 00:00:00 PST |  59
 03/31/2023 00:00:00 PDT |  90
 04/30/2023 00:00:00 PDT | 120
 05/31/2023 00:00:00 PDT | 151
 06/30/2023 00:00:00 PDT | 181
 07/31/2023 00:00:00 PDT | 212
 08/31/2023 00:00:00 PDT | 243
 09/30/2023 00:00:00 PDT | 273
 10/31/2023 00:00:00 PDT | 304
 11/30/2023 00:00:00 PST | 334
 12/31/2023 00:00:00 PST | 365

    This uses generate_series to start a series of month start dates over the desired range. Then it does the date math date_part('doy', a + ('1 month'::interval - '1 day'::interval)) AS doy to turn the month start dates into month end dates and then convert that date into doy.