ISO 8601 Strings vs Unix Epoch for Time-Series Sort Keys

Comparison of Time-Series Sort Keys

Using a numeric Unix Epoch timestamp as a Sort Key does not significantly impact query performance compared to an ISO 8601 string. In DynamoDB, both numeric and string types support efficient range queries (e.g., BETWEEN, >, <) with similar latency and throughput characteristics.

Performance and Functional Trade-offs

While performance is comparable, the functional capabilities differ based on the data type:

• Unix Epoch (Number): Required for the native Time to Live (TTL) feature. If your architecture requires automatic item expiration, a numeric epoch is the only supported format.
• ISO 8601 (String): Provides human-readability in the AWS Console and supports lexicographical sorting. However, this only works if the format is strictly consistent (e.g., YYYY-MM-DDTHH:mm:ssZ). Mixing timezones or varying the precision of fractional seconds will break the chronological sort order.

Architectural Recommendation

It is architecturally sound to store a redundant ISO 8601 attribute for readability while using a numeric Epoch for the Sort Key and TTL. This pattern separates the operational requirement (efficient sorting and automated cleanup) from the observability requirement (human-readable debugging).

To implement this, follow these steps:

1. Set the Sort Key as a Number (Unix Epoch) to enable TTL and consistent range queries.
2. Add a secondary attribute (e.g., timestamp_iso) as a String for display purposes.
3. Enable TTL on the table, pointing it to the numeric Epoch attribute.

Verification Steps

To verify the behavior of your chosen implementation, you can use the AWS CLI to test range boundaries:

# Example: Querying a range of epoch timestamps
aws dynamodb query \
    --table-name YourTimeSeriesTable \
    --key-condition-expression "PartitionKey = :pk AND SortKey BETWEEN :start AND :end" \
    --expression-attribute-values '{ ":pk": {"S": "sensor_01"}, ":start": {"N": "1700000000"}, ":end": {"N": "1700086400"} }'

Note: To provide a more specific recommendation on storage costs, please clarify the expected volume of items per partition and the total item size.