public inbox for [email protected]  

help / color / mirror / Atom feed

From: Igal Sapir <[email protected]>
To: Tom Lane <[email protected]>
Cc: pgsql-general <[email protected]>
Subject: Re: Passing a dynamic interval to generate_series()
Date: Sun, 30 Jun 2024 16:17:31 -0700
Message-ID: <CA+zig08QbXa9zkLJuK_QDiBon1Wq6T1gqwyr_4xCcY-i2=ePhA@mail.gmail.com> (raw)
In-Reply-To: <[email protected]>
References: <CA+zig0-suhFUeS7xcJyZ=4HDFXJ3Kn2adXo3B4zHUzDFgMscCw@mail.gmail.com>
	<[email protected]>

On Sun, Jun 30, 2024 at 3:51 PM Tom Lane <[email protected]> wrote:

> Igal Sapir <[email protected]> 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
>

view thread (3+ messages)  latest in thread

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: [email protected]
  Cc: [email protected], [email protected], [email protected]
  Subject: Re: Passing a dynamic interval to generate_series()
  In-Reply-To: <CA+zig08QbXa9zkLJuK_QDiBon1Wq6T1gqwyr_4xCcY-i2=ePhA@mail.gmail.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox