Commit timestamps

This topic describes how to write a commit timestamp for each insert and update operation that you perform with Cloud Spanner. To use this feature, set the allow_commit_timestamp option on a TIMESTAMP column, then write the timestamp as part of each transaction.

Overview

The commit timestamp, based on TrueTime technology, is the time when a transaction is committed in the database. The allow_commit_timestamp column option allows you to atomically store the commit timestamp into a column. Using the commit timestamps stored in tables, you can determine the exact ordering of mutations and build features like changelogs.

To insert commit timestamps in your database, complete the following steps:

  1. Create a TIMESTAMP column with the column option allow_commit_timestamp set to true in the schema definition.

  2. If you are performing inserts or updates with DML, use the PENDING_COMMIT_TIMESTAMP function to write the commit timestamp.

    If you are performing inserts or updates with mutations, use the placeholder string spanner.commit_timestamp() (or the client library constant) on insertions or updates to your commit timestamp column.

When Cloud Spanner commits the transaction, the commit timestamp is written to the LastUpdateTime column. You could then use LastUpdateTime to create a history of updates to the Performances table.

Commit timestamp values are not guaranteed to be unique. Transactions that write to non-overlapping sets of fields might have the same timestamp. Transactions that write to overlapping sets of fields have unique timestamps.

Cloud Spanner commit timestamps have microsecond granularity, and they are converted to nanoseconds when stored in TIMESTAMP columns.

Creating and deleting a commit timestamp column

Use the allow_commit_timestamp option to add and remove support for commit timestamps:

  • To create a new column, in a new or existing table, that supports commit timestamps.
  • To alter an existing timestamp column to support commit timestamps.
  • To remove commit timestamp support from a column.

Keys and indexes

You can use a commit timestamp column as a primary key column or as a non-key column. Primary keys can be defined as ASC or DESC.

  • ASC (default) - Ascending keys are ideal for answering queries from a specific time forward.
  • DESC - Descending keys keep the latest rows at the top of the table. They provide quick access to the latest records.

The allow_commit_timestamp option must be consistent across the primary keys of parent and child tables. If the option is not consistent across primary keys, Cloud Spanner returns an error. The only time the option can be inconsistent is when you are creating or updating the schema.

Do not use a commit timestamp column as the first part of the primary key of a table or the first part of the primary key of a secondary index. Using a commit timestamp column as the first part of a primary key creates hotspots and reduces data performance, but performance issues may occur even with low write rates. There is no performance overhead to enable the commit timestamps on non-key columns that are not indexed.

Creating a commit timestamp column

The following example creates a table with a column that supports commit timestamps.

C#

// Initialize request connection string for database creation.
string connectionString =
    $"Data Source=projects/{projectId}/instances/{instanceId}"
    + $"/databases/{databaseId}";
using (var connection = new SpannerConnection(connectionString))
{
    // Define create table statement for table with
    // commit timestamp column.
    string createTableStatement =
    @"CREATE TABLE Performances (
        SingerId       INT64 NOT NULL,
        VenueId        INT64 NOT NULL,
        EventDate      Date,
        Revenue        INT64,
        LastUpdateTime TIMESTAMP NOT NULL OPTIONS (allow_commit_timestamp=true)
    ) PRIMARY KEY (SingerId, VenueId, EventDate),
        INTERLEAVE IN PARENT Singers ON DELETE CASCADE";
    // Make the request.
    var cmd = connection.CreateDdlCommand(createTableStatement);
    await cmd.ExecuteNonQueryAsync();
}

Go

func createTableWithTimestamp(ctx context.Context, w io.Writer, adminClient *database.DatabaseAdminClient, database string) error {
	op, err := adminClient.UpdateDatabaseDdl(ctx, &adminpb.UpdateDatabaseDdlRequest{
		Database: database,
		Statements: []string{
			`CREATE TABLE Performances (
				SingerId        INT64 NOT NULL,
				VenueId         INT64 NOT NULL,
				EventDate       Date,
				Revenue         INT64,
				LastUpdateTime  TIMESTAMP NOT NULL OPTIONS (allow_commit_timestamp=true)
			) PRIMARY KEY (SingerId, VenueId, EventDate),
			INTERLEAVE IN PARENT Singers ON DELETE CASCADE`,
		},
	})
	if err != nil {
		return err
	}
	if err := op.Wait(ctx); err != nil {
		return err
	}
	fmt.Fprintf(w, "Created Performances table in database [%s]\n", database)
	return nil
}

Java

