DynamoDB timestamp attribute stored in string format, should retrieve records within a specified timestamp range

Question

Dynamodb has timestamp attribute which stores date and time "Thu Jan 14 19:55:27 UTC 2021" in String format. Now, I want to retrieve records which are in between "Thu Jan 11 20:55:27 UTC 2021" and "Thu Jan 13 22:15:27 UTC 2021". Below is the code:

from datetime import datetime
from boto3.dynamodb.conditions import Attr

def lambda_handler(event, context):
    startdatetime=event["startdate"]
    enddatetime=event["enddate"]
    start=datetime.strptime(startdatetime,"%a %b %d %H:%M:%S UTC %Y")
    end=datetime.strptime(enddatetime,"%a %b %d %H:%M:%S UTC %Y")
    
    fe=Attr('timestamp').between(start,end)
    response = table.scan(
            FilterExpression=fe
    )

This is not giving proper set of records as comparision is happening between string and datetime. Converting Attr() to datetime is not happening. Below is the code snippet I tried:

fe=datetime.strptime(str(Attr('timestamp')),"%a %b %d %H:%M:%S UTC %Y").between(start, end)

This also didnt help.

Any suggestions on this ?

Maurice Maurice · Accepted Answer · 2021-02-12T17:53:21

DynamoDB does string comparisons for the between part it has no actual concept of a datetime, which is why you have to be careful about the way you store your datetime information in the table.

There are basically two ways that are recommended to store the data in for between queries:

ISO8601 in UTC time, so something like this: 2021-02-12T18:50:00.000+00:00
Epoch time, the number of seconds since 1970-01-01T00:00:00.000+00:00

When you query, you also need to normalize the timestamp you want to query for according to these rules, otherwise it won't work.

Bottom line, the way you want to query it doesn't work, because the format doesn't lend itself to between queries when the underlying data structure is a string.

DynamoDB timestamp attribute stored in string format, should retrieve records within a specified timestamp range

1 Answers