Mixed ISO 8601 timestamp formats cause unexpected sort order in DynamoDB queries

Question

Goal: store a timestamp attribute that supports correct chronological ordering for range queries and enables prefix‑based filters when used as a DynamoDB sort key.
Constraint: the attribute must be stored in a single canonical format because mixing UTC ISO 8601 strings, offset‑containing strings, or epoch numbers breaks lexicographic or numeric sort order and invalidates between/>= operations. Additionally, TTL requires a Number attribute in epoch seconds, and begins_with‑style queries only work on string sort keys.
Uncertainty: deciding whether to use a fixed‑format UTC ISO 8601 string (which preserves sort order and allows prefix queries) or an epoch number (which guarantees numeric order and satisfies TTL directly) while still meeting the application’s query patterns.
Which format gives reliable sort order and prefix query support?
How should TTL be handled if the primary timestamp is stored as an ISO string?
What are the trade‑offs of storing offsets in the timestamp string for regional display?

Tasadduq Burney · Answer

Recommended Format for Sorting and Prefix Queries
To achieve both reliable chronological sort order and support for begins_with prefix filters, you must use a fixed-format UTC ISO 8601 string (e.g., YYYY-MM-DDThh:mm:ss.sssZ). 
Because DynamoDB performs lexicographic (alphabetical) sorting on string attributes, the ISO 8601 format ensures that chronological order matches alphabetical order, provided the format is consistent and uses UTC. This allows you to use range operators like BETWEEN, >=, and <=, as well as begins_with for date-based filtering (e.g., finding all records for a specific year or month).

Handling TTL with ISO Strings
DynamoDB's Time to Live (TTL) feature exclusively requires a Number attribute containing a Unix epoch timestamp in seconds. It cannot evaluate ISO 8601 strings.
To support both query patterns and TTL, implement a dual-attribute strategy:

Sort Key Attribute: Store the UTC ISO 8601 string for range queries and prefix filtering.
  TTL Attribute: Store a separate numeric attribute (e.g., expire_at) containing the epoch seconds.

Trade-offs of Storing Offsets
Storing timezone offsets (e.g., +05:00) directly within the sort key attribute breaks chronological sorting. Lexicographic sorting treats the offset as a literal string; therefore, 2023-01-01T10:00:00+05:00 will not sort correctly relative to 2023-01-01T10:00:00Z.
Recommended approach for regional display:

Store the timestamp in UTC in the sort key.
  Store the regional offset or timezone ID in a separate, non-key attribute.
  Convert the UTC timestamp to the local time on the client side for display.

Verification Steps
To verify your implementation, execute the following tests:

Range Test: Perform a Query using a KeyConditionExpression with BETWEEN on two ISO 8601 strings to confirm items return in chronological order.
  Prefix Test: Use begins_with(sort_key, '2023-10') to verify you can retrieve all records for October 2023.
  TTL Test: Set a numeric epoch value in your TTL attribute for a test item and verify its deletion after the expiration window.

Mixed ISO 8601 timestamp formats cause unexpected sort order in DynamoDB queries

1 answer

Recommended Format for Sorting and Prefix Queries

Handling TTL with ISO Strings

Trade-offs of Storing Offsets

Verification Steps

Your perspective belongs here.

0 question comments