static void createTableWithTimestamp(DatabaseAdminClient dbAdminClient, DatabaseId id) {
  OperationFuture<Void, UpdateDatabaseDdlMetadata> op =
      dbAdminClient.updateDatabaseDdl(
          id.getInstanceId().getInstance(),
          id.getDatabase(),
          Arrays.asList(
              "CREATE TABLE Performances (\n"
                  + "  SingerId     INT64 NOT NULL,\n"
                  + "  VenueId      INT64 NOT NULL,\n"
                  + "  EventDate    Date,\n"
                  + "  Revenue      INT64, \n"
                  + "  LastUpdateTime TIMESTAMP NOT NULL OPTIONS (allow_commit_timestamp=true)\n"
                  + ") PRIMARY KEY (SingerId, VenueId, EventDate),\n"
                  + "  INTERLEAVE IN PARENT Singers ON DELETE CASCADE"),
          null);
  try {
    // Initiate the request which returns an OperationFuture.
    op.get();
    System.out.println("Created Performances table in database: [" + id + "]");
  } catch (ExecutionException e) {
    // If the operation failed during execution, expose the cause.
    throw (SpannerException) e.getCause();
  } catch (InterruptedException e) {
    // Throw when a thread is waiting, sleeping, or otherwise occupied,
    // and the thread is interrupted, either before or during the activity.
    throw SpannerExceptionFactory.propagateInterrupt(e);
  }
}

Node.js

  // Imports the Google Cloud client library
  const {Spanner} = require('@google-cloud/spanner');

  /**
   * TODO(developer): Uncomment the following lines before running the sample.
   */
  // const projectId = 'my-project-id';
  // const instanceId = 'my-instance';
  // const databaseId = 'my-database';

  // Creates a client
  const spanner = new Spanner({
    projectId: projectId,
  });

  // Gets a reference to a Cloud Spanner instance
  const instance = spanner.instance(instanceId);
  const database = instance.database(databaseId);

  // Note: Cloud Spanner interprets Node.js numbers as FLOAT64s, so they
  // must be converted to strings before being inserted as INT64s
  const request = [
    `CREATE TABLE Performances (
        SingerId    INT64 NOT NULL,
        VenueId     INT64 NOT NULL,
        EventDate   DATE,
        Revenue     INT64,
        LastUpdateTime TIMESTAMP NOT NULL OPTIONS (allow_commit_timestamp=true)
      ) PRIMARY KEY (SingerId, VenueId, EventDate),
      INTERLEAVE IN PARENT Singers ON DELETE CASCADE`,
  ];

  // Creates a table in an existing database
  const [operation] = await database.updateSchema(request);

  console.log(`Waiting for operation on ${databaseId} to complete...`);

  await operation.promise();

  console.log(`Created table Performances in database ${databaseId}.`);

PHP

use Google\Cloud\Spanner\SpannerClient;

/**
 * Creates a table with a commit timestamp column.
 * Example:
 * ```
 * create_table_with_timestamp_column($instanceId, $databaseId);
 * ```
 *
 * @param string $instanceId The Spanner instance ID.
 * @param string $databaseId The Spanner database ID.
 */
function create_table_with_timestamp_column($instanceId, $databaseId)
{
    $spanner = new SpannerClient();
    $instance = $spanner->instance($instanceId);
    $database = $instance->database($databaseId);

    $operation = $database->updateDdl(
        "CREATE TABLE Performances (
    		SingerId	INT64 NOT NULL,
    		VenueId		INT64 NOT NULL,
    		EventDate	DATE,
    		Revenue		INT64,
    		LastUpdateTime	TIMESTAMP NOT NULL OPTIONS (allow_commit_timestamp=true)
    	) PRIMARY KEY (SingerId, VenueId, EventDate),
    	INTERLEAVE IN PARENT Singers on DELETE CASCADE"
    );

    print('Waiting for operation to complete...' . PHP_EOL);
    $operation->pollUntilComplete();

    printf('Created Performances table in database %s on instance %s' . PHP_EOL,
        $databaseId, $instanceId);
}

Python

def create_table_with_timestamp(instance_id, database_id):
    """Creates a table with a COMMIT_TIMESTAMP column."""

    spanner_client = spanner.Client()
    instance = spanner_client.instance(instance_id)
    database = instance.database(database_id)

    operation = database.update_ddl([
        """CREATE TABLE Performances (
            SingerId     INT64 NOT NULL,
            VenueId      INT64 NOT NULL,
            EventDate    Date,
            Revenue      INT64,
            LastUpdateTime TIMESTAMP NOT NULL
            OPTIONS(allow_commit_timestamp=true)
        ) PRIMARY KEY (SingerId, VenueId, EventDate),
          INTERLEAVE IN PARENT Singers ON DELETE CASCADE"""
    ])

    print('Waiting for operation to complete...')
    operation.result()

    print('Created Performances table on database {} on instance {}'.format(
        database_id, instance_id))

Ruby

# project_id  = "Your Google Cloud project ID"
# instance_id = "Your Spanner instance ID"
# database_id = "Your Spanner database ID"

require "google/cloud/spanner"

spanner = Google::Cloud::Spanner.new project: project_id
client  = spanner.database instance_id, database_id

job = client.update statements: [
  "CREATE TABLE Performances (
    SingerId     INT64 NOT NULL,
    VenueId      INT64 NOT NULL,
    EventDate    Date,
    Revenue      INT64,
    LastUpdateTime TIMESTAMP NOT NULL OPTIONS (allow_commit_timestamp=true)
   ) PRIMARY KEY (SingerId, VenueId, EventDate),
   INTERLEAVE IN PARENT Singers ON DELETE CASCADE"
]

