How to find Google Spanner table creation date and time?

Question

I am trying to the table creation date and time in Google Spanner but I dont see any option exist. Even there is no information available in the information.schema

Answer 1

In Spanner, you cannot get a create_time for table and I believe that's due to to how Spanner table is designed and shared to multiple regions thus you cannot get a reliable create_time. A workaround would be; when you create a table you can record that time and save it to another table. This way you will have a register for all your table creation time. A code for this would be:

// Imports the Google Cloud client library
const {Spanner} = require('@google-cloud/spanner');
             
// Creates a client
const projectId = "PROJECTID"
const instanceId = "INSTANCEID";
const databaseId = "DATABASEID";
const spanner = new Spanner({projectId});
 
// Gets a reference to a Cloud Spanner instance and database
const instance = spanner.instance(instanceId);
const database = instance.database(databaseId);

// create the table TablesMeta that will holds your tables creation time
const tableMetaschema = [
'CREATE TABLE TablesMeta (' +
'  tableId INT64 NOT NULL,' +
'  TableName STRING(1024),'+
'  TableCreateTime DATE' +
') PRIMARY KEY(tableId)'
];


database.updateSchema(TablesMeta)

// create a new table and add its creation time to the TablesMeta table
const singerSchema = [
'CREATE TABLE Singers (' +
'  SingerId INT64 NOT NULL,' +
'  FirstName STRING(1024),' +
'  LastName STRING(1024),' +
'  SingerInfo BYTES(MAX),' +
') PRIMARY KEY(SingerId)'
];

database.updateSchema(singerSchema).then(() => {

database.runTransaction(async (err, transaction) => {
if (err) {
  console.error(err);
  return;
}
try {
  const [rowCount] = await transaction.runUpdate({
    sql:
      'INSERT TablesMeta (tableId, TableName, TableCreateTime) VALUES (1, @TableName, @TableCreateTime)',
    params: {
      TableName: 'Singers',
      TableCreateTime: Spanner.date(),
    },
  });

  console.log(
    `Successfully inserted ${rowCount} record into the TablesMeta table.`
  );

  await transaction.commit();
} catch (err) {
  console.error('ERROR:', err);
} finally {
  // Close the database when finished.
  database.close();
}
});

})