Commit timestamps in PostgreSQL-dialect databases

This page describes how to write a commit timestamp for each insert and updateoperation that you perform with Spanner in PostgreSQL-dialect databases.

Insert commit timestamps

The commit timestamp, based onTrueTimetechnology, is the time when atransaction is committed in the database. You can atomically store the committimestamp of a transaction into a column.Using the commit timestamps stored in tables, you can determine the exactordering of mutations and build features like changelogs.

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

  1. Create a column of typeSPANNER.COMMIT_TIMESTAMP. For example:

    CREATETABLEPerformances(...LastUpdateTimeSPANNER.COMMIT_TIMESTAMPNOTNULL,...PRIMARYKEY(...));

  2. If you are performing inserts or updates with DML,use theSPANNER.PENDING_COMMIT_TIMESTAMP() function to write the committimestamp.

    If you are performing inserts or updates with prepared statementsor mutations,use the placeholder stringSPANNER.COMMIT_TIMESTAMP() for yourcommit timestamp column. You can also use thecommit timestamp constant provided by the client library. For example, thisconstant in the Java client isValue.COMMIT_TIMESTAMP.

When Spanner commits the transaction by using these placeholders ascolumn values, the actual commit timestamp is written to the specified column.You can then use this column valueto create a history of updates to the table.

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

Spanner commit timestamps have microsecond granularity,and they are converted to nanoseconds when stored inSPANNER.COMMIT_TIMESTAMPcolumns.

Keys and indexes

You can use a commit timestamp column as a primary key column or as a non-keycolumn. Primary keys can be defined asASC orDESC.

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

Avoid hotspots

Using commit timestamps under the following scenarios createshotspots, whichreduce data performance:

  • Commit timestamp column as the first part of the primary key of a table.

    CREATE TABLE Users (  LastAccess SPANNER.COMMIT_TIMESTAMP NOT NULL,  UserId     bigint NOT NULL,  ...  PRIMARY KEY (LastAccess, UserId)) ;

  • Commit timestamp primary key column as the first part of a secondary index.

    CREATE INDEX UsersByLastAccess ON Users(LastAccess)

    or

    CREATE INDEX UsersByLastAccessAndName ON Users(LastAccess, FirstName)

Hotspots reduce data performance, even with low write rates. There is noperformance overhead if commit timestamps are enabled on non-key columns thatare not indexed.

Add a commit timestamp column to an existing table

To add a commit timestamp column to an existing table, use theALTER TABLEstatement. For example to add aLastUpdateTime column to thePerformancestable, use the following statement:

ALTERTABLEPerformancesADDCOLUMNLastUpdateTimeSPANNER.COMMIT_TIMESTAMP;

Write a commit timestamp using a DML statement

You use theSPANNER.PENDING_COMMIT_TIMESTAMP() function to write the committimestamp in a DML statement. Spanner selects the commit timestamp when the transactioncommits.Note: After you call theSPANNER.PENDING_COMMIT_TIMESTAMP() function,the table and any derived index is unreadable to any subsequent SQL statements in the transaction. Youmust write commit timestamps as the last statement in a transaction to prevent the possibility oftrying to read the table. If you try to read the table, then Spanner returns an error.

The following DML statement updates theLastUpdateTime column in thePerformances table with the commit timestamp:

UPDATEPerformancesSETLastUpdateTime=SPANNER.PENDING_COMMIT_TIMESTAMP()WHERESingerId=1ANDVenueId=2ANDEventDate="2015-10-21"

Insert a row using a mutation

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

C++

voidInsertDataWithTimestamp(google::cloud::spanner::Clientclient){namespacespanner=::google::cloud::spanner;autocommit_result=client.Commit(spanner::Mutations{spanner::InsertOrUpdateMutationBuilder("Performances",{"SingerId","VenueId","EventDate","Revenue","LastUpdateTime"}).EmplaceRow(1,4,absl::CivilDay(2017,10,5),11000,spanner::CommitTimestamp{}).EmplaceRow(1,19,absl::CivilDay(2017,11,2),15000,spanner::CommitTimestamp{}).EmplaceRow(2,42,absl::CivilDay(2017,12,23),7000,spanner::CommitTimestamp{}).Build()});if(!commit_result)throwstd::move(commit_result).status();std::cout      <<"Update was successful [spanner_insert_data_with_timestamp_column]\n";}