puts "Waiting for update database operation to complete"

job.wait_until_done!

puts "Created table Performances in #{database_id}"

Adding the option changes the timestamp column as follows:

  • You can use the spanner.commit_timestamp() placeholder string (or a constant provided by the client library) for inserts and updates.
  • The column can only contain values in the past. For more information, see Providing your own value for the timestamp.

The option allow_commit_timestamp is case sensitive.

Adding a commit timestamp column to an existing table

To add a commit timestamp column to an existing table, use the ALTER TABLE statement:

ALTER TABLE table_name ADD COLUMN column_name TIMESTAMP
    NOT NULL OPTIONS (allow_commit_timestamp=true)

Converting a timestamp column to a commit timestamp column

You can convert an existing timestamp column into a commit timestamp column, but doing so requires Cloud Spanner to validate that the existing timestamp values are in the past. For example:

ALTER TABLE Albums ALTER COLUMN LastUpdateTime
    SET OPTIONS (allow_commit_timestamp=true)

You cannot change the data type or NULL annotation of a column in an ALTER TABLE statement that includes SET OPTIONS. For details, see Data Definition Language.

Removing the commit timestamp option

If you want to remove commit timestamp support from a column, use the option allow_commit_timestamp=null in an ALTER TABLE statement. The commit timestamp behavior is removed, but the column is still a timestamp. Changing the option does not alter any other characteristics of the column, such as type or nullability (NOT NULL). For example:

ALTER TABLE Albums ALTER COLUMN LastUpdateTime
    SET OPTIONS (allow_commit_timestamp=null)

Writing a commit timestamp using a DML statement

You use the PENDING_COMMIT_TIMESTAMP function to write the commit timestamp in a DML statement. Cloud Spanner selects the commit timestamp when the transaction commits.

The following DML statement updates the LastUpdated column in the Singers table with the commit timestamp:

UPDATE Singers SET LastUpdated = PENDING_COMMIT_TIMESTAMP() WHERE SingerId = 1

Inserting a row using a mutation

When inserting a row, Cloud Spanner writes the commit timestamp value only if you include the column in the column list and pass the spanner.commit_timestamp() placeholder string (or client library constant) as its value. For example:

C#

string connectionString =
$"Data Source=projects/{projectId}/instances/{instanceId}"
+ $"/databases/{databaseId}";
List<Performance> performances = new List<Performance> {
    new Performance {singerId = 1, venueId = 4, eventDate = DateTime.Parse("2017-10-05"),
        revenue = 11000},
    new Performance {singerId = 1, venueId = 19, eventDate = DateTime.Parse("2017-11-02"),
        revenue = 15000},
    new Performance {singerId = 2, venueId = 42, eventDate = DateTime.Parse("2017-12-23"),
        revenue = 7000},
};
// Create connection to Cloud Spanner.
using (var connection = new SpannerConnection(connectionString))
{
    await connection.OpenAsync();
    // Insert rows into the Performances table.
    var cmd = connection.CreateInsertCommand("Performances",
        new SpannerParameterCollection {
            {"SingerId", SpannerDbType.Int64},
            {"VenueId", SpannerDbType.Int64},
            {"EventDate", SpannerDbType.Date},
            {"Revenue", SpannerDbType.Int64},
            {"LastUpdateTime", SpannerDbType.Timestamp},
    });
    await Task.WhenAll(performances.Select(performance =>
    {
        cmd.Parameters["SingerId"].Value = performance.singerId;
        cmd.Parameters["VenueId"].Value = performance.venueId;
        cmd.Parameters["EventDate"].Value = performance.eventDate;
        cmd.Parameters["Revenue"].Value = performance.revenue;
        cmd.Parameters["LastUpdateTime"].Value = SpannerParameter.CommitTimestamp;
        return cmd.ExecuteNonQueryAsync();
    }));
    Console.WriteLine("Inserted data.");
}

Go

func writeWithTimestamp(ctx context.Context, w io.Writer, client *spanner.Client) error {
	performanceColumns := []string{"SingerId", "VenueId", "EventDate", "Revenue", "LastUpdateTime"}
	m := []*spanner.Mutation{
		spanner.InsertOrUpdate("Performances", performanceColumns, []interface{}{1, 4, "2017-10-05", 11000, spanner.CommitTimestamp}),
		spanner.InsertOrUpdate("Performances", performanceColumns, []interface{}{1, 19, "2017-11-02", 15000, spanner.CommitTimestamp}),
		spanner.InsertOrUpdate("Performances", performanceColumns, []interface{}{2, 42, "2017-12-23", 7000, spanner.CommitTimestamp}),
	}
	_, err := client.Apply(ctx, m)
	return err
}

Java

static final List<Performance> PERFORMANCES =
    Arrays.asList(
        new Performance(1, 4, "2017-10-05", 11000),
        new Performance(1, 19, "2017-11-02", 15000),
        new Performance(2, 42, "2017-12-23", 7000));
