Passing a dynamic interval to generate_series()

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|

SELECT ('1 ' || 'month')::interval;

interval|
--------+
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|

Anyway, you solved my issue, so thank you very much as always,

Igal

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 wrote show 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.