Thread

1. Passing a dynamic interval to generate_series()

   Igal Sapir <igal@lucee.org> — 2024-06-30T22:39:26Z

   Hello,
   
   I am trying to pass a dynamic interval to generate_series() with date range.
   
   This works as expected, and generates a series with an interval of 1 month:
   
   SELECT generate_series(
       date_trunc('month', current_date),
       date_trunc('month', current_date + interval '7 month'),
       interval '1 month'
   )
   
   
   This works as expected and returns an interval of 1 month:
   
   SELECT ('1 ' || 'month')::interval;
   
   
   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
   )
   
   
   And this returns a series with interval of 1 second??
   
   SELECT generate_series(
       date_trunc('month', current_date),
       date_trunc('month', current_date + interval '7 month'),
       (interval '1 ' || 'month')::interval
   )
   
   Because this returns an interval of 1 second:
   
   SELECT (interval '1 ' || 'month')::interval;
   
   Is that a bug?
   
   
   I am able to work around the issue using a CASE statement, but shouldn't it
   work simply by concatenating the string with the || operator?
   
   Thank you,
   
   Igal
   

2. Re: Passing a dynamic interval to generate_series()

   Tom Lane <tgl@sss.pgh.pa.us> — 2024-06-30T22:51:14Z

   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)
   
   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
   
   
   
   

3. Re: Passing a dynamic interval to generate_series()

   Igal Sapir <igal@lucee.org> — 2024-06-30T23:17:31Z

   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
   >
   

4. Re: Passing a dynamic interval to generate_series()

   Francisco Olarte <folarte@peoplecall.com> — 2024-07-01T07:20:11Z

   Hi Igal:
   
   On Mon, 1 Jul 2024 at 01:17, Igal Sapir <igal@lucee.org> wrote:
   
   > 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|
   
   No, it does not, try it like this:
   s=> with a(x) as ( SELECT (interval '1 ' || 'month')) select x,
   pg_typeof(x) from a;
          x       | pg_typeof
   ---------------+-----------
    00:00:01month | text
   (1 row)
   
   And you'll understand what is happening. Cast to interval has higher
   priority then concatenation, so you are selecting a 1 second interval,
   casting it to text, '00:00:01', adding 'month' at end.
   
   This can also be noticed because month output would not use ':' and have spaces:
   s=> with a(x) as ( SELECT '001.00MONTHS'::interval) select x,
   pg_typeof(x) from a;
      x   | pg_typeof
   -------+-----------
    1 mon | interval
   (1 row)
   
   ( I used fractions, uppercase and no spaces on input to show how
   interval output normalizes ).
   
   Francisco Olarte.
   
   
   
   

5. Re: Passing a dynamic interval to generate_series()

   shammat@gmx.net — 2024-07-01T07:37:24Z

   Igal Sapir schrieb am 01.07.2024 um 00:39:
   > I am trying to pass a dynamic interval to generate_series() with date range.
   >
   > This works as expected, and generates a series with an interval of 1 month:
   >
   > SELECT generate_series(
   >     date_trunc('month', current_date),
   >     date_trunc('month', current_date + interval '7 month'),
   >     interval '1 month'
   > )
   >
   >
   > This works as expected and returns an interval of 1 month:
   >
   > SELECT ('1 ' || 'month')::interval;
   >
   >
   > 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
   > )
   
   I am a fan of make_interval() when it comes to creating intervals from dynamic parameters:
   
   SELECT generate_series(
       date_trunc('month', current_date),
       date_trunc('month', current_date + interval '7 month'),
       make_interval(months => 1)
   )
   
   
   The value for make_interval() can e.g. passed as a parameter from your programming language.