sqldatabasepostgresqldategenerate-series

Is it possible to avoid CASE ... WHEN in generate_series statement when two different beginning dates are required


I have one query that I use for different date sequence generation depending on the query parameters from the backend.

Gist for it is that for the first month, I need date to be generate_series input date and for the rest of the generated months' date should be the beginning of the month as shown in the results below.

Here are the three ways, how I use it below with the correct results.

Example for month sequence ->

    SELECT
        date::DATE AS sequence_value,
        CASE WHEN date = '2022-01-16'
            THEN date::DATE
            ELSE date_trunc('MONTH', date)::DATE END AS date,
        (DATE_TRUNC('MONTH', date) + INTERVAL '1 MONTH')::DATE AS date_end,
        to_char(date, 'MONTH') AS name
    FROM GENERATE_SERIES('2022-01-16', '2023-01-02', INTERVAL '1 MONTH') AS date

RESULTS:

"sequence_value"    "date"       "date_end"       "name"
"2022-01-16"    "2022-01-16"    "2022-02-01"    "JANUARY  "
"2022-02-16"    "2022-02-01"    "2022-03-01"    "FEBRUARY "
"2022-03-16"    "2022-03-01"    "2022-04-01"    "MARCH    "
"2022-04-16"    "2022-04-01"    "2022-05-01"    "APRIL    "

Example for week sequence ->

    SELECT
        date::DATE AS sequence_value,
        CASE WHEN date = '2022-01-16'
            THEN date::DATE
            ELSE date_trunc('WEEK', date)::DATE END AS date,
        (DATE_TRUNC('WEEK', date) + INTERVAL '1 WEEK')::DATE AS date_end,
        to_char(date, 'MM-DD') AS name
    FROM GENERATE_SERIES('2022-01-16', '2023-01-02', INTERVAL '1 WEEK') AS date


RESULTS:


"sequence_value"    "date"       "date_end"      "name"
"2022-01-16"    "2022-01-16"    "2022-01-17"    "01-16"
"2022-01-23"    "2022-01-17"    "2022-01-24"    "01-23"
"2022-01-30"    "2022-01-24"    "2022-01-31"    "01-30"
"2022-02-06"    "2022-01-31"    "2022-02-07"    "02-06"

Example for day sequence ->

    SELECT
        date::DATE AS sequence_value,
        CASE WHEN date = '2022-01-16'
            THEN date::DATE
            ELSE date_trunc('DAY', date)::DATE END AS date,
        (DATE_TRUNC('DAY', date) + INTERVAL '1 DAY')::DATE AS date_end,
        to_char(date, 'MM-DD') AS name
    FROM GENERATE_SERIES('2022-01-16', '2023-01-02', INTERVAL '1 DAY') AS date

RESULTS:

"sequence_value"    "date"        "date_end"    "name"
"2022-01-16"    "2022-01-16"    "2022-01-17"    "01-16"
"2022-01-17"    "2022-01-17"    "2022-01-18"    "01-17"
"2022-01-18"    "2022-01-18"    "2022-01-19"    "01-18"
"2022-01-19"    "2022-01-19"    "2022-01-20"    "01-19"

Is there a way to make the query more concise and efficient?

Perhaps, it is possible to replace the CASE ... WHEN statement with something else?

As of now, I don't really need it in the day sequence generation as it generates the dates day by day, however, for the month sequence generation it is necessary, as otherwise, I would get the month starting date for the day 16th.


