Search code examples
sqltime-seriesrowpostgresql-9.1generate-series

How to show all dates from a certain date range in horizontal row?

I have database table in PostgreSQL named as t1 like:

Name StartDate EndDate
Oct-18 2018-10-01 2018-10-05

I want the result for the date range like:

Oct-18 2018-10-01 2018-10-02 2018-10-03 2018-10-04 2018-10-05

with the help of generate_series() I can do it "vertically", but how to get the result in a single row?

Solution

  • Use generate_series(). But SQL does not allow a dynamic number of result columns. So you must wrap your result in a string, array or document type to make it work. Example with an ARRAY constructor in a LATERAL subquery - in Postgres 10 or later:

    SELECT t1.name, d.date_arr::date[]
FROM   t1
LEFT   JOIN LATERAL (
   SELECT ARRAY(SELECT generate_series(t1.startdate::timestamp
                                     , t1.enddate::timestamp
                                     , interval '1 day'))
   ) d(date_arr) ON true;

    Why (preferably) Postgres 10 or later?

    Why the cast to timestamp?

    Why LEFT JOIN .. ON true?

    Though LEFT JOIN is not necessary in this particular case (could be CROSS JOIN) because the ARRAY constructor always returns a row.

    Postgres 9.1

    LATERAL requires Postgres 9.3 or later. You can substitute with a correlated subquery:

    SELECT name
     , ARRAY(SELECT generate_series(startdate::timestamp
                                  , enddate::timestamp
                                  , interval '1 day')::date)
FROM   t1;

    Even works with pg 8.4:

    db<>fiddle here

    But consider upgrading to a current version.

    crosstab()?

    crosstab() cannot overcome the static nature of SQL, either. There are limited workarounds with prepared row types ...