CODEWITHSUNDEEP
sqlpostgresql
PinballWizard
PinballWizard

Reputation: 141

How to get list of months from today to january?

i tried to generate list of months from today to january, with this code

select now() - (interval '1' month * generate_series(0,7))

i need to set up the column number so how can i get all the list of month without set up the column number if today is november?

Upvotes: 0

Views: 80

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1271241

I think you want:

select *
from generate_series(date_trunc('year', now()), date_trunc('month', now()),
                     interval '1 month') g(mon);

If you want all the days from todays date going backward:

select *
from generate_series(now(), date_trunc('year', now()),
                     interval '-1 month') g(mon);

However, you have to think about what you would want to do if today were, say, Aug 31 and the month is February.

Upvotes: 1

Scoots
Scoots

Reputation: 3102

with
    __start as(
        select
            date_trunc('month', current_timestamp)::date as start_month -- Starts from this month
            --(date_trunc('month', current_timestamp) + '1 month'::interval)::date as start_month -- Starts from next month
    ),
    __end as(
        select
            start_month,
            (start_month + ((13 - extract(month from start_month))::text || ' months')::interval)::date as end_month -- Includes January in results
            -- (start_month + ((12 - extract(month from start_month))::text || ' months')::interval)::date as end_month -- Does not include January
        from
            __start
    )
select
    generate_series(start_month, end_month, '1 month'::interval)::date as intervening_month
from
    __end

Will output the current month, every month between now and January and January itself typecast to date

I've included alternatives which are commented out for excluding the current month, and excluding January

Upvotes: 1

Related Questions