Re: SQL:2011 PERIODS vs Postgres Ranges?

Hi Jeff,

Thanks for sharing your thoughts and encouragement! :-)

> The model in [7] is
> based heavily on pack/unpack operators, and it's hard for me to see
> how those fit into SQL. Also, the pack/unpack operators have some
> theoretical weirdness that the book does not make clear*.
>
> *: My question was about the significance
> of the order when packing on two intervals. Hugh Darwen was kind
> enough to reply at length, and offered a lot of insight, but was still
> somewhat inconclusive.

I'd be interested in seeing that conversation if you ever find it again.

I really like how Date/Darwen/Lorentzos use pack/unpack to explain
temporal operations as operating on every concurrent "instant"
separately, and then bringing the adjacent instants back together into
ranges again. Even if you don't materialize that approach, conceptually
it makes it easy to understand what's going on.

So what is great about the patch from Anton Dignös
(https://www.postgresql-archive.org/PROPOSAL-Temporal-query-processing-with-range-types-tt5913058.html)
is that (like Date/Darwen/Lorentzos) you still have temporal variants
for every operator in the relational algebra, but they give
straightforward & efficient implementations of each based on traditional
operators plus just their two new "normalize" and "align" operations. (I
think they renamed these in later papers/patches though?) Their main
paper is at https://files.ifi.uzh.ch/boehlen/Papers/modf174-dignoes.pdf
if anyone wants to read it. It's short! :-)

The biggest challenge implementing temporal operators in plain SQL is
merging/splitting ranges from the left & right sides of an operator so
they line up. A single row can get split into multiple rows, or several
rows might be merged into one, etc. You can see how tricky Snodgrass's
"coalesce" operation is in his book. I gave some example SQL to
implement coalesce with UNNEST plus a range_agg function at
https://github.com/pjungwir/range_agg but with the Dignös approach I
don't think you'd need that. Normalize/align targets roughly the same
problem.

Anyway I'd be curious whether the theoretical weirdness you found in
pack/unpack also applies to normalize/align.

Yours,

--
Paul ~{:-)
pj(at)illuminatedcomputing(dot)com