To confirm that we have not lost any data, we can perform an as-of query. We want to query the state of the blueprint at time 3 (), before the non-destructive update. We from . We created a blueprint with key at time 3 () and edited it at time 4 (). We can query as-of any time in the range . We'll use the beginning of the range: . Plugging these values into the template, we GET The response we get from is nearly identical to the response we would have got from had we run the query at time 3: . This makes sense! There's a small difference in the data. Some of the values that used to be are now time 4: . This illustrates an important rule of temporal data. All writes into the data store are immutable and append-only, except for the value. 2001-01-03 GET /api/blueprint/{blueprintKey}?asOf={asOf} 6ed1f638-a63c-3a54-af67-ba494f27bff2 2001-01-03 2001-01-04 [2001-01-03, 2001-01-04) 2001-01-03 /api/blueprint/6ed1f638-a63c-3a54-af67-ba494f27bff2?asOf=2001-01-03T23:59:59Z { "key": "6ed1f638-a63c-3a54-af67-ba494f27bff2", "systemFrom": "2001-01-03T23:59:59Z", - "systemTo": null, + "systemTo": "2001-01-04T23:59:59Z", "version": { "number": 1, "systemFrom": "2001-01-03T23:59:59Z", - "systemTo": null, + "systemTo": "2001-01-04T23:59:59Z", "createdOn": "2001-01-03T23:59:59Z", "createdBy": { "userId": "User ID" }, "lastUpdatedBy": { "userId": "User ID" } }, "title": "Blueprint title", "voteSummary": { "numberOfUpvotes": 0, "systemFrom": "2001-01-03T23:59:59Z", "systemTo": null }, "blueprintString": { "sha": "cc341849b4086ce7b1893b366b0dc8e99ce4e595", "createdOn": "2001-01-02T23:59:59Z", "createdBy": { "userId": "User ID" } }, "imgurImage": { "imgurId": "Imgur ID 1", "imgurType": "image/png", "height": 300, "width": 300, "systemFrom": "2001-01-01T23:59:59Z", "systemTo": null }, "descriptionMarkdown": "Blueprint description markdown", "tags": [ { "tagCategory": "belt", "tagName": "balancer", "systemFrom": "2001-01-03T23:59:59Z", "systemTo": null, "tag": { "category": "belt", "name": "balancer", "ordinal": 1, "systemFrom": "2000-01-01T00:00:00Z", "systemTo": null } } ] } /api/blueprint/{blueprintKey}?asOf=2001-01-03T23:59:59Z /api/blueprint/{blueprintKey} 2001-01-03 systemTo null 2001-01-04 systemTo

Next we'll focus on the data store. In this example, we're using a relational database, but these concepts apply to any data store. The schema maps closely to the json examples above, so if you're comfortable with the data, feel free to skip ahead to the queries. BLUEPRINT after create systemFrom systemTo key blueprint title descriptionMarkdown imgurImageId blueprintStringSha 2001-01-03 23:59:59.0 9999-12-01 23:59:00.0 6ed1 Blueprint title Blueprint description markdown Imgur ID 1 cc34 BLUEPRINT after update systemFrom systemTo key blueprint title descriptionMarkdown imgurImageId blueprintStringSha `:icon: minus {stroke: 'red'}` 2001-01-03 23:59:59.0 `:icon: plus {stroke: 'green'}` 2001-01-04 23:59:59.0 6ed1 `:icon: minus {stroke: 'red'}` Blueprint title `:icon: minus {stroke: 'red'}` Blueprint description markdown Imgur ID 1 cc34 `:icon: plus {stroke: 'green'}` 2001-01-04 23:59:59.0 9999-12-01 23:59:00.0 6ed1 `:icon: plus {stroke: 'green'}` Edited blueprint title `:icon: plus {stroke: 'green'}` Edited Blueprint description markdown Imgur ID 1 cc34 BLUEPRINT_VERSION after create systemFrom systemTo key createdById lastUpdatedById number createdOn 2001-01-03 23:59:59.0 9999-12-01 23:59:00.0 6ed1 User ID User ID 1 2001-01-03 23:59:59.0 BLUEPRINT_VERSION after update systemFrom systemTo key createdById lastUpdatedById number createdOn `:icon: minus {stroke: 'red'}` 2001-01-03 23:59:59.0 `:icon: plus {stroke: 'green'}` 2001-01-04 23:59:59.0 6ed1 User ID User ID `:icon: minus {stroke: 'red'}` 1 2001-01-03 23:59:59.0 `:icon: plus {stroke: 'green'}` 2001-01-04 23:59:59.0 9999-12-01 23:59:00.0 6ed1 User ID User ID `:icon: plus {stroke: 'green'}` 2 2001-01-03 23:59:59.0 Temporal Schema patterns All tables have and columns. Old data is phased out by setting to now. New data is phased in by setting to now. The new row's and the old row's are set to the same value, forming a contiguous timeline. When several tables are edited within a transaction, the and values are set to the same value across all tables. Unchanged data is copied from the old row to the new row. For very wide columns that don't change frequently, it may be more efficient to split out a separate table. The value of the new row is set to to indicate that the row is still active. In json, we had used to represent the infinity date. systemFrom systemTo systemTo systemFrom systemFrom systemTo systemFrom systemTo systemTo 9999-12-01 23:59:00.00 null

As-of queries are implemented in SQL by adding temporal criteria to our clause. Now we can see why the infinity date is represented as . If we instead used we'd need to add additional criteria to our WHERE clauses. Joins that are one hop away from our main table are similar. Joins that are two hops away from our main table are more complicated. We'll see examples of these later. Temporal query patterns We perform asOf queries by adding to our clause. We add this exact came criteria to every query. We always all columns from the table. In the examples above we used . In production usage, it's common to list the columns explicitly. We never columns from two tables in the same query. Even in the upcoming examples of joins, we always from one table at a time. In the next section, we'll learn about adding versions and querying "as of" a version number. WHERE select * from BLUEPRINT t0 where t0.key = '6ed1f638-a63c-3a54-af67-ba494f27bff2' and t0.system_from <= '2001-01-03 23:59:59.000' and t0.system_to > '2001-01-03 23:59:59.000' 9999-12-01 23:59:00.00 null select * from BLUEPRINT_TAG t0 where t0.blueprint_key = '6ed1f638-a63c-3a54-af67-ba494f27bff2' and t0.system_from <= '2001-01-03 23:59:59.000' and t0.system_to > '2001-01-03 23:59:59.000' where system_from <= {asOf} and system_to > {asOf} WHERE SELECT SELECT * SELECT SELECT