C#

usingGoogle.Cloud.Spanner.Data;usingSystem;usingSystem.Collections.Generic;usingSystem.Linq;usingSystem.Threading.Tasks;publicclassWriteDataWithTimestampAsyncSample{publicclassPerformance{publicintSingerId{get;set;}publicintVenueId{get;set;}publicDateTimeEventDate{get;set;}publiclongRevenue{get;set;}}publicasyncTask<int>WriteDataWithTimestampAsync(stringprojectId,stringinstanceId,stringdatabaseId){stringconnectionString=$"Data Source=projects/{projectId}/instances/{instanceId}/databases/{databaseId}";List<Performance>performances=newList<Performance>{newPerformance{SingerId=1,VenueId=4,EventDate=DateTime.Parse("2017-10-05"),Revenue=11000},newPerformance{SingerId=1,VenueId=19,EventDate=DateTime.Parse("2017-11-02"),Revenue=15000},newPerformance{SingerId=2,VenueId=42,EventDate=DateTime.Parse("2017-12-23"),Revenue=7000},};// Create connection to Cloud Spanner.usingvarconnection=newSpannerConnection(connectionString);awaitconnection.OpenAsync();// Insert rows into the Performances table.varrowCountAarray=awaitTask.WhenAll(performances.Select(performance=>{varcmd=connection.CreateInsertCommand("Performances",newSpannerParameterCollection{{"SingerId",SpannerDbType.Int64,performance.SingerId},{"VenueId",SpannerDbType.Int64,performance.VenueId},{"EventDate",SpannerDbType.Date,performance.EventDate},{"Revenue",SpannerDbType.Int64,performance.Revenue},{"LastUpdateTime",SpannerDbType.Timestamp,SpannerParameter.CommitTimestamp},});returncmd.ExecuteNonQueryAsync();}));returnrowCountAarray.Sum();}}

Go

import("context""cloud.google.com/go/spanner")funcwriteWithTimestamp(dbstring)error{ctx:=context.Background()client,err:=spanner.NewClient(ctx,db)iferr!=nil{returnerr}deferclient.Close()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)returnerr}

Java

staticfinalList<Performance>PERFORMANCES=Arrays.asList(newPerformance(1,4,"2017-10-05",11000),newPerformance(1,19,"2017-11-02",15000),newPerformance(2,42,"2017-12-23",7000));staticvoidwriteExampleDataWithTimestamp(DatabaseClientdbClient){List<Mutation>mutations=newArrayList<>();for(Performanceperformance: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 libraryconst{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 clientconstspanner=newSpanner({projectId:projectId,});// Gets a reference to a Cloud Spanner instance and databaseconstinstance=spanner.instance(instanceId);constdatabase=instance.database(databaseId);// Instantiate Spanner table objectsconstperformancesTable=database.table('Performances');constdata=[{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 INT64stry{awaitperformancesTable.insert(data);console.log('Inserted data.');}catch(err){console.error('ERROR:',err);}finally{// Close the database when finisheddatabase.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(string $instanceId, string $databaseId): void{    $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

definsert_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)withdatabase.batch()asbatch: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.newproject:project_idclient=spanner.clientinstance_id,database_id# Get commit_timestampcommit_timestamp=client.commit_timestampclient.commitdo|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}]endputs"Inserted data"

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

Update a row using a mutation

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

For example, to update a commit timestamp column namedLastUpdateTime:

C++

voidUpdateDataWithTimestamp(google::cloud::spanner::Clientclient){namespacespanner=::google::cloud::spanner;autocommit_result=client.Commit(spanner::Mutations{spanner::UpdateMutationBuilder("Albums",{"SingerId","AlbumId","MarketingBudget","LastUpdateTime"}).EmplaceRow(1,1,1000000,spanner::CommitTimestamp{}).EmplaceRow(2,2,750000,spanner::CommitTimestamp{}).Build()});if(!commit_result)throwstd::move(commit_result).status();std::cout      <<"Update was successful [spanner_update_data_with_timestamp_column]\n";}

C#

usingGoogle.Cloud.Spanner.Data;usingSystem;usingSystem.Threading.Tasks;publicclassUpdateDataWithTimestampColumnAsyncSample{publicasyncTask<int>UpdateDataWithTimestampColumnAsync(stringprojectId,stringinstanceId,stringdatabaseId){stringconnectionString=$"Data Source=projects/{projectId}/instances/{instanceId}/databases/{databaseId}";usingvarconnection=newSpannerConnection(connectionString);varrowCount=0;usingvarupdateCmd1=connection.CreateUpdateCommand("Albums",newSpannerParameterCollection{{"SingerId",SpannerDbType.Int64,1},{"AlbumId",SpannerDbType.Int64,1},{"MarketingBudget",SpannerDbType.Int64,1000000},{"LastUpdateTime",SpannerDbType.Timestamp,SpannerParameter.CommitTimestamp},});rowCount+=awaitupdateCmd1.ExecuteNonQueryAsync();usingvarupdateCmd2=connection.CreateUpdateCommand("Albums",newSpannerParameterCollection{{"SingerId",SpannerDbType.Int64,2},{"AlbumId",SpannerDbType.Int64,2},{"MarketingBudget",SpannerDbType.Int64,750000},{"LastUpdateTime",SpannerDbType.Timestamp,SpannerParameter.CommitTimestamp},});rowCount+=awaitupdateCmd2.ExecuteNonQueryAsync();Console.WriteLine("Updated data.");returnrowCount;}}

Go

import("context""io""cloud.google.com/go/spanner")funcupdateWithTimestamp(wio.Writer,dbstring)error{ctx:=context.Background()client,err:=spanner.NewClient(ctx,db)iferr!=nil{returnerr}deferclient.Close()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}),})returnerr}

