Re: Handling time series data with PostgreSQL

Greetings,

* Mark Johnson (remi9898(at)gmail(dot)com) wrote:
> I think the OP may be referring to Oracle's Temporal Validity feature.

Perhaps, but that's not the only way to manage time series data.

> [ ... ] 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.

I've found that using the range data types can work quite will, with
overlaps queries, to manage time-series data instead of using pairs of
timestamp columns. With range data types you can also create exclusion
constraints to ensure that you don't end up introducing overlapping
ranges.

Either way require adjusting your queries though, no? And inserting and
maintaining the data..? I can appreciate wanting to be standards
compliant but this specific use-case doesn't really provide much
justification for using this particular feature. Perhaps there are
better ones.

Thanks,

Stephen