Re: Handling time series data with PostgreSQL

I think the OP may be referring to Oracle's Temporal Validity feature.
This type of feature has yet to be implemented in PostgreSQL (see
https://www.postgresql.org/docs/13/unsupported-features-sql-standard.html
item T181).

Temporal Validity allows you to add a time dimension to any table, and only
display rows of data that are valid for the requested time period.
Oracle's implementation of Temporal Validity uses the PERIOD FOR clause in
CREATE TABLE, ALTER TABLE, and SELECT statements as illustrated below:

CREATE TABLE EMPLOYEE
(
ID NUMBER PRIMARY KEY,
TAX_ID VARCHAR2(10),
HIRE_DATE TIMESTAMP,
TERM_DATE TIMESTAMP,
PERIOD FOR EMP_VALID_TIME (HIRE_DATE, TERM_DATE)
);

SELECT * FROM EMPLOYEE
VERSIONS PERIOD FOR EMP_VALID_TIME
BETWEEN TO_TIMESTAMP('06-OCT-2013', 'DD-MON-YYYY')
AND TO_TIMESTAMP('31-OCT-2013', 'DD-MON-YYYY');

ID TAX_ID HIRE_DATE TERM_DATE
----- ---------- ------------------------------
------------------------------
1 123456789 06-OCT-13 12.00.00.000000 AM 07-NOV-15 12.00.00.000000 AM
2 222456789 07-OCT-13 12.00.00.000000 AM
4 444004444
5 505050505 30-OCT-13 12.00.00.000000 AM 31-OCT-13 12.00.00.000000 AM
6 666999666 30-SEP-13 12.00.00.000000 AM 31-DEC-13 12.00.00.000000 AM

The above feature requires Oracle 12 or higher. SQL Server 2016 and later
also support it. In earlier releases of each DBMS we tried to accomplish
the same by adding pairs of timestamp columns to each table and then
writing our own code to handle row filtering. Partitioning isn't needed.
Certainly partitioning by range could be used, but it would still require
some manual efforts.

-Mark

On Wed, Oct 7, 2020 at 10:41 AM Stephen Frost <sfrost(at)snowman(dot)net> wrote:

> Greetings,
>
> * Jayaram (jairamcbe(at)gmail(dot)com) wrote:
> > So, Do we need the timescaleDB as mandatory to handle time series data?
> Is
> > there any way to handle hourly to days,months,yearly data with PGSQL
> alone
> > without timescale addon?
>
> Certainly there is and a lot of people do it- what isn't clear is what
> it is you feel is missing from PG when it comes to handling time series
> data..? Generally speaking there's concerns about PG's ability to
> handle lots of partitions (which comes from there being very large
> amounts of data being stored), but v12 and v13 have made great
> improvements in that area and it's not nearly an issue any longer (and
> performs better in quite a few cases than extensions).
>
> > Ours is a new project and we are unsure about whether we should have both
> > timescaleDB and PGSQL or PGSQL alone is capable of handling this time
> > series data by tuning the right indexes.etc..
>
> Partitioning and index tuning in PG (look at using BRIN if you haven't
> already...) is important when you get to larger data volumes.
>
> Thanks,
>
> Stephen
>