Reputation: 141
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
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
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