static void writeExampleDataWithTimestamp(DatabaseClient dbClient) {
  List<Mutation> mutations = new ArrayList<>();
  for (Performance performance : PERFORMANCES) {
    mutations.add(
        Mutation.newInsertBuilder("Performances")
            .set("SingerId")
            .to(performance.singerId)
            .set("VenueId")
            .to(performance.venueId)
            .set("EventDate")
            .to(performance.eventDate)
            .set("Revenue")
            .to(performance.revenue)
            .set("LastUpdateTime")
            .to(Value.COMMIT_TIMESTAMP)
            .build());
  }
  dbClient.write(mutations);
}

Node.js

  // Imports the Google Cloud client library
  const {Spanner} = require('@google-cloud/spanner');

  /**
   * TODO(developer): Uncomment the following lines before running the sample.
   */
  // const projectId = 'my-project-id';
  // const instanceId = 'my-instance';
  // const databaseId = 'my-database';

  // Creates a client
  const spanner = new Spanner({
    projectId: projectId,
  });

  // Gets a reference to a Cloud Spanner instance and database
  const instance = spanner.instance(instanceId);
  const database = instance.database(databaseId);

  // Instantiate Spanner table objects
  const performancesTable = database.table('Performances');

  const data = [
    {
      SingerId: '1',
      VenueId: '4',
      EventDate: '2017-10-05',
      Revenue: '11000',
      LastUpdateTime: 'spanner.commit_timestamp()',
    },
    {
      SingerId: '1',
      VenueId: '19',
      EventDate: '2017-11-02',
      Revenue: '15000',
      LastUpdateTime: 'spanner.commit_timestamp()',
    },
    {
      SingerId: '2',
      VenueId: '42',
      EventDate: '2017-12-23',
      Revenue: '7000',
      LastUpdateTime: 'spanner.commit_timestamp()',
    },
  ];

  // Inserts rows into the Singers table
  // Note: Cloud Spanner interprets Node.js numbers as FLOAT64s, so
  // they must be converted to strings before being inserted as INT64s
  try {
    await performancesTable.insert(data);
    console.log('Inserted data.');
  } catch (err) {
    console.error('ERROR:', err);
  } finally {
    // Close the database when finished
    database.close();
  }

PHP

use Google\Cloud\Spanner\SpannerClient;

/**
 * Inserts sample data into a table with a commit timestamp column.
 *
 * The database and table must already exist and can be created using
 * `create_table_with_timestamp_column`.
 * Example:
 * ```
 * insert_data_with_timestamp_column($instanceId, $databaseId);
 * ```
 *
 * @param string $instanceId The Spanner instance ID.
 * @param string $databaseId The Spanner database ID.
 */
function insert_data_with_timestamp_column($instanceId, $databaseId)
{
    $spanner = new SpannerClient();
    $instance = $spanner->instance($instanceId);
    $database = $instance->database($databaseId);

    $operation = $database->transaction(['singleUse' => true])
        ->insertBatch('Performances', [
            ['SingerId' => 1, 'VenueId' => 4, 'EventDate' => '2017-10-05', 'Revenue' => 11000, 'LastUpdateTime' => $spanner->commitTimestamp()],
            ['SingerId' => 1, 'VenueId' => 19, 'EventDate' => '2017-11-02', 'Revenue' => 15000, 'LastUpdateTime' => $spanner->commitTimestamp()],
            ['SingerId' => 2, 'VenueId' => 42, 'EventDate' => '2017-12-23', 'Revenue' => 7000, 'LastUpdateTime' => $spanner->commitTimestamp()],
        ])
        ->commit();

    print('Inserted data.' . PHP_EOL);
}

Python

def insert_data_with_timestamp(instance_id, database_id):
    """Inserts data with a COMMIT_TIMESTAMP field into a table. """

    spanner_client = spanner.Client()
    instance = spanner_client.instance(instance_id)

    database = instance.database(database_id)

    with database.batch() as batch:
        batch.insert(
            table='Performances',
            columns=(
                'SingerId', 'VenueId', 'EventDate',
                'Revenue', 'LastUpdateTime',),
            values=[
                (1, 4, "2017-10-05", 11000, spanner.COMMIT_TIMESTAMP),
                (1, 19, "2017-11-02", 15000, spanner.COMMIT_TIMESTAMP),
                (2, 42, "2017-12-23", 7000, spanner.COMMIT_TIMESTAMP)])

    print('Inserted data.')

Ruby

# project_id  = "Your Google Cloud project ID"
# instance_id = "Your Spanner instance ID"
# database_id = "Your Spanner database ID"

require "google/cloud/spanner"

spanner = Google::Cloud::Spanner.new project: project_id
client  = spanner.client instance_id, database_id

# Get commit_timestamp
commit_timestamp = client.commit_timestamp

