CODEWITHSUNDEEP
sqlpostgresql
Neil Middleton
Neil Middleton

Reputation: 22240

Generating a series from a predefined date (PG)

I'm trying to generate a series of monthly dates from a starting date, which happens to be the date of the oldest user in my users table.

Whilst I can select some dates quite easily;

SELECT generate_series(
now(),
now() + '5 months'::interval,
'1 month'::interval);

and can select the date I need to start at:

SELECT to_date( to_char(CAST(min(created_at) AS DATE),'yyyy-MM') || '-01','yyyy-mm-dd') from users

How can I combine the two so that I'm selecting every month up until now?

Upvotes: 0

Views: 81

Answers (2)

Erwin Brandstetter
Erwin Brandstetter

Reputation: 659207

Turns out, it can be even simpler. :)

SELECT generate_series(
          date_trunc('year', min(created_at))
        , now()
        , interval '1 month') AS month;
FROM   users;

More about date_trunc in the manual.

Or, if you actually want the data type date instead of timestamp with time zone:

SELECT generate_series(
          date_trunc('year', min(created_at))
        , now()
        , interval '1 month')::date AS month;
FROM   users;

Upvotes: 1

Neil Middleton
Neil Middleton

Reputation: 22240

Turns out it's pretty simple:

SELECT generate_series(
(SELECT to_date( to_char(CAST(min(created_at) AS DATE),'yyyy-MM') || '-01','yyyy-mm-dd') from users),
now(),
'1 month'::interval) as month;

Upvotes: 1

Related Questions