public inbox for [email protected]help / color / mirror / Atom feed
Re: Passing a dynamic interval to generate_series() 3+ messages / 3 participants [nested] [flat]
* Re: Passing a dynamic interval to generate_series() @ 2024-06-30 22:51 Tom Lane <[email protected]> 2024-06-30 23:17 ` Re: Passing a dynamic interval to generate_series() Igal Sapir <[email protected]> 0 siblings, 1 reply; 3+ messages in thread From: Tom Lane @ 2024-06-30 22:51 UTC (permalink / raw) To: Igal Sapir <[email protected]>; +Cc: pgsql-general <[email protected]> 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 ^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Passing a dynamic interval to generate_series() 2024-06-30 22:51 Re: Passing a dynamic interval to generate_series() Tom Lane <[email protected]> @ 2024-06-30 23:17 ` Igal Sapir <[email protected]> 2024-07-01 07:20 ` Re: Passing a dynamic interval to generate_series() Francisco Olarte <[email protected]> 0 siblings, 1 reply; 3+ messages in thread From: Igal Sapir @ 2024-06-30 23:17 UTC (permalink / raw) To: Tom Lane <[email protected]>; +Cc: pgsql-general <[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 > ^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Passing a dynamic interval to generate_series() 2024-06-30 22:51 Re: Passing a dynamic interval to generate_series() Tom Lane <[email protected]> 2024-06-30 23:17 ` Re: Passing a dynamic interval to generate_series() Igal Sapir <[email protected]> @ 2024-07-01 07:20 ` Francisco Olarte <[email protected]> 0 siblings, 0 replies; 3+ messages in thread From: Francisco Olarte @ 2024-07-01 07:20 UTC (permalink / raw) To: Igal Sapir <[email protected]>; +Cc: Tom Lane <[email protected]>; pgsql-general <[email protected]> Hi Igal: On Mon, 1 Jul 2024 at 01:17, Igal Sapir <[email protected]> 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. ^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2024-07-01 07:20 UTC | newest] Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed) -- links below jump to the message on this page -- 2024-06-30 22:51 Re: Passing a dynamic interval to generate_series() Tom Lane <[email protected]> 2024-06-30 23:17 ` Igal Sapir <[email protected]> 2024-07-01 07:20 ` Francisco Olarte <[email protected]>
This inbox is served by agora; see mirroring instructions for how to clone and mirror all data and code used for this inbox