Hello Adrian,
> Le 31 juill. 2021 à 15:49, Adrian Klaver <[email protected]> a écrit :
>
> On 7/31/21 11:59 AM, François Beausoleil wrote:
>> Hello all!
>> I’m excited for multi ranges, as they fit nicely into a scheduling app. What
>> I’m trying to express is something along the lines of « Every weekday from
>> 2021-08-01 and 2021-10-01, from 9 AM to 8 PM, every 90 minutes ». You can
>> think of public transit for the model.
>> Initially, I was going to create a table with every departure recorded, and
>> was going to refresh the table on every write to the parent table, but that
>> means maintaining many rows for every change to the schedule. Then, I
>> remembered multi ranges in PG14, and they fit nicely with what I had in mind.
>> Now that I can store the info I want, I’d like to iterate over the ranges,
>> so I can generate the exact departure times, something similar to this:
>> SELECT instant
>> FROM generate_series(
>> '{["2021-08-02 08:00:00","2021-08-02 11:00:00"),["2021-08-03
>> 08:00:00","2021-08-03 20:00:00"]’}
>> , interval ’90 minutes’) as instant;
>> 2021-08-02 08:00
>> 2021-08-02 09:30
>> — 2021-08-02 11:00 excluded as the range excludes its upper bound
>> 2021-08-03 08:00:00
>> 2021-08-03 09:30:00
>> 2021-08-03 11:00:00
>> 2021-08-03 12:30:00
>> 2021-08-03 14:00:00
>> 2021-08-03 15:30:00
>> 2021-08-03 17:00:00
>> 2021-08-03 18:30:00
>> 2021-08-03 20:00:00 — included, as the upper bound is inclusive
>> That function doesn’t exist, and I can’t seem to find a function to iterate
>> over a multi range either. Does such a function exist? I’m specifically
>> looking at https://www.postgresql.org/docs/14/functions-range.html.
>> This is a toy application, a spike to see what’s possible.
>> I wanted to avoid maintaining a table with hundreds of rows per route, if a
>> route runs frequently enough (every 10 minutes, 8 AM to 8 PM, over a year).
>> Of course, I can avoid refreshing the departures table if the schedule
>> hasn’t changed, but still, preparing this table will not take a constant
>> amount of time; e.g. it will depend on the schedule’s size.
>> Any tips appreciated!
>
> How about:
>
> SELECT
> *
> FROM
> generate_series ('2021-08-02 08:00:00'::timestamp, '2021-08-02
> 10:59:00'::timestamp, interval '90 minutes') AS instant
> UNION
> SELECT
> *
> FROM
> generate_series ('2021-08-03 08:00:00'::timestamp, '2021-08-03
> 20:00:00'::timestamp, interval '90 minutes') AS instant
> ORDER BY instant;
Yes, in fact, I wrote the following:
--------------------------------------------------------------------------------------------------------------------------
CREATE FUNCTION generate_series(tstzrange, interval) RETURNS SETOF timestamp
with time zone AS $$
SELECT n
FROM generate_series(lower($1), upper($1), $2) AS t0(n)
WHERE $1 @> n
$$ LANGUAGE sql immutable;
CREATE FUNCTION generate_series(tsrange, interval) RETURNS SETOF timestamp
without time zone AS $$
SELECT n
FROM generate_series(lower($1), upper($1), $2) AS t0(n)
WHERE $1 @> n
$$ LANGUAGE sql immutable;
That was the easy part. My end goal is to iterate over a tsmultirange: I would
like to get each individual range from a given multi range. Ideally, I’d like
to do that without parsing the textual version of the multi range.
While mowing the lawn, I thought that since the syntax of multi ranges is
similar to arrays, maybe I could use unnest(), but sadly, that was not to be
the case:
# select
unnest('{[2021-08-02,2021-08-04],[2021-08-07,2021-08-09)}'::tsmultirange);
ERROR: function unnest(tsmultirange) does not exist
LINE 1: select unnest('{[2021-08-02,2021-08-04],[2021-08-07,2021-08-...
Apparently, PG can accept multi range values, but can’t do much with them at
the time, except to check for inclusion/exclusion.
Thanks for your time!
François
>> François
>
>
> --
> Adrian Klaver
> [email protected] <mailto:[email protected]>