client.commit do |c|
  c.insert "Performances", [
    { SingerId: 1, VenueId: 4, EventDate: "2017-10-05", Revenue: 11_000, LastUpdateTime: commit_timestamp },
    { SingerId: 1, VenueId: 19, EventDate: "2017-11-02", Revenue: 15_000, LastUpdateTime: commit_timestamp },
    { SingerId: 2, VenueId: 42, EventDate: "2017-12-23", Revenue: 7000, LastUpdateTime: commit_timestamp }
  ]
end

puts "Inserted data"

Commit timestamps can only be written to columns annotated with the allow_commit_timestamp=true option.

If you have mutations on rows in multiple tables, you must specify spanner.commit_timestamp() (or client library constant) for the commit timestamp column in each table.

Updating a row using a mutation

When updating a row, Cloud Spanner writes the commit timestamp value only if you include the column in the column list and pass the spanner.commit_timestamp() placeholder string (or client library constant) as its value. You cannot update the primary key of a row. To update the primary key, delete the existing row and create a new row.

For example, to update a commit timestamp column named LastUpdateTime:

C#

string connectionString =
$"Data Source=projects/{projectId}/instances/{instanceId}"
+ $"/databases/{databaseId}";
// Create connection to Cloud Spanner.
using (var connection = new SpannerConnection(connectionString))
{
    var cmd = connection.CreateUpdateCommand("Albums",
        new SpannerParameterCollection {
            {"SingerId", SpannerDbType.Int64},
            {"AlbumId", SpannerDbType.Int64},
            {"MarketingBudget", SpannerDbType.Int64},
            {"LastUpdateTime", SpannerDbType.Timestamp},
        });
    var cmdLookup =
        connection.CreateSelectCommand("SELECT * FROM Albums");
    using (var reader = await cmdLookup.ExecuteReaderAsync())
    {
        while (await reader.ReadAsync())
        {
            if (reader.GetFieldValue<int>("SingerId") == 1
                && reader.GetFieldValue<int>("AlbumId") == 1)
            {
                cmd.Parameters["SingerId"].Value =
                    reader.GetFieldValue<int>("SingerId");
                cmd.Parameters["AlbumId"].Value =
                    reader.GetFieldValue<int>("AlbumId");
                cmd.Parameters["MarketingBudget"].Value = 1000000;
                cmd.Parameters["LastUpdateTime"].Value =
                    SpannerParameter.CommitTimestamp;
                await cmd.ExecuteNonQueryAsync();
            }
            if (reader.GetInt64(0) == 2 && reader.GetInt64(1) == 2)
            {
                cmd.Parameters["SingerId"].Value =
                    reader.GetFieldValue<int>("SingerId");
                cmd.Parameters["AlbumId"].Value =
                    reader.GetFieldValue<int>("AlbumId");
                cmd.Parameters["MarketingBudget"].Value = 750000;
                cmd.Parameters["LastUpdateTime"].Value =
                    SpannerParameter.CommitTimestamp;
                await cmd.ExecuteNonQueryAsync();
            }
        }
    }
}
Console.WriteLine("Updated data.");

Go

func updateWithTimestamp(ctx context.Context, w io.Writer, client *spanner.Client) error {
	cols := []string{"SingerId", "AlbumId", "MarketingBudget", "LastUpdateTime"}
	_, err := client.Apply(ctx, []*spanner.Mutation{
		spanner.Update("Albums", cols, []interface{}{1, 1, 1000000, spanner.CommitTimestamp}),
		spanner.Update("Albums", cols, []interface{}{2, 2, 750000, spanner.CommitTimestamp}),
	})
	return err
}

Java

static void updateWithTimestamp(DatabaseClient dbClient) {
  // Mutation can be used to update/insert/delete a single row in a table. Here we use
  // newUpdateBuilder to create update mutations.
  List<Mutation> mutations =
      Arrays.asList(
          Mutation.newUpdateBuilder("Albums")
              .set("SingerId")
              .to(1)
              .set("AlbumId")
              .to(1)
              .set("MarketingBudget")
              .to(1000000)
              .set("LastUpdateTime")
              .to(Value.COMMIT_TIMESTAMP)
              .build(),
          Mutation.newUpdateBuilder("Albums")
              .set("SingerId")
              .to(2)
              .set("AlbumId")
              .to(2)
              .set("MarketingBudget")
              .to(750000)
              .set("LastUpdateTime")
              .to(Value.COMMIT_TIMESTAMP)
              .build());
  // This writes all the mutations to Cloud Spanner atomically.
  dbClient.write(mutations);
}

