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

Question

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.

Answer 1

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