Java

staticvoidupdateWithTimestamp(DatabaseClientdbClient){// 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 libraryconst{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 clientconstspanner=newSpanner({projectId:projectId,});// Gets a reference to a Cloud Spanner instance and databaseconstinstance=spanner.instance(instanceId);constdatabase=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 INT64sconstalbumsTable=database.table('Albums');constdata=[{SingerId:'1',AlbumId:'1',MarketingBudget:'1000000',LastUpdateTime:'spanner.commit_timestamp()',},{SingerId:'2',AlbumId:'2',MarketingBudget:'750000',LastUpdateTime:'spanner.commit_timestamp()',},];try{awaitalbumsTable.update(data);console.log('Updated data.');}catch(err){console.error('ERROR:',err);}finally{// Close the database when finisheddatabase.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(string $instanceId, string $databaseId): void{    $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

defupdate_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)withdatabase.batch()asbatch: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.newproject:project_idclient=spanner.clientinstance_id,database_idcommit_timestamp=client.commit_timestampclient.commitdo|c|c.update"Albums",[{SingerId:1,AlbumId:1,MarketingBudget:100_000,LastUpdateTime:commit_timestamp},{SingerId:2,AlbumId:2,MarketingBudget:750_000,LastUpdateTime:commit_timestamp}]endputs"Updated data"

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

Query a commit timestamp column

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

C++

voidQueryDataWithTimestamp(google::cloud::spanner::Clientclient){namespacespanner=::google::cloud::spanner;spanner::SqlStatementselect("SELECT SingerId, AlbumId, MarketingBudget, LastUpdateTime""  FROM Albums"" ORDER BY LastUpdateTime DESC");usingRowType=std::tuple<std::int64_t,std::int64_t,absl::optional<std::int64_t>,absl::optional<spanner::Timestamp>>;autorows=client.ExecuteQuery(std::move(select));for(auto&row:spanner::StreamOf<RowType>(rows)){if(!row)throwstd::move(row).status();std::cout <<std::get<0>(*row) <<" " <<std::get<1>(*row);automarketing_budget=std::get<2>(*row);if(!marketing_budget){std::cout <<" NULL";}else{std::cout <<' ' <<*marketing_budget;}autolast_update_time=std::get<3>(*row);if(!last_update_time){std::cout <<" NULL";}else{std::cout <<' ' <<*last_update_time;}std::cout <<"\n";}}