Node.js

  // ...

  // Imports the Google Cloud client library
  const {Spanner} = require('@google-cloud/spanner');

  /**
   * TODO(developer): Uncomment the following lines before running the sample.
   */
  // const projectId = 'my-project-id';
  // const instanceId = 'my-instance';
  // const databaseId = 'my-database';

  // Creates a client
  const spanner = new Spanner({
    projectId: projectId,
  });

  // Gets a reference to a Cloud Spanner instance and database
  const instance = spanner.instance(instanceId);
  const database = instance.database(databaseId);

  // Update a row in the Albums table
  // Note: Cloud Spanner interprets Node.js numbers as FLOAT64s, so they
  // must be converted to strings before being inserted as INT64s
  const albumsTable = database.table('Albums');

  const data = [
    {
      SingerId: '1',
      AlbumId: '1',
      MarketingBudget: '1000000',
      LastUpdateTime: 'spanner.commit_timestamp()',
    },
    {
      SingerId: '2',
      AlbumId: '2',
      MarketingBudget: '750000',
      LastUpdateTime: 'spanner.commit_timestamp()',
    },
  ];

  try {
    await albumsTable.update(data);
    console.log('Updated data.');
  } catch (err) {
    console.error('ERROR:', err);
  } finally {
    // Close the database when finished
    database.close();
  }

PHP

use Google\Cloud\Spanner\SpannerClient;

/**
 * Updates sample data in a table with a commit timestamp column.
 *
 * Before executing this method, a new column MarketingBudget has to be added to the Albums
 * table by applying the DDL statement "ALTER TABLE Albums ADD COLUMN MarketingBudget INT64".
 *
 * In addition this update expects the LastUpdateTime column added by applying the DDL statement
 * "ALTER TABLE Albums ADD COLUMN LastUpdateTime TIMESTAMP OPTIONS (allow_commit_timestamp=true)"
 *
 * Example:
 * ```
 * update_data_with_timestamp_column($instanceId, $databaseId);
 * ```
 *
 * @param string $instanceId The Spanner instance ID.
 * @param string $databaseId The Spanner database ID.
 */
function update_data_with_timestamp_column($instanceId, $databaseId)
{
    $spanner = new SpannerClient();
    $instance = $spanner->instance($instanceId);
    $database = $instance->database($databaseId);

    $operation = $database->transaction(['singleUse' => true])
        ->updateBatch('Albums', [
            ['SingerId' => 1, 'AlbumId' => 1, 'MarketingBudget' => 1000000, 'LastUpdateTime' => $spanner->commitTimestamp()],
            ['SingerId' => 2, 'AlbumId' => 2, 'MarketingBudget' => 750000, 'LastUpdateTime' => $spanner->commitTimestamp()],
        ])
        ->commit();

    print('Updated data.' . PHP_EOL);
}

Python

def update_data_with_timestamp(instance_id, database_id):
    """Updates Performances tables in the database with the COMMIT_TIMESTAMP
    column.

    This updates the `MarketingBudget` column which must be created before
    running this sample. You can add the column by running the `add_column`
    sample or by running this DDL statement against your database:

        ALTER TABLE Albums ADD COLUMN MarketingBudget INT64

    In addition this update expects the LastUpdateTime column added by
    applying this DDL statement against your database:

        ALTER TABLE Albums ADD COLUMN LastUpdateTime TIMESTAMP
        OPTIONS(allow_commit_timestamp=true)
    """
    spanner_client = spanner.Client()
    instance = spanner_client.instance(instance_id)

    database = instance.database(database_id)

    with database.batch() as batch:
        batch.update(
            table='Albums',
            columns=(
                'SingerId', 'AlbumId', 'MarketingBudget', 'LastUpdateTime'),
            values=[
                (1, 1, 1000000, spanner.COMMIT_TIMESTAMP),
                (2, 2, 750000, spanner.COMMIT_TIMESTAMP)])

    print('Updated data.')

Ruby

# project_id  = "Your Google Cloud project ID"
# instance_id = "Your Spanner instance ID"
# database_id = "Your Spanner database ID"

require "google/cloud/spanner"

spanner = Google::Cloud::Spanner.new project: project_id
client  = spanner.client instance_id, database_id

commit_timestamp = client.commit_timestamp

client.commit do |c|
  c.update "Albums", [
    { SingerId: 1, AlbumId: 1, MarketingBudget: 100_000, LastUpdateTime: commit_timestamp },
    { SingerId: 2, AlbumId: 2, MarketingBudget: 750_000, LastUpdateTime: commit_timestamp }
  ]
end

puts "Updated data"

Commit timestamps can only be written to columns annotated with the allow_commit_timestamp=true option.

If you have mutations on rows in multiple tables, you must specify spanner.commit_timestamp() (or the client library constant) for the commit timestamp column in each table.

Querying a commit timestamp column

The following example queries the commit timestamp column of the table.

C#

string connectionString =
$"Data Source=projects/{projectId}/instances/{instanceId}"
+ $"/databases/{databaseId}";
// Create connection to Cloud Spanner.
using (var connection = new SpannerConnection(connectionString))
{
    var cmd =
        connection.CreateSelectCommand(
            "SELECT SingerId, AlbumId, "
            + "MarketingBudget, LastUpdateTime FROM Albums");
    using (var reader = await cmd.ExecuteReaderAsync())
    {
        while (await reader.ReadAsync())
        {
            string budget = string.Empty;
            if (reader["MarketingBudget"] != DBNull.Value)
            {
                budget = reader.GetFieldValue<string>("MarketingBudget");
            }
            string timestamp = string.Empty;
            if (reader["LastUpdateTime"] != DBNull.Value)
            {
                timestamp = reader.GetFieldValue<string>("LastUpdateTime");
            }
            Console.WriteLine("SingerId : "
            + reader.GetFieldValue<string>("SingerId")
            + " AlbumId : "
            + reader.GetFieldValue<string>("AlbumId")
            + $" MarketingBudget : {budget}"
            + $" LastUpdateTime : {timestamp}");
        }
    }
}

