Fetching data on the basis of Date and Time range

Hi all,
I am working on a project we have documents with following data:

 {
    "operator" : "ABC",
    "startTime" : "18:00",
    "startDate" : "20-02-2024",
    "endTime" : "18:15",
    "endDate" : "20-02-2024",
    "totalCost" : 2.3,
    "totalProduction" : 3.3,
}

now we want to retrieve data on the basis of provided range of dates and time i.e. user will add startDate, endDate, startTime and endTime. for example user enters startDate=20-02-2024&endDate=23-02-2024&startTime=10:15&endTime=14:00,All the data in that range should be fetched means data starting from 20th feb at 10:15 till 23rd feb till 14:00 should be in the results. How can we do this, any help in this regard. Here the main issue is we are saving dates and time as strings and not as LocalDateTime.

Regards,
Saif

You are quite right. To be able to do date and time range you must convert to Date.

1 - you may convert once and for all
2 - you may convert dynamically every time you run the query which will not be able to use indexes as you will be querying on computed values

I let you decide which one is the best.

1 Like