From: | Igal Sapir <igal(at)lucee(dot)org> |
---|---|
To: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
Cc: | pgsql-general <pgsql-general(at)lists(dot)postgresql(dot)org> |
Subject: | Re: Passing a dynamic interval to generate_series() |
Date: | 2024-06-30 23:17:31 |
Message-ID: | CA+zig08QbXa9zkLJuK_QDiBon1Wq6T1gqwyr_4xCcY-i2=ePhA@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
On Sun, Jun 30, 2024 at 3:51 PM Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> 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)
>
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
>
From | Date | Subject | |
---|---|---|---|
Next Message | Francisco Olarte | 2024-07-01 07:20:11 | Re: Passing a dynamic interval to generate_series() |
Previous Message | Tom Lane | 2024-06-30 22:51:14 | Re: Passing a dynamic interval to generate_series() |