Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1sO3no-00C8zU-J0 for pgsql-general@arkaria.postgresql.org; Sun, 30 Jun 2024 23:17:48 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1sO3nm-00DmKw-SW for pgsql-general@arkaria.postgresql.org; Sun, 30 Jun 2024 23:17:47 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1sO3nm-00DmKf-Cu for pgsql-general@lists.postgresql.org; Sun, 30 Jun 2024 23:17:46 +0000 Received: from mail-oa1-x30.google.com ([2001:4860:4864:20::30]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.94.2) (envelope-from ) id 1sO3nj-003vsV-8f for pgsql-general@lists.postgresql.org; Sun, 30 Jun 2024 23:17:45 +0000 Received: by mail-oa1-x30.google.com with SMTP id 586e51a60fabf-25da8a19acaso1017344fac.2 for ; Sun, 30 Jun 2024 16:17:43 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=lucee.org; s=google; t=1719789462; x=1720394262; darn=lists.postgresql.org; h=cc:to:subject:message-id:date:from:in-reply-to:references :mime-version:from:to:cc:subject:date:message-id:reply-to; bh=bnedVooom7iQuhtpp8XynQtQaWbGndizgbCNKqW6oRs=; b=hbJbdDFsmnXUuzr+kK2bzFW19wz0VGJijL1dP7G0eRVqxRC8yaNeL73b6mq8o79BkF wsX7iP/LqZ0VbllyDpT55fwhIjvaToBaEEQR+5w1rQdrxFCcn/F9v+P7ELDldl3aqzwN IpxTwKDtBEQX48fwf0YEmH4AHtNXTRiCs6ApiXeeX0YhDGkJoDSBwmcgkQVZGOACx+Gu DoqNW1809vJOV0DMBGRGDLvjaegni6mbvBDGofElr/eZHoG2TGtAPelMGOqsIU8R1046 5cgX4CVpLGUG2EqSQaC6Eah/uuTTuz48TZjRJRLQO0pIuZFNdovhv/u8sKpvnJV9xA8S nS6g== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1719789462; x=1720394262; h=cc:to:subject:message-id:date:from:in-reply-to:references :mime-version:x-gm-message-state:from:to:cc:subject:date:message-id :reply-to; bh=bnedVooom7iQuhtpp8XynQtQaWbGndizgbCNKqW6oRs=; b=iUAzDByT0qKtkKQ9IDILmtZBDoCfCAWZFiyJQakA1od4kby4DX/POhtq0cnHKEyKF0 zfNQZXcggdUmhI7ok2hvKhlvlhJPQgFyUTHrG7wTzkaZF+DFZMg4tby7k8LpPqKMXivO xrQHL2Rw7pI9ZAFu/40DOk6UMO8L8rB6i7TbLucOikJnAf2Q5cDmKX5rnjhh9DJvZ8R1 +ADtTtmo/uJb6f6MnVLubP1HcLq1n1WrIj52XSOgzXCmWbe4DuijIWovW5ggoYZFb+Kt MSN8U8Kg9PgE+S6Nn5RD73mnldXLogue6NDTb2iUQq66JeSjrHq5DJ3LvyN09UpujN56 vrIw== X-Gm-Message-State: AOJu0Yyz1CMeMaO9xwblcvVUK3qqwPjUC1gMIQ5XHkzvy+qIYP1SiBv4 h8QmvUp8fNeX599qOPD/z/KWpbRs/gpP5CIO5bF/VHQKiGDTi0e8bFWfY4p5xRR8Qew1Vj0EfXZ hUkqRUDkW2E+sCcGnPsZD8h3yzGwYrCHpn0gT9w== X-Google-Smtp-Source: AGHT+IHPz2KXr3HQMWjXGXj0/cKxsu91B+ROuOcc4m1lY0Qb3EJCVoNUM3jXl9jwgJ0E2kj74JJAl0YPN1V4KhGbjXY= X-Received: by 2002:a05:6870:568e:b0:25d:7fd1:cd15 with SMTP id 586e51a60fabf-25db3442fefmr3715713fac.4.1719789462508; Sun, 30 Jun 2024 16:17:42 -0700 (PDT) MIME-Version: 1.0 References: <2537942.1719787874@sss.pgh.pa.us> In-Reply-To: <2537942.1719787874@sss.pgh.pa.us> From: Igal Sapir Date: Sun, 30 Jun 2024 16:17:31 -0700 Message-ID: Subject: Re: Passing a dynamic interval to generate_series() To: Tom Lane Cc: pgsql-general Content-Type: multipart/alternative; boundary="0000000000000074db061c23b040" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --0000000000000074db061c23b040 Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable On Sun, Jun 30, 2024 at 3:51=E2=80=AFPM Tom Lane wrote: > Igal Sapir 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-SYNTA= X-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 > --0000000000000074db061c23b040 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
On Sun, Jun 30, 2024 at 3:51=E2=80=AFPM T= om 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(
>=C2=A0 =C2=A0 =C2=A0date_trunc('month', current_date),
>=C2=A0 =C2=A0 =C2=A0date_trunc('month', current_date + interval= '7 month'),
>=C2=A0 =C2=A0 =C2=A0interval ('1 ' || 'month')::interva= l
> )

You're overthinking it.

SELECT generate_series(
=C2=A0 =C2=A0 date_trunc('month', current_date),
=C2=A0 =C2=A0 date_trunc('month', current_date + interval '7 mo= nth'),
=C2=A0 =C2=A0 ('1 ' || 'month')::interval
);
=C2=A0 =C2=A0 generate_series=C2=A0 =C2=A0 =C2=A0
------------------------
=C2=A02024-06-01 00:00:00-04
=C2=A02024-07-01 00:00:00-04
=C2=A02024-08-01 00:00:00-04
=C2=A02024-09-01 00:00:00-04
=C2=A02024-10-01 00:00:00-04
=C2=A02024-11-01 00:00:00-04
=C2=A02024-12-01 00:00:00-05
=C2=A02025-01-01 00:00:00-05
(8 rows)

Thank you, Tom.=C2=A0 I t= hought that I tried that too, but apparently I did not because it works the= way you wrote it.

=C2=A0

It might help to read this:

https://= www.postgresql.org/docs/current/sql-syntax-lexical.html#SQL-SYNTAX-CONSTANT= S-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.=C2=A0 For example,

select (interval '1 ');
=C2=A0interval
----------
=C2=A000:00:01
(1 row)

select (interval '1 ' || 'month');
=C2=A0 =C2=A0?column?=C2=A0 =C2=A0
---------------
=C2=A000:00:01month
(1 row)

I actually did test the express= ion that I posted, but it might be casting it twice.=C2=A0 While your examp= les that you wrote show 1 month correctly:

SELECT (interval '1 ' || 'month&= #39;);

?column? =C2=A0 =C2=A0 |
= -------------+
00:00:01month|

SELECT ('1 ' || 'month')::inter= val;

interval|
--------+
=C2= =A0 =C2=A01 mon|

When the expression includ= es the "::interval" suffix as in the example that I posted it ret= urns 1 second, possibly because it is casting to interval twice (at least o= n PostgreSQL 16.2 (Debian 16.2-1.pgdg120+2)):

SELECT (interval '1 ' || 'mon= th')::interval;
=
interval|
--= ------+
00:00:01|

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

Igal
<= /div>

=C2=A0

=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 regards, tom lane
--0000000000000074db061c23b040--