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;
instant
---------------------
2021-08-02 08:00:00
2021-08-02 09:30:00
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
François
--
Adrian Klaver
adrian.kla...@aklaver.com
