# Client-side timestamps in Amazon Keyspaces
In Amazon Keyspaces, client-side timestamps are Cassandra-compatible timestamps that are persisted for each cell in your table. You can use client-side timestamps for conflict resolution by letting your client applications determine the order of writes. For example, when clients of a globally distributed application make updates to the same data, client-side timestamps persist the order in which the updates were made on the clients. Amazon Keyspaces uses these timestamps to process the writes.
Amazon Keyspaces client-side timestamps are fully managed. You don’t have to manage low-level system settings such as clean-up and compaction strategies.
When you delete data, the rows are marked for deletion with a tombstone. Amazon Keyspaces removes tombstoned data automatically (typically within 10 days) without impacting your application performance or availability. Tombstoned data isn't available for data manipulation language (DML) statements. As you continue to perform reads and writes on rows that contain tombstoned data, the tombstoned data continues to count towards storage, read capacity units (RCUs), and write capacity units (WCUs) until it's deleted from storage.
After client-side timestamps have been turned on for a table, you can specify a timestamp with the `USING TIMESTAMP` clause in your Data Manipulation Language (DML) CQL query. For more information, see [Use client-side timestamps in queries in Amazon Keyspaces](client-side-timestamps-how-to-queries.md). If you do not specify a timestamp in your CQL query, Amazon Keyspaces uses the timestamp passed by your client driver. If the client driver doesn’t supply timestamps, Amazon Keyspaces assigns a cell-level timestamp automatically, because timestamps can't be `NULL`. To query for timestamps, you can use the `WRITETIME` function in your DML statement.
Amazon Keyspaces doesn't charge extra to turn on client-side timestamps. However, with client-side timestamps you store and write additional data for each value in your row. This can lead to additional storage usage and in some cases additional throughput usage. For more information about Amazon Keyspaces pricing, see [Amazon Keyspaces (for Apache Cassandra) pricing](https://aws.amazon.com/keyspaces/pricing).
When client-side timestamps are turned on in Amazon Keyspaces, every column of every row stores a timestamp. These timestamps take up approximately 20–40 bytes (depending on your data), and contribute to the storage and throughput cost for the row. These metadata bytes also count towards your 1-MB row size quota. To determine the overall increase in storage space (to ensure that the row size stays under 1 MB), consider the number of columns in your table and the number of collection elements in each row. For example, if a table has 20 columns, with each column storing 40 bytes of data, the size of the row increases from 800 bytes to 1200 bytes. For more information on how to estimate the size of a row, see [Estimate row size in Amazon Keyspaces](calculating-row-size.md). In addition to the extra 400 bytes for storage, in this example, the number of write capacity units (WCUs) consumed per write increases from 1 WCU to 2 WCUs. For more information on how to calculate read and write capacity, see [Configure read/write capacity modes in Amazon Keyspaces](ReadWriteCapacityMode.md).
After client-side timestamps have been turned on for a table, you can't turn it off.
To learn more about how to use client-side timestamps in queries, see [Use client-side timestamps in queries in Amazon Keyspaces](client-side-timestamps-how-to-queries.md).
**Topics**
+ [How Amazon Keyspaces client-side timestamps integrate with AWS services](#client-side-timestamps_integration)
+ [Create a new table with client-side timestamps in Amazon Keyspaces](client-side-timestamps-create-new-table.md)
+ [Configure client-side timestamps for a table in Amazon Keyspaces](client-side-timestamps-existing-table.md)
+ [Use client-side timestamps in queries in Amazon Keyspaces](client-side-timestamps-how-to-queries.md)
## How Amazon Keyspaces client-side timestamps integrate with AWS services
The following client-side timestamps metric is available in Amazon CloudWatch to enable continuous monitoring.
+ `SystemReconciliationDeletes` – The number of delete operations required to remove tombstoned data.
For more information about how to monitor CloudWatch metrics, see [Monitoring Amazon Keyspaces with Amazon CloudWatch](monitoring-cloudwatch.md).
When you use CloudFormation, you can enable client-side timestamps when creating a Amazon Keyspaces table. For more information, see the [AWS CloudFormation User Guide](https://docs.aws.amazon.com/AWSCloudFormation/latest/UserGuide/aws-resource-cassandra-table.html).
# Create a new table with client-side timestamps in Amazon Keyspaces
Follow these examples to create a new Amazon Keyspaces table with client-side timestamps enabled using the Amazon Keyspaces AWS Management Console, Cassandra Query Language (CQL), or the AWS Command Line Interface
------
#### [ Console ]
**Create a new table with client-side timestamps (console)**
1. Sign in to the AWS Management Console, and open the Amazon Keyspaces console at [https://console.aws.amazon.com/keyspaces/home](https://console.aws.amazon.com/keyspaces/home).
1. In the navigation pane, choose **Tables**, and then choose **Create table**.
1. On the **Create table** page in the **Table details** section, select a keyspace and provide a name for the new table.
1. In the **Schema** section, create the schema for your table.
1. In the **Table settings** section, choose **Customize settings**.
1. Continue to **Client-side timestamps**.
Choose **Turn on client-side timestamps** to turn on client-side timestamps for the table.
1. Choose **Create table**. Your table is created with client-side timestamps turned on.
------
#### [ Cassandra Query Language (CQL) ]
**Create a new table using CQL**
1. To create a new table with client-side timestamps enabled using CQL, you can use the following example.
```
CREATE TABLE my_keyspace.my_table (
userid uuid,
time timeuuid,
subject text,
body text,
user inet,
PRIMARY KEY (userid, time)
) WITH CUSTOM_PROPERTIES = {'client_side_timestamps': {'status': 'enabled'}};
```
1. 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'}
```
------
#### [ AWS CLI ]
**Create a new table using the AWS CLI**
1. To create a new table with client-side timestamps enabled, you can use the following example.
```
./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}]'
```
1. 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-1:111122223333:/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": ""
}
}
```
------
# Configure client-side timestamps for a table in Amazon Keyspaces
Follow these examples to turn on client-side timestamps for existing tables using the Amazon Keyspaces AWS Management Console, Cassandra Query Language (CQL), or the AWS Command Line Interface.
------
#### [ Console ]
**To turn on client-side timestamps for an existing table (console)**
1. Sign in to the AWS Management Console, and open the Amazon Keyspaces console at [https://console.aws.amazon.com/keyspaces/home](https://console.aws.amazon.com/keyspaces/home).
1. Choose the table that you want to update, and then choose **Additional settings** tab.
1. On the **Additional settings** tab, go to **Modify client-side timestamps** and select **Turn on client-side timestamps**
1. Choose **Save changes** to change the settings of the table.
------
#### [ Cassandra Query Language (CQL) ]
**Using a CQL statement**
1. Turn on client-side timestamps for an existing table with the `ALTER TABLE` CQL statement.
```
ALTER TABLE my_table WITH custom_properties = {'client_side_timestamps': {'status': 'enabled'}};;
```
1. 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'}
```
------
#### [ AWS CLI ]
**Using the AWS CLI**
1. You can turn on client-side timestamps for an existing table using the AWS CLI using the following example.
```
./aws keyspaces update-table \
--keyspace-name my_keyspace \
--table-name my_table \
--client-side-timestamps 'status=ENABLED'
```
1. 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 and state the status for client-side timestamps as `ENABLED`.
```
{
"keyspaceName": "my_keyspace",
"tableName": "my_table",
"resourceArn": "arn:aws:cassandra:us-east-1:111122223333:/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": ""
}
}
```
------
# Use client-side timestamps in queries in Amazon Keyspaces
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](cql.dml.md).
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;
```