top of page

Won't give us the time of day 'ta

In this post, I will describe the temporal mechanism that was developed in Postgres as a part of an open-source enterprise system that has inherent data exchange capabilities.

Temporal data refers to master data that changes over time. A practical example is payroll parameters, where an employee's pay rate may change over different periods. Storing this data as temporal, enables processing of prior period adjustments. For instance, if we discover that an employee worked more hours in the previous month but had a pay rate adjustment in the current month, we need to modify the hours for the previous month and reprocess it based on the pay rate from last month.

To facilitate the management of temporal data in a consistent manner, the platform includes a procedure that will automatically determine whether to update an existing segment, insert a new segment, or merge two segments together based on the temporal update it is provided.

When an enterprise system has temporal data setup with this mechanism, we gain significant abilities for reporting and AI as a result of being able to show how the entire database changed over time. Furthermore, users can simply change a query date and the system will return all data to them as if it were that date.

The temporal functionality works hand in hand with the change history functionality. Whenever temporal data changes, the change history function captures the updates and presents them to the user in a meaningful way. Additionally, the temporal functionality seamlessly integrates with the data exchange capability, ensuring that temporal updates are appropriately disseminated to subscribed systems.

Please check out and “020.060 Temporal” where I discuss the rationale for temporal, a procedure that will automatically normalizes temporal data and why it is important to understand temporal resolutions. You can also view the source code at

I encourage you to share your thoughts, questions, and likes.

Thank you for your attention.

2 views0 comments

Recent Posts

See All

Recursive selects to rollup financial data

In this post I am going to describe the financial roll-up procedure that was developed using Postgres that is a part of an enterprise system platform that has an inherent capability to exchange data b

Need help creating an application framework

In this post, I have a #QuestionForGroup about creating an application framework to connect to the open-source platform I created using Postgres. The platform allows us to create enterprise systems th

Automatically consolidate data into a Data Warehouse

In this post I am going to describe the data warehouse generate procedure (DW procedure) that was developed using Postgres that is a part of an enterprise system platform that has an inherent capabili


bottom of page