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.
These are a couple of the ways I'd go about it...
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