[prev in list] [next in list] [prev in thread] [next in thread] 

List:       postgresql-general
Subject:    Re: Help with writing a generate_series(tsmultirange, interval)
From:       Adrian Klaver <adrian.klaver () aklaver ! com>
Date:       2021-07-31 19:49:39
Message-ID: 7aa77027-63c5-21df-181f-0852f9ceadf9 () aklaver ! com
[Download RAW message or body]

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.klaver@aklaver.com


[prev in list] [next in list] [prev in thread] [next in thread]