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.
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@xxxxxxxxxxx> wrote:
Greetings,
* Jayaram (jairamcbe@xxxxxxxxx) 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
