From: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
---|---|
To: | Igal Sapir <igal(at)lucee(dot)org> |
Cc: | pgsql-general <pgsql-general(at)lists(dot)postgresql(dot)org> |
Subject: | Re: Passing a dynamic interval to generate_series() |
Date: | 2024-06-30 22:51:14 |
Message-ID: | 2537942.1719787874@sss.pgh.pa.us |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
Igal Sapir <igal(at)lucee(dot)org> writes:
> But this throws an error (SQL Error [42601]: ERROR: syntax error at or near
> "'1 '"):
> SELECT generate_series(
> date_trunc('month', current_date),
> date_trunc('month', current_date + interval '7 month'),
> interval ('1 ' || 'month')::interval
> )
You're overthinking it.
SELECT generate_series(
date_trunc('month', current_date),
date_trunc('month', current_date + interval '7 month'),
('1 ' || 'month')::interval
);
generate_series
------------------------
2024-06-01 00:00:00-04
2024-07-01 00:00:00-04
2024-08-01 00:00:00-04
2024-09-01 00:00:00-04
2024-10-01 00:00:00-04
2024-11-01 00:00:00-04
2024-12-01 00:00:00-05
2025-01-01 00:00:00-05
(8 rows)
It might help to read this:
https://www.postgresql.org/docs/current/sql-syntax-lexical.html#SQL-SYNTAX-CONSTANTS-GENERIC
and to experiment with what you get from the constituent elements
of what you tried, rather than trying to guess what they are from
generate_series's behavior. For example,
select (interval '1 ');
interval
----------
00:00:01
(1 row)
select (interval '1 ' || 'month');
?column?
---------------
00:00:01month
(1 row)
regards, tom lane
From | Date | Subject | |
---|---|---|---|
Next Message | Igal Sapir | 2024-06-30 23:17:31 | Re: Passing a dynamic interval to generate_series() |
Previous Message | Igal Sapir | 2024-06-30 22:39:26 | Passing a dynamic interval to generate_series() |