Thread: Passing a dynamic interval to generate_series()
Hello,
I am trying to pass a dynamic interval to generate_series() with date range.
This works as expected, and generates a series with an interval of 1 month:
date_trunc('month', current_date),
date_trunc('month', current_date + interval '7 month'),
interval '1 month'
)
This works as expected and returns an interval of 1 month:
SELECT ('1 ' || 'month')::interval;
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
)
date_trunc('month', current_date),
date_trunc('month', current_date + interval '7 month'),
interval ('1 ' || 'month')::interval
)
And this returns a series with interval of 1 second??
SELECT generate_series(
date_trunc('month', current_date),
date_trunc('month', current_date + interval '7 month'),
(interval '1 ' || 'month')::interval
)
date_trunc('month', current_date),
date_trunc('month', current_date + interval '7 month'),
(interval '1 ' || 'month')::interval
)
Because this returns an interval of 1 second:
SELECT (interval '1 ' || 'month')::interval;
Is that a bug?
I am able to work around the issue using a CASE statement, but shouldn't it work simply by concatenating the string with the || operator?
Thank you,
Igal
Igal Sapir <igal@lucee.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
On Sun, Jun 30, 2024 at 3:51 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:
Igal Sapir <igal@lucee.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)
Thank you, Tom. I thought that I tried that too, but apparently I did not because it works the way you wrote it.
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)
I actually did test the expression that I posted, but it might be casting it twice. While your examples that you wrote show 1 month correctly:
SELECT (interval '1 ' || 'month');
?column? |
-------------+
00:00:01month|
-------------+
00:00:01month|
SELECT ('1 ' || 'month')::interval;
interval|
--------+
1 mon|
--------+
1 mon|
When the expression includes the "::interval" suffix as in the example that I posted it returns 1 second, possibly because it is casting to interval twice (at least on PostgreSQL 16.2 (Debian 16.2-1.pgdg120+2)):
SELECT (interval '1 ' || 'month')::interval;
interval|
--------+
00:00:01|
--------+
00:00:01|
Anyway, you solved my issue, so thank you very much as always,
Igal
regards, tom lane
Hi Igal: On Mon, 1 Jul 2024 at 01:17, Igal Sapir <igal@lucee.org> wrote: > I actually did test the expression that I posted, but it might be casting it twice. While your examples that you wroteshow 1 month correctly: > SELECT (interval '1 ' || 'month'); > ?column? | > -------------+ > 00:00:01month| No, it does not, try it like this: s=> with a(x) as ( SELECT (interval '1 ' || 'month')) select x, pg_typeof(x) from a; x | pg_typeof ---------------+----------- 00:00:01month | text (1 row) And you'll understand what is happening. Cast to interval has higher priority then concatenation, so you are selecting a 1 second interval, casting it to text, '00:00:01', adding 'month' at end. This can also be noticed because month output would not use ':' and have spaces: s=> with a(x) as ( SELECT '001.00MONTHS'::interval) select x, pg_typeof(x) from a; x | pg_typeof -------+----------- 1 mon | interval (1 row) ( I used fractions, uppercase and no spaces on input to show how interval output normalizes ). Francisco Olarte.
Igal Sapir schrieb am 01.07.2024 um 00:39: > I am trying to pass a dynamic interval to generate_series() with date range. > > This works as expected, and generates a series with an interval of 1 month: > > SELECT generate_series( > date_trunc('month', current_date), > date_trunc('month', current_date + interval '7 month'), > interval '1 month' > ) > > > This works as expected and returns an interval of 1 month: > > SELECT ('1 ' || 'month')::interval; > > > 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 > ) I am a fan of make_interval() when it comes to creating intervals from dynamic parameters: SELECT generate_series( date_trunc('month', current_date), date_trunc('month', current_date + interval '7 month'), make_interval(months => 1) ) The value for make_interval() can e.g. passed as a parameter from your programming language.