How to retrieve minimum transaction/update time for all table on QLDB

Question

In the doc, we can retrieve history by date, but how to retrieve minimum date for all tables on that ledger?

I tried this:

SELECT MIN(h.metadata.txTime) FROM history(Clients) AS h

but it gives error: <UNKNOWN>: at line <UNKNOWN>, column <UNKNOWN>: <UNKNOWN>; Expected number: 2021-08-04T12:24:07.921Z it seems MIN function only can receives number but there's no way to convert timestamp to epoch/unix timestamp?

also

SELECT MIN(TO_STRING(h.metadata.txTime,'yyyyMMddHHmmss.SSS')) FROM history(Clients) AS h

<UNKNOWN>: at line <UNKNOWN>, column <UNKNOWN>: <UNKNOWN>; Expected number: "20210804122407.921"

(tried postgresql cast style ::float also didn't work)

EDIT: nevermind, there's CAST

SELECT MIN(CAST(TO_STRING(h.metadata.txTime,'yyyyMMddHHmmss.SSS') AS float)) FROM history(Clients) AS h

this one works, but hopefully the float is accurate enough to represent timestamp.

back to the question, how to retrieve from all table?

Table1 | minTxTime
Table2 | minTxTime
Table3 | minTxTime

Arahant · Answer 1 · 2022-02-10T21:22:47.573

There are two steps to this:

To get the list of tables in your ledger.

select tableId from information_schema.user_tables

will return a result set of tableIds of all your tables in your ledger, both active and inactive. If you only want active tables, you can add a where clause to the above query:

select tableId from information_schema.user_tables tables where tables.status = 'ACTIVE'

To get your min txTime for each of the table You can then use the above result and feed it into your other history query.

You can do it programmatically through a driver. Here's the pseudocode:

result = transaction {
  tables = execute("select tableId from information_schema.user_tables u where u.status = 'ACTIVE'")
  tables.map( t ->
    execute("SELECT MIN(CAST(TO_STRING(h.metadata.txTime,'yyyyMMddHHmmss.SSS') AS float)) FROM history(t)")
  )
}

EDIT: Here's a functioning NodeJS code

import { QldbDriver } from 'amazon-qldb-driver-nodejs';

const ledgerName = "my-test-ledger-1"
const serviceConfigOptions = {region: "us-east-2"};
const qldbDriver = new QldbDriver(ledgerName, serviceConfigOptions);

async function getTables() {
    return new Promise((resolve, reject) => {
        qldbDriver.getTableNames()
        .then((res) => {
            resolve(res);
        })
        .catch((err) => {
            console.log(err);
            reject(err);
        })
    })
}

async function minTxTime(table) {
    return new Promise((resolve, reject) => {
        qldbDriver.executeLambda(async (txn) => {
            const results = (await txn.execute("SELECT MIN(CAST(TO_STRING(h.metadata.txTime,'yyyyMMddHHmmss.SSS') AS float)) as minTxTime FROM history("+table+") as h")).getResultList();
            resolve(results);
        })
        .catch((err) => {
            console.log(err);
            reject(err);
        })
    });
}

async function main() {
    try {
        var tables = await getTables();
        for(var t in tables) {
            var res = await minTxTime(tables[t]);
            console.log(res);
        }
    }
    catch (error) {
        console.error(error);
    }
}

main()

How to retrieve minimum transaction/update time for all table on QLDB

1 Answers1