Free tools Windows power users keep installed
One-click scans. No signup required.
How do you model rates that change over time without rewriting history? Keep a stable identity for the rate schedule and store each rate as a separate version with an explicit business-effective period. Close the prior version’s interval when a new rate takes effect; do not overwrite the old rate value. If you also need to know what the database recorded at an earlier time, preserve a second, system-time history.
Start by separating the two questions time can answer
An effective-dated rate history answers which rate applies to a business date. System-time history answers what the database held at a past point. These clocks are different: a rate may apply from a future date, and a correction entered today may change what you now believe applied in the past.
As an Amazon Associate I earn from qualifying purchases.
- Business-effective time, also called valid or application time, records when a rate is intended to apply. It may cover past, current, or future dates.
- System time, also called transaction or recording time, records when a particular version was stored in the database.
- Bitemporal history records both. It can answer both “What rate do we now believe applied on March 1?” and “What did the database believe on March 5 about the rate for March 1?” The OASIS temporal-data extension frames the distinction as when something happened or will happen, and when it was learned; SAP describes application-time periods and their combination with system versioning in its HANA Cloud documentation.
A system-versioned table alone is not an effective-dated schedule. SQL Server’s system-managed period columns capture database row-version timing, while SAP application-time periods represent business-defined periods. SAP documents combining application time with system versioning to create bitemporal tables. See Microsoft’s SQL Server temporal-table overview and the SAP application-time reference.
Recommended Free Tools
Represent each rate as a dated version
A practical rate-version record includes a stable schedule identifier, the rate value and unit or currency, and the interval when that version applies. Keep versions distinct rather than replacing the value in place.
#1 Best Overall
rate_id: identifies the rate schedule or other stable business identity.rate_valueandcurrencyor another unit: state the amount and how to interpret it.effective_fromandeffective_to: define the business-effective interval.- A separate version key is useful when posted transactions must continue pointing to the exact version used at posting time.
When a new rate takes effect, close the old interval and add a new version. For example, if a rate changes on February 1, the previous version ends on that date and the new one begins on that date. A later correction may require changing the effective-date schedule; if you must retain what the database believed before the correction, capture system-time history as well.
Use half-open intervals and define the boundary rules
Use a start-inclusive, end-exclusive convention: [effective_from, effective_to). A row from January 1 up to February 1 applies through January dates and not at the instant February begins. The next row can start exactly at the previous row’s end, with no overlap at the boundary. IBM’s temporal data modeling reference describes period beginnings as inclusive and ends as exclusive.
A conceptual as-of lookup is:
SELECT rate_value
FROM rate_version
WHERE rate_id = :rate_id
AND effective_from <= :as_of
AND :as_of < effective_to;This is conceptual SQL, not a tested query for a particular database engine. If an active interval has no planned end, choose one consistent representation, such as a nullable effective_to or a documented high-date sentinel, and make the lookup and overlap rules match it.
The database cannot infer the meaning of a missing or conflicting interval. Decide explicitly:
- Whether gaps mean there is no applicable rate, the prior rate carries forward, or the data is invalid.
- Whether overlapping effective periods are forbidden for the same rate identity.
- How open-ended current intervals are represented and constrained.
Choose the history model that fits the question
| Need | Model | What it preserves |
|---|---|---|
| Find the rate that applies on a business date, including a scheduled future date | Effective-dated versions | Business-effective periods and prior rate versions |
| Reconstruct what the database contained before an edit | System-time history or an append-only audit log | Recorded row versions over database time |
| Correct past effective dates and still answer what was believed before the correction | Bitemporal history | Both business-effective periods and database-recorded periods |
| Keep posted transactions attached to the exact rate version used | Version records with a stable version key referenced by the transaction | The specific version selected at posting |
This follows the same broad distinction as data warehousing’s Type 1 and Type 2 changes: Type 1 overwrites an attribute, while Type 2 keeps a separate row per version, usually with validity dates. Microsoft discusses these patterns alongside temporal-table scenarios in its usage-scenarios documentation.
Independent reader supportYour contribution helps us test, update, and keep practical guides available for everyone.What database temporal features do—and do not—provide
SQL Server system-versioned temporal tables
Microsoft documents SQL Server temporal tables as a current table paired with a history table and system-managed period columns. Updates and deletes preserve prior row versions, and FOR SYSTEM_TIME AS OF can reconstruct a past database state. This is useful for audit and point-in-time analysis, but it tracks system time, not automatically the business-effective time of a rate. Microsoft cautions that transaction-time periods may not align with slowly changing-dimension business logic when incoming data is significantly delayed. It also notes that a data modification can produce a history row even when no column values changed, so update patterns affect the audit trail. Consult the version-specific temporal-table overview and usage scenarios.
SAP HANA Cloud application-time periods
SAP HANA Cloud application-time period tables manage business periods separately from system timestamps and support periods in the past, present, or future. SAP says they can be combined with system-versioned tables for bitemporal history. Review the SAP application-time period documentation for the target version’s syntax and behavior.
Windows Errors? Fix Them Before They Spread
Repair common Windows errors and clear accumulated junk for a smoother, more stable PC - no reinstall needed.Free scan · no reinstallOutdated Drivers Are Slowing You Down
One free scan finds every outdated or missing driver and matches the right update for your exact hardware.Free scan · exact hardware matchQuick Recap
Implementation checks before relying on the history
- Use effective dates whenever future scheduling or business-date lookup matters; automatic system history does not supply that business calendar.
- Prevent overlapping periods for the same rate identity, and document the intended behavior for gaps.
- Test lookups at the exact start and end boundaries under the chosen inclusive/exclusive convention.
- Decide whether corrections to past-effective dates must preserve the earlier recorded belief. If so, use bitemporal history or an equivalent audit design.
- Give rate versions their own stable key when other records must remain tied to the precise version selected.
- Evaluate interval enforcement, as-of query ergonomics, correction behavior, and history storage and retention costs for the actual database and workload; there is no universal performance or storage advantage established here.
Product prices and availability are accurate as of the date/time indicated and are subject to change. Any price and availability information displayed on Amazon at the time of purchase will apply.