Solution

  • These are a couple of the ways I'd go about it...

    fiddle

    SELECT
      GREATEST('2022-01-16'::date, s.date                     )   AS date_start,
      LEAST(   '2023-01-01'::date, s.date + INTERVAL '1 MONTH')   AS date_end,
      to_char(s.date, 'MONTH')                                    AS name
    FROM
      GENERATE_SERIES(
        DATE_TRUNC('MONTH', '2022-01-16'::date),
                            '2023-01-01'::date - INTERVAL '1 DAY',
        INTERVAL '1 MONTH'
      )
        AS s
    
    date_start date_end name
    2022-01-16 2022-02-01 00:00:00 JANUARY
    2022-02-01 2022-03-01 00:00:00 FEBRUARY
    2022-03-01 2022-04-01 00:00:00 MARCH
    2022-04-01 2022-05-01 00:00:00 APRIL
    2022-05-01 2022-06-01 00:00:00 MAY
    2022-06-01 2022-07-01 00:00:00 JUNE
    2022-07-01 2022-08-01 00:00:00 JULY
    2022-08-01 2022-09-01 00:00:00 AUGUST
    2022-09-01 2022-10-01 00:00:00 SEPTEMBER
    2022-10-01 2022-11-01 00:00:00 OCTOBER
    2022-11-01 2022-12-01 00:00:00 NOVEMBER
    2022-12-01 2023-01-01 00:00:00 DECEMBER
    SELECT 12
    
    SELECT
      GREATEST('2022-01-16'::date, s.date)   AS date_start,
      LEAST(   '2023-01-01'::date, s.date)   AS date_end,
      to_char(m.month_start, 'MONTH')        AS name
    FROM
      GENERATE_SERIES(
        '2022-01-16'::date,
        '2023-01-01'::date - INTERVAL '1 DAY',
        INTERVAL '1 MONTH'
      )
        AS s
      CROSS JOIN LATERAL
      (
        SELECT
          DATE_TRUNC('MONTH', s.date)                       AS month_start,
          DATE_TRUNC('MONTH', s.date) + INTERVAL '1 MONTH'  AS month_end
      )
        AS m
    
    date_start date_end name
    2022-01-16 2022-01-16 JANUARY
    2022-02-16 2022-02-16 FEBRUARY
    2022-03-16 2022-03-16 MARCH
    2022-04-16 2022-04-16 APRIL
    2022-05-16 2022-05-16 MAY
    2022-06-16 2022-06-16 JUNE
    2022-07-16 2022-07-16 JULY
    2022-08-16 2022-08-16 AUGUST
    2022-09-16 2022-09-16 SEPTEMBER
    2022-10-16 2022-10-16 OCTOBER
    2022-11-16 2022-11-16 NOVEMBER
    2022-12-16 2022-12-16 DECEMBER
    SELECT 12
    
    SELECT
      GREATEST('2022-01-16'::date, s.date                    )   AS date_start,
      LEAST(   '2023-01-01'::date, s.date + INTERVAL '1 WEEK')   AS date_end,
      to_char(s.date, 'MM-DD')                                   AS name
    FROM
      GENERATE_SERIES(
        DATE_TRUNC('WEEK', '2022-01-16'::date),
                           '2023-01-01'::date - INTERVAL '1 DAY',
        INTERVAL '1 WEEK'
      )
        AS s
    
    date_start date_end name
    2022-01-16 2022-01-17 00:00:00 01-10
    2022-01-17 2022-01-24 00:00:00 01-17
    2022-01-24 2022-01-31 00:00:00 01-24
    2022-01-31 2022-02-07 00:00:00 01-31
    2022-02-07 2022-02-14 00:00:00 02-07
    2022-02-14 2022-02-21 00:00:00 02-14
    2022-02-21 2022-02-28 00:00:00 02-21
    2022-02-28 2022-03-07 00:00:00 02-28
    2022-03-07 2022-03-14 00:00:00 03-07
    2022-03-14 2022-03-21 00:00:00 03-14
    2022-03-21 2022-03-28 00:00:00 03-21
    2022-03-28 2022-04-04 00:00:00 03-28
    2022-04-04 2022-04-11 00:00:00 04-04
    2022-04-11 2022-04-18 00:00:00 04-11
    2022-04-18 2022-04-25 00:00:00 04-18
    2022-04-25 2022-05-02 00:00:00 04-25
    2022-05-02 2022-05-09 00:00:00 05-02
    2022-05-09 2022-05-16 00:00:00 05-09
    2022-05-16 2022-05-23 00:00:00 05-16
    2022-05-23 2022-05-30 00:00:00 05-23
    2022-05-30 2022-06-06 00:00:00 05-30
    2022-06-06 2022-06-13 00:00:00 06-06
    2022-06-13 2022-06-20 00:00:00 06-13
    2022-06-20 2022-06-27 00:00:00 06-20
    2022-06-27 2022-07-04 00:00:00 06-27
    2022-07-04 2022-07-11 00:00:00 07-04
    2022-07-11 2022-07-18 00:00:00 07-11
    2022-07-18 2022-07-25 00:00:00 07-18
    2022-07-25 2022-08-01 00:00:00 07-25
    2022-08-01 2022-08-08 00:00:00 08-01
    2022-08-08 2022-08-15 00:00:00 08-08
    2022-08-15 2022-08-22 00:00:00 08-15
    2022-08-22 2022-08-29 00:00:00 08-22
    2022-08-29 2022-09-05 00:00:00 08-29
    2022-09-05 2022-09-12 00:00:00 09-05
    2022-09-12 2022-09-19 00:00:00 09-12
    2022-09-19 2022-09-26 00:00:00 09-19
    2022-09-26 2022-10-03 00:00:00 09-26
    2022-10-03 2022-10-10 00:00:00 10-03
    2022-10-10 2022-10-17 00:00:00 10-10
    2022-10-17 2022-10-24 00:00:00 10-17
    2022-10-24 2022-10-31 00:00:00 10-24
    2022-10-31 2022-11-07 00:00:00 10-31
    2022-11-07 2022-11-14 00:00:00 11-07
    2022-11-14 2022-11-21 00:00:00 11-14
    2022-11-21 2022-11-28 00:00:00 11-21
    2022-11-28 2022-12-05 00:00:00 11-28
    2022-12-05 2022-12-12 00:00:00 12-05
    2022-12-12 2022-12-19 00:00:00 12-12
    2022-12-19 2022-12-26 00:00:00 12-19
    2022-12-26 2023-01-01 00:00:00 12-26
    SELECT 51