TTL silent non-deletion with millisecond epoch DynamoDB Time To Live

DynamoDB TTL only supports Unix epoch time in seconds. If you store epoch milliseconds, the value is interpreted as seconds, placing the expiration date tens of thousands of years in the future. This results in "silent non-deletion" where items never expire despite appearing to have a valid timestamp.

Modeling for Local Midnight and DST Transitions

Because DynamoDB treats attributes as opaque values and lacks timezone-aware functions, you must handle all temporal logic at the application layer before writing to the database. To support a consistent local midnight in America/New_York while maintaining chronological sort order across Daylight Saving Time (DST) transitions, use the following model:

The Recommended Schema

• TTL Attribute (Number): Store the UTC Unix epoch seconds for the calculated local midnight. This ensures the TTL engine deletes the item correctly.
• GSI Sort Key (Number): Use the same UTC Unix epoch seconds. Since numbers sort numerically, this guarantees a strict chronological sequence regardless of DST offset changes.
• Display Attribute (String/Map): If the local wall-clock time is needed for presentation, store it as a separate attribute. Do not use this for sorting or filtering.

Implementation Steps

1. Calculate at Write: Use an IANA-aware library (e.g., luxon or java.time) to determine the exact UTC instant of the next local midnight in America/New_York.
2. Normalize to Seconds: Convert that instant to a Number representing UTC epoch seconds.
3. Update Writes: Ensure both the TTL attribute and the GSI sort key receive this normalized Number.
4. Backfill Existing Data: Existing items with millisecond values will not expire automatically. You must scan the table and update these attributes by dividing the millisecond values by 1,000.

ISO 8601 Sort Order Guarantees

There are no guarantees that ISO 8601 strings with differing offsets will sort chronologically on a Global Secondary Index. DynamoDB sorts strings lexicographically by UTF-8 bytes. For example, a timestamp ending in Z (UTC) will sort differently than one ending in -04:00, even if they represent the same instant. To maintain chronological order in a string-based sort key, you must normalize all values to a single fixed offset (typically UTC/Z).

Verification Commands

To diagnose the current state of your TTL attribute, check a sample item via the CLI:

aws dynamodb get-item --table-name YourTableName --key '{"YourPartitionKey": {"S": "ExampleValue"}}'

Observation: If the TTL value is approximately 1.7e12, it is in milliseconds (incorrect). If it is approximately 1.7e9, it is in seconds (correct).

Note: To provide a more specific migration strategy, please confirm if your GSI sort key is currently a String or a Number.