C#

usingGoogle.Cloud.Spanner.Data;usingSystem;usingSystem.Collections.Generic;usingSystem.Threading.Tasks;publicclassQueryDataWithTimestampColumnAsyncSample{publicclassAlbum{publicintSingerId{get;set;}publicintAlbumId{get;set;}publicDateTime?LastUpdateTime{get;set;}publiclong?MarketingBudget{get;set;}}publicasyncTask<List<Album>>QueryDataWithTimestampColumnAsync(stringprojectId,stringinstanceId,stringdatabaseId){stringconnectionString=$"Data Source=projects/{projectId}/instances/{instanceId}/databases/{databaseId}";usingvarconnection=newSpannerConnection(connectionString);usingvarcmd=connection.CreateSelectCommand("SELECT SingerId, AlbumId, MarketingBudget, LastUpdateTime FROM Albums ORDER BY LastUpdateTime DESC");varalbums=newList<Album>();usingvarreader=awaitcmd.ExecuteReaderAsync();while(awaitreader.ReadAsync()){albums.Add(newAlbum{SingerId=reader.GetFieldValue<int>("SingerId"),AlbumId=reader.GetFieldValue<int>("AlbumId"),LastUpdateTime=reader.IsDBNull(reader.GetOrdinal("LastUpdateTime"))?(DateTime?)null:reader.GetFieldValue<DateTime>("LastUpdateTime"),MarketingBudget=reader.IsDBNull(reader.GetOrdinal("MarketingBudget"))?0:reader.GetFieldValue<long>("MarketingBudget")});}returnalbums;}}

Go

import("context""fmt""io""strconv""cloud.google.com/go/spanner""google.golang.org/api/iterator")funcqueryWithTimestamp(wio.Writer,dbstring)error{ctx:=context.Background()client,err:=spanner.NewClient(ctx,db)iferr!=nil{returnerr}deferclient.Close()stmt:=spanner.Statement{SQL:`SELECT SingerId, AlbumId, MarketingBudget, LastUpdateTimeFROM Albums ORDER BY LastUpdateTime DESC`}iter:=client.Single().Query(ctx,stmt)deferiter.Stop()for{row,err:=iter.Next()iferr==iterator.Done{returnnil}iferr!=nil{returnerr}varsingerID,albumIDint64varmarketingBudgetspanner.NullInt64varlastUpdateTimespanner.NullTimeiferr:=row.ColumnByName("SingerId",&singerID);err!=nil{returnerr}iferr:=row.ColumnByName("AlbumId",&albumID);err!=nil{returnerr}iferr:=row.ColumnByName("MarketingBudget",&marketingBudget);err!=nil{returnerr}budget:="NULL"ifmarketingBudget.Valid{budget=strconv.FormatInt(marketingBudget.Int64,10)}iferr:=row.ColumnByName("LastUpdateTime",&lastUpdateTime);err!=nil{returnerr}timestamp:="NULL"iflastUpdateTime.Valid{timestamp=lastUpdateTime.String()}fmt.Fprintf(w,"%d %d %s %s\n",singerID,albumID,budget,timestamp)}}

Java

