Re: Understanding the differences between Temporal tables, CDC and Time Series.

On 10.11.21 07:43, SQL Padawan wrote:
> I'm looking into Temporal Tables (TT - i.e. System/Application time
> versioning) but I would like to understand the differences between TTs
> and two other features (which appear similar in many ways) - Change Data
> Capture and Time Series.
>
> Are Temporal Tables (in a sense) a query framework framework around CDC?
>
> And if that's the case, what then are Time Series - which are
> specifically designed to host (time) changing data?
>
> Are Temporal Tables (again, in a sense) Time Series databases which only
> record changes in time(-stamps)?
>
> I'm unclear as to the "philosophical" distinctions here and would be
> grateful if anybody could explain the diffence(s) between them?

A time series database contains data records containing a time stamp (a
point in time). These records represent things that happened, for
example log files, measurements, events. This data is then usually used
for analytics, such as, how many web site visitors did we have per hour
over the last week.

A temporal database contains records that have a timestamp range (in
PostgreSQL terms, also known as period or interval, delimited by two
points in time). There are (at least) two ways to use this. One is
that the database system automatically records when a record was
inserted, updated, and deleted, so that historical versions of records
are automatically kept, and you can then query, what was the value of
this at this time, or more generally what was the result of this query
at this time. Use cases here are record-keeping, auditing, and the
like. The other is that the database users themselves enter when a
record is supposed to be valid, and the database system takes this into
account in queries. So you could have product prices or salaries that
automatically change at a certain time. This is basically a tool to
make development of certain database applications more convenient.

So these two concepts are really totally separate and independent
things, except that they have a word based on "time" in their name.