Services or capabilities described in Amazon Web Services documentation might vary by Region. To see the differences applicable to the China Regions, see Getting Started with Amazon Web Services in China (PDF).

Using client-side timestamps in Amazon Keyspaces

You can use the Amazon Keyspaces (for Apache Cassandra) console, Cassandra Query Language (CQL), the Amazon SDK, and the Amazon Command Line Interface (Amazon CLI) to turn on client-side timestamps. This section provides examples of how to turn on client-side timestamps on new and existing tables and how to use client-side timestamps in queries. For more information about the API, see Amazon Keyspaces API Reference.

Creating a new table with client-side timestamps turned on (console)

Follow these steps to create a new table with client-side timestamps turned on with the Amazon Keyspaces console.

Turning on client-side timestamps on existing tables (console)

Follow these steps to turn on client-side timestamps for existing tables using the Amazon Keyspaces Amazon Web Services Management Console.

Creating a new table with client-side timestamps turned on (CQL)

To turn on client-side timestamps when you're creating a new table, you can use the following CQL statement.

CREATE TABLE my_table (
   userid uuid,
   time timeuuid,
   subject text,
   body text,
   user inet,
   PRIMARY KEY (userid, time)
) WITH CUSTOM_PROPERTIES = {'client_side_timestamps': {'status': 'enabled'}};

To confirm the client-side timestamps settings for the new table, use a SELECT statement to review the custom_properties as shown in the following example.

SELECT custom_properties from system_schema_mcs.tables where keyspace_name = 'my_keyspace' and table_name = 'my_table';

The output of this statement shows the status for client-side timestamps.

'client_side_timestamps': {'status': 'enabled'}

Turning on client-side timestamps for existing tables using ALTER TABLE (CQL)

To turn on client-side timestamps for an existing table, you can use the following CQL statement.

ALTER TABLE my_table WITH custom_properties = {'client_side_timestamps': {'status': 'enabled'}};;

To confirm the client-side timestamps settings for the new table, use a SELECT statement to review the custom_properties as shown in the following example.

SELECT custom_properties from system_schema_mcs.tables where keyspace_name = 'my_keyspace' and table_name = 'my_table';

The output of this statement shows the status for client-side timestamps.

'client_side_timestamps': {'status': 'enabled'}

Creating a new table with client-side timestamps turned on (CLI)

To turn on client-side timestamps when creating a new table, you can use the following CLI statement.

./aws keyspaces create-table \
--keyspace-name my_keyspace \
--table-name my_table \
--client-side-timestamps 'status=ENABLED' \
--schema-definition 'allColumns=[{name=id,type=int},{name=date,type=timestamp},{name=name,type=text}],partitionKeys=[{name=id}]'

To confirm that client-side timestamps are turned on for the new table, run the following code.

./aws keyspaces get-table \
--keyspace-name my_keyspace \
--table-name my_table 

The output should look similar to this example.

{
    "keyspaceName": "my_keyspace",
    "tableName": "my_table",
    "resourceArn": "arn:aws:cassandra:us-east-2:555555555555:/keyspace/my_keyspace/table/my_table",
    "creationTimestamp": 1662681206.032,
    "status": "ACTIVE",
    "schemaDefinition": {
        "allColumns": [
            {
                "name": "id",
                "type": "int"
            },
            {
                "name": "date",
                "type": "timestamp"
            },
            {
                "name": "name",
                "type": "text"
            }
        ],
        "partitionKeys": [
            {
                "name": "id"
            }
        ],
        "clusteringKeys": [],
        "staticColumns": []
    },
    "capacitySpecification": {
        "throughputMode": "PAY_PER_REQUEST",
        "lastUpdateToPayPerRequestTimestamp": 1662681206.032
    },
    "encryptionSpecification": {
        "type": "AWS_OWNED_KMS_KEY"
    },
    "pointInTimeRecovery": {
        "status": "DISABLED"
    },
    "clientSideTimestamps": {
        "status": "ENABLED"
    },
    "ttl": {
        "status": "ENABLED"
    },
    "defaultTimeToLive": 0,
    "comment": {
        "message": ""
    }
}

Turning on client-side timestamps on an existing table (CLI)

To turn on client-side timestamps for an existing table using the CLI, you can use the following code.

./aws keyspaces update-table \
--keyspace-name my_keyspace \
--table-name my_table \
--client-side-timestamps 'status=ENABLED'

To confirm that client-side timestamps are turned on for the table, run the following code.

./aws keyspaces get-table \
--keyspace-name my_keyspace \
--table-name my_table

The output should look similar to this example.

{
    "keyspaceName": "my_keyspace",
    "tableName": "my_table",
    "resourceArn": "arn:aws:cassandra:us-east-2:555555555555:/keyspace/my_keyspace/table/my_table",
    "creationTimestamp": 1662681312.906,
    "status": "ACTIVE",
    "schemaDefinition": {
        "allColumns": [
            {
                "name": "id",
                "type": "int"
            },
            {
                "name": "date",
                "type": "timestamp"
            },
            {
                "name": "name",
                "type": "text"
            }
        ],
        "partitionKeys": [
            {
                "name": "id"
            }
        ],
        "clusteringKeys": [],
        "staticColumns": []
    },
    "capacitySpecification": {
        "throughputMode": "PAY_PER_REQUEST",
        "lastUpdateToPayPerRequestTimestamp": 1662681312.906
    },
    "encryptionSpecification": {
        "type": "AWS_OWNED_KMS_KEY"
    },
    "pointInTimeRecovery": {
        "status": "DISABLED"
    },
    "clientSideTimestamps": {
        "status": "ENABLED"
    },
    "ttl": {
        "status": "ENABLED"
    },
    "defaultTimeToLive": 0,
    "comment": {
        "message": ""
    }
}

Using client-side timestamps in Data Manipulation Language (DML) statements

After you have turned on client-side timestamps, you can pass the timestamp in your INSERT, UPDATE, and DELETE statements with the USING TIMESTAMP clause. The timestamp value is a bigint representing a number of microseconds since the standard base time known as the epoch: January 1 1970 at 00:00:00 GMT. A timestamp that is supplied by the client has to fall between the range of 2 days in the past and 5 minutes in the future from the current wall clock time. Amazon Keyspaces keeps timestamp metadata for the life of the data. You can use the WRITETIME function to look up timestamps that occurred years in the past. For more information about CQL syntax, see DML statements (data manipulation language) in Amazon Keyspaces.

The following CQL statement is an example of how to use a timestamp as an update_parameter.

INSERT INTO catalog.book_awards (year, award, rank, category, book_title, author, publisher)
   VALUES (2022, 'Wolf', 4, 'Non-Fiction', 'Science Update', 'Ana Carolina Silva', 'SomePublisher') 
   USING TIMESTAMP 1669069624;

If you do not specify a timestamp in your CQL query, Amazon Keyspaces uses the timestamp passed by your client driver. If no timestamp is supplied by the client driver, Amazon Keyspaces assigns a server-side timestamp for your write operation.

To see the timestamp value that is stored for a specific column, you can use the WRITETIME function in a SELECT statement as shown in the following example.

SELECT year, award, rank, category, book_title, author, publisher, WRITETIME(year), WRITETIME(award), WRITETIME(rank),
  WRITETIME(category), WRITETIME(book_title), WRITETIME(author), WRITETIME(publisher) from catalog.book_awards;