Re: SQL:2011 PERIODS vs Postgres Ranges?

On Sun, 2018-10-21 at 22:10 +0300, Heikki Linnakangas wrote:
> On 21/10/2018 21:17, Paul A Jungwirth wrote:
> > 3. Build our own abstractions on top of ranges, and then use those
> > to
> > implement PERIOD-based features. This is the least clear option,
> > and I
> > imagine it would require a lot more design effort. Our range types
> > are
> > already a step in this direction. Does anyone think this approach
> > has
> > promise? If so I can start thinking about how we'd do it. I imagine
> > we
> > could use a lot of the ideas in [7].
> > ...
> > [7] C. J. Date, Hugh Darwen, Nikos Lorentzos. Time and Relational
> > Theory, Second Edition: Temporal Databases in the Relational Model
> > and
> > SQL. 2nd edition, 2014.
>
> +1 on this approach. I think [7] got the model right. If we can
> implement SQL-standard PERIODs on top of it, then that's a bonus,
> but
> having sane, flexible, coherent set of range operators is more
> important
> to me.

+1 for approach #3 from me as well. It was my original intention for
range types, though my first priority was utility and not the standard.
I think we are likely to run into a few areas where they aren't a
perfect fit to the standard, but I think it's a promising approach and
we can probably work around those issues by using special operators.

> What are we missing? It's been years since I read that book, but
> IIRC
> temporal joins is one thing, at least. What features do you have in
> mind?

We do support temporal joins, just not as efficiently as I'd like, and
the language doesn't make it quite as clear as it could be.

I look at that book as a source of inspiration, but I don't think it's
simple to map features one-to-one. For instance, 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*.

Regards,
Jeff Davis

*: I asked in a temporal discussion group (that was unfortunately a
part of LinkedIn circa 2011 and I can't find any reference to the
discussion outside my mailbox). 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.