Query a DynamoDB table with time range

Question

I am coming from a relational database background and currently trying to familiarise with NoSQL methodology (DynamoDB particularly). I am using Python boto3 SDK as my development environment.

I have a table called "availabilities" that has two fields called "fromTime" and "toTime". They hold time data in 24-hours format such as "16:00", or "08:30". I need to write a function that fetches all the records that the current time falls in the range of from and to times. For example current time is 16:00. The function should fetch all the rows that 16:00 falls into the range of from an to times such as 08:00 - 18:00. I am not sure if I should go with scan or query but here is how I tried it in a Lambda function:

import json
import logging
import boto3
from botocore.exceptions import ClientError
from boto3.dynamodb.conditions import Key, Attr
from datetime import datetime


def scan(self, table_name, region, filter_expression, projection_expression):
    dynamodb_resource = boto3.resource("dynamodb", region_name=region)
    table = dynamodb_resource.Table(table_name)
    
    response = table.scan(
        FilterExpression=filter_expression,
        ProjectionExpression=projection_expression
    )
    return response
    
def lambda_handler(event, context):
    now = datetime.now()
    current_time = now.strftime("%H:%M")
    
    fe = current_time.between(Key('fromTime'), Key('toTime'))
    pe = 'user_id'
    
    availableUserIDs = scan('availabilities', 'us-east-1', fe, pe)['Items']
    
    return {
        'statusCode': 200,
        'body': json.dumps(availableUserIDs)
    }

I'm getting an error as follows:

"errorMessage": "'str' object has no attribute 'between'"

I am thinking about converting everything into minutes since midnight by multiplying hours by 60 and adding minutes on to it, and then comparing like that. However, I'm not sure how I can access the fromTime and toTime fields before actually running the scan function.

`fe` is a string and I would not think that `current_time.between(Key('fromTime'), Key('toTime'))` would work. That aside, check out https://stackoverflow.com/questions/40390386/how-to-scan-between-date-range-using-lambda-and-dynamodb for a more correct filter expression. — JonSG, Mar 24 '23 at 15:42
You cannot use GetItem or Query to retrieve items that have an attribute value in some arbitrary range (such as between a start and end time). Your only option would be to scan, with a filter. That said, scan is costly (performance and price) because it touches every item in the table. You don't generally want to use scan to find items, unless it's a small table. — jarmod, Mar 24 '23 at 15:44
@JonSG if my fields were of type date, this would be the way to go. However, my fields are of type time such as '16:00' and '08:30' — CrimsonDay, Mar 24 '23 at 15:48
Maybe one possible solution could be creating 2 date objects from current day and set the time to from or to time accordingly. — CrimsonDay, Mar 24 '23 at 15:52

Leeroy Hannigan · Accepted Answer · 2023-03-24T18:58:50.960

update

As you mentioned you have two different attributes, you cannot use between you need to state Atrr('start').gt(time) AND Attr('end').lt(time)

This should work. Top tip, never create your clients inside a function, or inside the request handler of a Lambda function, you want to re-use the client as much as possible to reduce latency.

import json
import logging
import boto3
from botocore.exceptions import ClientError
from boto3.dynamodb.conditions import Key, Attr
from datetime import datetime
dynamodb_resource = boto3.resource("dynamodb", region_name='us-east-1')
table = dynamodb_resource.Table('availabilities')

def scan(self, start_time, end_time, projection_expression):
    
    response = table.scan(
         FilterExpression=Attr('<your-key>').between(start_time, end_time)
         ProjectionExpression=projection_expression
    )
    return response
    
def lambda_handler(event, context):
    
    pe = 'user_id'
    start_time = '<some start time>'
    end_time = '<some end time>'
    availableUserIDs = scan(start_time, end_time, pe)['Items']
    
    return {
        'statusCode': 200,
        'body': json.dumps(availableUserIDs)
    }

I think this is doing the exact opposite of what I needed. The start_time and end_time are attributes in table. What we are looking for is to check if current time falls in between these two attributes. — CrimsonDay, Mar 24 '23 at 17:14
Then you cannot use between. You need to state one attribute greater than and the other less than . — Leeroy Hannigan, Mar 24 '23 at 18:55
Thank you so much for the help, with a slight change in logic, I was able to accomplish what I needed:`Attr('fromTime').lt(current_time) & Attr('toTime').gt(current_time)` Also thank you for the heads up about the clients, I fixed that part as well. Cheers! — CrimsonDay, Mar 25 '23 at 17:58

Query a DynamoDB table with time range

1 Answers1

update