staticvoidqueryMarketingBudgetWithTimestamp(DatabaseClientdbClient){// Rows without an explicit value for MarketingBudget will have a MarketingBudget equal to// null. A try-with-resource block is used to automatically release resources held by// ResultSet.try(ResultSetresultSet=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 libraryconst{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 clientconstspanner=newSpanner({projectId:projectId,});// Gets a reference to a Cloud Spanner instance and databaseconstinstance=spanner.instance(instanceId);constdatabase=instance.database(databaseId);constquery={sql:`SELECT SingerId, AlbumId, MarketingBudget, LastUpdateTime          FROM Albums ORDER BY LastUpdateTime DESC`,};// Queries rows from the Albums tabletry{const[rows]=awaitdatabase.run(query);rows.forEach(row=>{constjson=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 finisheddatabase.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(string $instanceId, string $databaseId): void{    $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

defquery_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)withdatabase.snapshot()assnapshot:results=snapshot.execute_sql("SELECT SingerId, AlbumId, MarketingBudget FROM Albums ""ORDER BY LastUpdateTime DESC")forrowinresults:print("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.newproject:project_idclient=spanner.clientinstance_id,database_idclient.execute("SELECT SingerId, AlbumId, MarketingBudget, LastUpdateTime                FROM Albums ORDER BY LastUpdateTime DESC").rows.eachdo|row|puts"#{row[:SingerId]}#{row[:AlbumId]}#{row[:MarketingBudget]}#{row[:LastUpdateTime]}"end

Provide your own value for the commit timestamp column

In your code, you can provide your own value for the commit timestamp columninstead of passingspanner.commit_timestamp() (or the available client libraryconstant) as the column value. The value must be a timestamp in the past. Thisrestriction ensures that writing timestamps is an inexpensive and fastoperation. One way to confirm that a value is in the past is to compare it tothe value returned by theCURRENT_TIMESTAMP SQL function. The server returns aFailedPrecondition error if a future timestamp is specified.

Create a changelog

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

CREATETABLEDocuments(UserIdint8NOTNULL,DocumentIdint8NOTNULL,ContentstextNOTNULL,PRIMARYKEY(UserId,DocumentId));CREATETABLEDocumentHistory(UserIdint8NOTNULL,DocumentIdint8NOTNULL,TsSPANNER.COMMIT_TIMESTAMPNOTNULL,Deltatext,PRIMARYKEY(UserId,DocumentId,Ts))INTERLEAVEINPARENTDocuments;

To create a changelog, insert a new row inDocumentHistory in the sametransaction in which you insert or update a row inDocument. In the insertionof the new row inDocumentHistory, use the placeholderspanner.commit_timestamp() (or client library constant) to tellSpanner to write the commit timestamp into columnTs.Interleaving theDocumentsHistory table with theDocuments table permitsdata locality and more efficient inserts and updates. However, it also adds theconstraint that the parent and child rows must be deleted together. To keep therows inDocumentHistory after rows inDocuments are deleted, don'tinterleave the tables.

Optimize recent-data queries with commit timestamps

Commit timestamps optimize your Spanner database andcan reduce query I/O when retrieving data written after a particulartime.

To activate this optimization, a query'sWHERE clause must include acomparison between a table's commit timestamp column and a specific timethat you provide, with the following attributes:

  • Provide the specific time as aconstant expression: a literal, aparameter, or a function whose own arguments evaluate to constants.

  • Compare whether the commit timestamp is more recent than thegiven time, through either the> or>= operators.

  • Optionally, add further restrictions to theWHERE clause withAND.Extending the clause withOR disqualifies the query from thisoptimization.

For example, consider the followingPerformances table, which includesa commit timestamp column:

CREATETABLEPerformances(SingerIdbigintNOTNULL,VenueIdbigintNOTNULL,EventDatetimestampwithtimezoneNOTNULL,Revenuebigint,LastUpdateTimespanner.commit_timestamp,PRIMARYKEY(SingerId,VenueId,EventDate));

This query benefits from the commit-timestamp optimization describedearlier, because it has a greater-than-or-equal-to comparison betweenthe table's commit timestamp column and a constant expression—in thiscase, a literal:

SELECT*FROMPerformancesWHERELastUpdateTime>='2022-01-01';

Except as otherwise noted, the content of this page is licensed under theCreative Commons Attribution 4.0 License, and code samples are licensed under theApache 2.0 License. For details, see theGoogle Developers Site Policies. Java is a registered trademark of Oracle and/or its affiliates.

Last updated 2025-07-18 UTC.