Querying Temporal Tables

During our initial introduction we briefly saw how to query temporal tables but there were a couple of specific bits which I wanted to cover around querying these tables. The first of these concerns the point in time we query against and the second is how we go about using that point in time to retrieve the data from the table. As with the previous posts we’ll start with the following setup:

CREATE TABLE dbo.TemporalSample
(
  ProductID INT PRIMARY KEY,
  ProductName VARCHAR(100),
  StockQuantity INT,
  StartDate DATETIME2 GENERATED ALWAYS AS ROW START HIDDEN,
  EndDate DATETIME2 GENERATED ALWAYS AS ROW END HIDDEN,
  PERIOD FOR SYSTEM_TIME (StartDate, EndDate)
 )
WITH (
	SYSTEM_VERSIONING = ON (
		HISTORY_TABLE = dbo.TemporalSampleHistory
	));

/* Insert some sample data */
INSERT INTO TemporalSample (
	ProductID,
	ProductName,
	StockQuantity
)
VALUES (1, 'Apples', 10),
	(2, 'Pears', 15),
	(3, 'Oranges', 8);

What time is it

When we’ve been looking at these tables you may have noticed that the timestamps which are present in the periods don’t align to your time zone. This is due to the changes being recorded in UTC. As a result, if you want to be targeting a very specific point in time then you may need to do some work with the dates and times which you pass into the temporal query.

Ideally you’d have your application and databases store your timestamps in UTC to remove ambiguity in your data. If that isn’t the case then an alternative approach would be to take your local timestamps and convert those to UTC using the built-in SQL functions.

Below is an example where I look at the data in the table as it would have been yesterday afternoon (2nd May) with the conversion from my time zone (BST) to UTC to align with the history:

/* Derive the correct UTC time */
DECLARE @AsOfTime DATETIME2 = '2022-05-02 15:00';
DECLARE @AsOf DATETIMEOFFSET
	= @AsOfTime AT TIME ZONE 'GMT Standard Time';
SET @AsOf = @AsOf AT TIME ZONE 'UTC'

/* Query the table at the correct time */
SELECT *
FROM TemporalSample
	FOR SYSTEM_TIME AS OF @AsOf;

If you have a time zone closer to UTC and data which doesn’t move too fast then you might be able to get away without this conversion but it might be needed if you move further afield or into the cloud so bear that in mind.

What can we query

With that out of the way regarding the timestamps, I don’t want to be mudding the water with these examples and specific points in time so I’ll be tracking the periods in the query with the GETUTCDATE() function.

There are multiple ways in which you can query temporal tables depending on exactly what you’re looking for out of them. One of these we used above was the AS OF expression to get the records as they were at a specific point in time. Its also possible to request a range of records covering a time period or even all records within both the regular and historical data.

As detailed within the documentation, below are the selection of ways in which we can query the temporal table which are summarised as follows:

• AS OF to retrieve the contents of the table at a specific point in time
• FROM ... TO which will retrieve all records which were active during the period however neither boundary is inclusive
• BETWEEN ... AND which also retrieves the records in a range as above – but in this instance the upper bound is inclusive
• CONTAINED IN to retrieve all records which were both created and closed within the specified time period with inclusive upper and lower bounds
• ALL retrieves all records within the table and the history

We’ve already seen how the AS OF expression operates with our data so lets focus on a single product (product ID 1 – apples) and see how the data is returned when using the other expressions after making some changes to the values in the table. I’ve added some delays into the script as running this back to back can get the timestamps a little tangled. Firstly we’ll make a couple of changes:

WAITFOR DELAY '00:00:01'
DECLARE @OriginalData DATETIME2 = GETUTCDATE();
WAITFOR DELAY '00:00:01'

/* Make some changes */
UPDATE TemporalSample
SET StockQuantity += 1;

WAITFOR DELAY '00:00:01'
DECLARE @FirstChange DATETIME2 = GETUTCDATE();
WAITFOR DELAY '00:00:01'

/* Make some more changes */
UPDATE TemporalSample
SET StockQuantity += 1;

WAITFOR DELAY '00:00:01'
DECLARE @SecondChange DATETIME2 = GETUTCDATE();

Now that we’ve got some history in our table we’ll want another section to follow up and use the different expressions available to us. The queries and the results will look something like the following:

SELECT *, 'FROM ... TO' [Expression]
FROM TemporalSample
	FOR SYSTEM_TIME
	FROM @OriginalData TO @FirstChange
WHERE ProductID = 1;

SELECT *, 'BETWEEN ... AND' [Expression]
FROM TemporalSample
	FOR SYSTEM_TIME
	BETWEEN @FirstChange AND  @SecondChange
WHERE ProductID = 1;

SELECT *, 'CONTAINED IN' [Expression]
FROM TemporalSample
	FOR SYSTEM_TIME
	CONTAINED IN (@OriginalData, @SecondChange)
WHERE ProductID = 1;

SELECT *, 'ALL' [Expression]
FROM TemporalSample
	FOR SYSTEM_TIME ALL
WHERE ProductID = 1;

You’ll see the FROM ... TO and BETWEEN ... AND results would be similar to what we’d expect to see when using standard range operators within our WHERE clauses. Albeit these examples don’t provide a clear of the boundary checks within our sample data as I’m looking to show repeatable examples without hardcoded timestamps in.

We then move onto the CONTAINED IN which only looks at the records which were both opened and closed within the time window. As a result the entry when the quantity was set as 10 is excluded as this was opened prior to our period, and the live entry with the volume at 12 hasn’t yet closed so that’s outside of the period. This leaves us with the single entry falling inside our range.

Finally we can see that the ALL expression is returning all of the records for the product regardless of when they were created so we see all 3 entries.

Note that we can see in these examples that the results returned aren’t necessarily within chronological order. It appears that matching results from the live table are returned first followed by the matching entries from the history table, in some sort of a UNION‘d fashion. If the results are needed in a specific order you’ll need to specify this in an ORDER BY expression.

Wrap up

In this post we’ve looked at our options for querying the records from a temporal table and we also touched on how we need to ensure we’re using UTC timestamps when looking at our time periods to get accurate results returned.

Next up I’d like to touch on modifying the temporal tables. Remember that we’ve seen this isn’t just one table that we’re dealing with but two of them. We’ll look into that a little more soon.