In this lesson, you will learn how to "time travel" through your database by retrieving the exact state of your data as it existed at a specific historical moment.
The Concept: Historical Snapshots
In standard SQL, a query typically returns the "current" state of your data. However, in many systems, rows are not simply deleted or overwritten; instead, they are tracked using ValidFrom and ValidTo timestamps. A "Point-in-Time" query allows you to reconstruct what a table looked like at a precise moment in the past by filtering these time ranges.
Think of it like a deck of cards where each card has a "start" and "end" date. If you want to know the state of the deck on January 1st, you only care about the cards that were "alive" (active) on that day.
How to Write a Point-in-Time Query
To perform this retrieval, you use a filter that checks if your target timestamp falls between the record’s start and end boundaries. If your target time is '2023-06-01', your query would look like this:
SELECT employee_name, salary
FROM employees
WHERE '2023-06-01' >= valid_from
AND '2023-06-01' < valid_to;
Why This Matters
If you are auditing changes—for example, trying to figure out exactly what a customer’s address was three years ago before they moved—you cannot rely on the current record. By applying these temporal filters, you can query historical versions of your data just as easily as you query current data.
Notice that we use < for the valid_to column. This is a best practice to ensure that if a record ended exactly at midnight on June 1st, it is considered "closed" and does not overlap with the next record starting at the exact same time. This simple range logic effectively turns your database into a living history book, allowing you to slice through time to extract the exact data state required for your reports or investigations.
Key Takeaway: A Point-in-Time query reconstructs historical data by filtering range-based timestamps to identify which records were active at your specific moment of interest.