Go

func queryWithTimestamp(ctx context.Context, w io.Writer, client *spanner.Client) error {
	stmt := spanner.Statement{
		SQL: `SELECT SingerId, AlbumId, MarketingBudget, LastUpdateTime
				FROM Albums ORDER BY LastUpdateTime DESC`}
	iter := client.Single().Query(ctx, stmt)
	defer iter.Stop()
	for {
		row, err := iter.Next()
		if err == iterator.Done {
			return nil
		}
		if err != nil {
			return err
		}
		var singerID, albumID int64
		var marketingBudget spanner.NullInt64
		var lastUpdateTime spanner.NullTime
		if err := row.ColumnByName("SingerId", &singerID); err != nil {
			return err
		}
		if err := row.ColumnByName("AlbumId", &albumID); err != nil {
			return err
		}
		if err := row.ColumnByName("MarketingBudget", &marketingBudget); err != nil {
			return err
		}
		budget := "NULL"
		if marketingBudget.Valid {
			budget = strconv.FormatInt(marketingBudget.Int64, 10)
		}
		if err := row.ColumnByName("LastUpdateTime", &lastUpdateTime); err != nil {
			return err
		}
		timestamp := "NULL"
		if lastUpdateTime.Valid {
			timestamp = lastUpdateTime.String()
		}
		fmt.Fprintf(w, "%d %d %s %s\n", singerID, albumID, budget, timestamp)
	}
}

Java

static void queryMarketingBudgetWithTimestamp(DatabaseClient dbClient) {
  // Rows without an explicit value for MarketingBudget will have a MarketingBudget equal to
  // null.
  ResultSet resultSet =
      dbClient
          .singleUse()
          .executeQuery(
              Statement.of(
                  "SELECT SingerId, AlbumId, MarketingBudget, LastUpdateTime FROM Albums"
                      + " ORDER BY LastUpdateTime DESC"));
  while (resultSet.next()) {
    System.out.printf(
        "%d %d %s %s\n",
        resultSet.getLong("SingerId"),
        resultSet.getLong("AlbumId"),
        // We check that the value is non null. ResultSet getters can only be used to retrieve
        // non null values.
        resultSet.isNull("MarketingBudget") ? "NULL" : resultSet.getLong("MarketingBudget"),
        resultSet.isNull("LastUpdateTime") ? "NULL" : resultSet.getTimestamp("LastUpdateTime"));
  }
}

Node.js

  // ...

  // Imports the Google Cloud client library
  const {Spanner} = require('@google-cloud/spanner');

  /**
   * TODO(developer): Uncomment the following lines before running the sample.
   */
  // const projectId = 'my-project-id';
  // const instanceId = 'my-instance';
  // const databaseId = 'my-database';

  // Creates a client
  const spanner = new Spanner({
    projectId: projectId,
  });

  // Gets a reference to a Cloud Spanner instance and database
  const instance = spanner.instance(instanceId);
  const database = instance.database(databaseId);

  const query = {
    sql: `SELECT SingerId, AlbumId, MarketingBudget, LastUpdateTime
            FROM Albums ORDER BY LastUpdateTime DESC`,
  };

  // Queries rows from the Albums table
  try {
    const [rows] = await database.run(query);

    rows.forEach(row => {
      const json = row.toJSON();

      console.log(
        `SingerId: ${json.SingerId}, AlbumId: ${
          json.AlbumId
        }, MarketingBudget: ${
          json.MarketingBudget ? json.MarketingBudget : null
        }, LastUpdateTime: ${json.LastUpdateTime}`
      );
    });
  } catch (err) {
    console.error('ERROR:', err);
  } finally {
    // Close the database when finished
    database.close();
  }

PHP

use Google\Cloud\Spanner\SpannerClient;

/**
 * Queries sample data from a database with a commit timestamp column.
 *
 * This sample uses the `MarketingBudget` column. You can add the column
 * by running the `add_column` sample or by running this DDL statement against
 * your database:
 *
 *      ALTER TABLE Albums ADD COLUMN MarketingBudget INT64
 *
 * This sample also uses the 'LastUpdateTime' commit timestamp column. You can
 * add the column by running the `add_timestamp_column` sample or by running
 * this DDL statement against your database:
 *
 * 		ALTER TABLE Albums ADD COLUMN LastUpdateTime TIMESTAMP OPTIONS (allow_commit_timestamp=true)
 *
 * Example:
 * ```
 * query_data_with_timestamp_column($instanceId, $databaseId);
 * ```
 *
 * @param string $instanceId The Spanner instance ID.
 * @param string $databaseId The Spanner database ID.
 */
