Querying revision history

Amazon QLDB stores the complete history of every document in a table. You can see all three revisions of the vehicle registration document you previously inserted, updated, and deleted in Updating and deleting documents by querying the built-in history function.

History function

The history function in QLDB is a PartiQL extension that returns revisions from the system-defined view of your table. So, it includes both your data and the associated metadata in the same schema as the committed view.

Syntax

SELECT * FROM history( table_name | 'table_id' [, `start-time` [, `end-time` ] ] ) AS h
[ WHERE h.metadata.id = 'id' ]

Arguments

table_name | 'table_id'

Either the table name or table ID. A table name is a PartiQL identifier that you can denote with double quotation marks or no quotation marks. A table ID is a string literal that must be enclosed in single quotation marks. To learn more about using table IDs, see Querying the history of inactive tables.

`start-time`, `end-time`

(Optional) Specifies the time range during which any revisions were active. These parameters don't specify the time range during which revisions were committed to the journal in a transaction.

The start and end times are Ion timestamp literals that can be denoted with backticks (`...`). To learn more, see Querying Ion with PartiQL in Amazon QLDB.

These time parameters have the following behavior:

• The start-time and end-time are both inclusive. They must be in ISO 8601 date and time format and in Coordinated Universal Time (UTC).

• The start-time must be less than or equal to end-time and can be any arbitrary date in the past.

• The end-time must be less than or equal to the current UTC date and time.

• If you specify a start-time but not an end-time, your query defaults the end-time to the current date and time. If you specify neither, your query returns the entire history.

'id'

(Optional) The document ID for which you want to query the revision history, denoted by single quotation marks.

Tip

As a best practice, qualify a history query with both a date range (start-time and end-time) and a document ID (metadata.id). In QLDB, every SELECT query is processed in a transaction and is subject to a transaction timeout limit.

History queries don't use the indexes that you create on a table. QLDB history is indexed by document ID only, and you can't create additional history indexes at this time. History queries that include a start time and end time gain the benefit of date range qualification.

History query example

To query the vehicle registration document's history, use the id that you previously saved in Updating and deleting documents. For example, the following history query returns any revisions for document ID ADR2Ll1fGsU4Jr4EqTdnQF that were ever active between 2019-06-05T00:00:00Z and 2019-06-05T23:59:59Z.

Note

Remember that the start and end time parameters don't specify the time range when revisions were committed to the journal in a transaction. For example, if a revision was committed before 2019-06-05T00:00:00Z and remained active past that start time, this example query will return that revision in the results.

Be sure to replace the id, start time, and end time with your own values as appropriate.

SELECT * FROM history(VehicleRegistration, `2019-06-05T00:00:00Z`, `2019-06-05T23:59:59Z`) AS h
WHERE h.metadata.id = 'ADR2Ll1fGsU4Jr4EqTdnQF' --replace with your id

Your query results should look similar to the following.

{
    blockAddress:{
        strandId:"JdxjkR9bSYB5jMHWcI464T",
        sequenceNo:14
    },
    hash:{{B2wYwrHKOWsmIBmxUgPRrTx9lv36tMlod2xVvWNiTbo=}},
    data: {
        VIN: "1HVBBAANXWH544237",
        LicensePlateNumber: "LS477D",
        State: "WA",
        City: "Tacoma",
        PendingPenaltyTicketAmount: 42.20,
        ValidFromDate: 2011-10-26T,
        ValidToDate: 2023-09-25T,
        Owners: {
            PrimaryOwner: { PersonId: "KmA3XPKKFqYCP2zhR3d0Ho" },
            SecondaryOwners: []
        }
    },
    metadata:{
        id:"ADR2Ll1fGsU4Jr4EqTdnQF",
        version:0,
        txTime:2019-06-05T20:53:321d-3Z,
        txId:"HgXAkLjAtV0HQ4lNYdzX60"
    }
},
{
    blockAddress:{
        strandId:"JdxjkR9bSYB5jMHWcI464T",
        sequenceNo:17
    },
    hash:{{LGSFZ4iEYWZeMwmAqcxxNyT4wbCtuMOmFCj8pEd6Mp0=}},
    data: {
        VIN: "1HVBBAANXWH544237",
        LicensePlateNumber: "LS477D",
        State: "WA",
        PendingPenaltyTicketAmount: 42.20,
        ValidFromDate: 2011-10-26T,
        ValidToDate: 2023-09-25T,
        Owners: {
            PrimaryOwner: { PersonId: "KmA3XPKKFqYCP2zhR3d0Ho" },
            SecondaryOwners: []
        },
        City: "Bellevue"
    },
    metadata:{
        id:"ADR2Ll1fGsU4Jr4EqTdnQF",
        version:1,
        txTime:2019-06-05T21:01:442d-3Z,
        txId:"9cArhIQV5xf5Tf5vtsPwPq"
    }
},
{
    blockAddress:{
        strandId:"JdxjkR9bSYB5jMHWcI464T",
        sequenceNo:19
    },
    hash:{{7bm5DUwpqJFGrmZpb7h9wAxtvggYLPcXq+LAobi9fDg=}},
    metadata:{
        id:"ADR2Ll1fGsU4Jr4EqTdnQF",
        version:2,
        txTime:2019-06-05T21:03:76d-3Z,
        txId:"9GslbtDtpVHAgYghR5FXbZ"
    }
}

The output includes metadata attributes that provide details on when each item was modified, and by which transaction. From this data, you can see the following:

• The document is uniquely identified by its system-assigned id: ADR2Ll1fGsU4Jr4EqTdnQF. This is a UUID that is represented in a Base62-encoded string.

• An INSERT statement creates the initial revision of a document (version 0).

• Each subsequent update creates a new revision with the same document id and an incremented version number.

• The txId field indicates the transaction that committed each revision, and txTime shows when each was committed.

• A DELETE statement creates a new, but final revision of a document. This final revision has metadata only.

To learn how to permanently delete a revision, proceed to Redacting document revisions.