Re: PG and Temporal

On Mon, Jun 1, 2015 at 1:25 PM, Steve Midgley <science(at)misuse(dot)org> wrote:
>
> I think there are a lot of theories as to how to make temporal table systems
> work. It hugely depends on your requirements. That said, the data warehouse
> community has built a kind of solution with dimension tables representing
> time, and fact tables aligning to those dimensions via relations. It makes
> certain temporal "grain size" problems much easier to solve (and usually
> faster). Though using Pg's date extraction functions I've gotten pretty
> amazing performance as well: basically creating on-demand time dimensions as
> needed.. Read Ralph Kimball's work on data warehousing for a good
> introduction.
>
> This isn't the same as creating versioned tables per your references above
> but I hope will be useful in your research..

The bi-temporal I am talking about has nothing to do with
datawarehouse. It basically needs two things to be satisfied

1. What was my table like as on a particular date. Like SELECT * FROM
TABLE WHERE system_time =

This is called system time.

2. What is the effective date of a row. Imagine interest rate which
has a start date and an end date. This is called business time. By
implementing business time in the database, lot of checks can be
pushed to the server (like constraint checking).

Currently only DB2 offers full implementation (SQL 2011) of both (1) and (2).

The add-on I downloaded offers (1) only and that too partial, as
there is no sql support for querying a table as of a system time.