public inbox for [email protected]  

help / color / mirror / Atom feed

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

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)

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

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

* 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