Postgres Pro
Downloads
Home   >   mailing lists

Re: Passing a dynamic interval to generate_series() - Mailing list pgsql-general

From Igal Sapir
Subject Re: Passing a dynamic interval to generate_series()
Date
Msg-id CA+zig08QbXa9zkLJuK_QDiBon1Wq6T1gqwyr_4xCcY-i2=ePhA@mail.gmail.com
Whole thread Raw
In response to Re: Passing a dynamic interval to generate_series()  (Tom Lane <tgl@sss.pgh.pa.us>)
Responses Re: Passing a dynamic interval to generate_series()
List pgsql-general
Tree view
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

 

                        regards, tom lane

pgsql-general by date:

Previous
From: Tom Lane
Date:
Subject: Re: Passing a dynamic interval to generate_series()
Next
From: Francisco Olarte
Date:
Subject: Re: Passing a dynamic interval to generate_series()