function query_data_with_timestamp_column($instanceId, $databaseId)
{
    $spanner = new SpannerClient();
    $instance = $spanner->instance($instanceId);
    $database = $instance->database($databaseId);

    $results = $database->execute(
        'SELECT SingerId, AlbumId, MarketingBudget, LastUpdateTime ' .
        ' FROM Albums ORDER BY LastUpdateTime DESC'
    );

    foreach ($results as $row) {
        if ($row['MarketingBudget'] == null) {
            $row['MarketingBudget'] = 'NULL';
        }
        if ($row['LastUpdateTime'] == null) {
            $row['LastUpdateTime'] = 'NULL';
        }
        printf('SingerId: %s, AlbumId: %s, MarketingBudget: %s, LastUpdateTime: %s' . PHP_EOL,
            $row['SingerId'], $row['AlbumId'], $row['MarketingBudget'], $row['LastUpdateTime']);
    }
}

Python

def query_data_with_timestamp(instance_id, database_id):
    """Queries sample data from the database using SQL.

    This updates the `LastUpdateTime` column which must be created before
    running this sample. You can add the column by running the
    `add_timestamp_column` sample or by running this DDL statement
    against your database:

        ALTER TABLE Performances ADD COLUMN LastUpdateTime TIMESTAMP
        OPTIONS (allow_commit_timestamp=true)

    """
    spanner_client = spanner.Client()
    instance = spanner_client.instance(instance_id)

    database = instance.database(database_id)

    with database.snapshot() as snapshot:
        results = snapshot.execute_sql(
            'SELECT SingerId, AlbumId, MarketingBudget FROM Albums '
            'ORDER BY LastUpdateTime DESC')

    for row in results:
        print(u'SingerId: {}, AlbumId: {}, MarketingBudget: {}'.format(*row))

Ruby

# project_id  = "Your Google Cloud project ID"
# instance_id = "Your Spanner instance ID"
# database_id = "Your Spanner database ID"

require "google/cloud/spanner"

spanner = Google::Cloud::Spanner.new project: project_id
client  = spanner.client instance_id, database_id

client.execute("SELECT SingerId, AlbumId, MarketingBudget, LastUpdateTime
                FROM Albums ORDER BY LastUpdateTime DESC").rows.each do |row|
  puts "#{row[:SingerId]} #{row[:AlbumId]} #{row[:MarketingBudget]} #{row[:LastUpdateTime]}"
end

Providing your own value for the commit timestamp column

You can provide your own value for the commit timestamp column, instead of passing spanner.commit_timestamp() (or client library constant) as the column value. The value must be a timestamp in the past. This restriction ensures that writing timestamps is an inexpensive and fast operation. An easy way to confirm that a value is in the past is to compare it to the value returned by the CURRENT_TIMESTAMP SQL function. The server returns a FailedPrecondition error if a future timestamp is specified.

Creating a changelog

Suppose that you want to create a changelog of every mutation that happens to a table and then use that changelog for auditing. An example would be a table that stores the history of changes to word processing documents. The commit timestamp makes creating the changelog easier, because the timestamps can enforce ordering of the changelog entries. You could build a changelog that stores the history of changes to a given document using a schema like the following example:

CREATE TABLE Documents (
  UserId     INT64 NOT NULL,
  DocumentId INT64 NOT NULL,
  Contents   STRING(MAX) NOT NULL,
) PRIMARY KEY (UserId, DocumentId);

CREATE TABLE DocumentHistory (
  UserId     INT64 NOT NULL,
  DocumentId INT64 NOT NULL,
  Ts         TIMESTAMP NOT NULL OPTIONS (allow_commit_timestamp=true),
  Delta      STRING(MAX),
) PRIMARY KEY (UserId, DocumentId, Ts),
  INTERLEAVE IN PARENT Documents ON DELETE NO ACTION;

To create a changelog, insert a new row in DocumentHistory in the same transaction in which you insert or update a row in Document. In the insertion of the new row in DocumentHistory, use the placeholder spanner.commit_timestamp() (or client library constant) to tell Cloud Spanner to write the commit timestamp into column Ts. Interleaving the DocumentsHistory table with the Documents table will allow for data locality and more efficient inserts and updates. However, it also adds the constraint that the parent and child rows must be deleted together. To keep the rows in DocumentHistory after rows in Documents are deleted, do not interleave the tables.

The size of a row should be less than 4 GB for best performance. (The size of a row includes the top-level row and all of its interleaved child and index rows.) In this example, there is a limit to how many rows there can be in DocumentHistory for a particular document, because of the row size limit. If you expect the changelog in DocumentHistory to be large, you can design your app to delete the oldest rows in DocumentHistory. Alternatively, you can design your schema so that DocumentHistory is a top-level table instead of an interleaved table.

What's next

Use commit timestamps to create a change log with Go.

Was this page helpful? Let us know how we did:

Send feedback about...

This page
Documentation feedback
Cloud Spanner Documentation
Product feedback