hi!
I want to get all the documents which occured after 8th august 2023 format saved in my document is “mm/dd/yyyy hh:mm:ss AM” in mongodb collection orderstatus
Example:
date_time: 12/31/2023 07:58:32 PM
i am trying ISO and newDate query but they are not working. Please recommend me a solution.
I want to get these documents in mongo compass and python as well.
Your help will be really appreciated.
Can you paste in exacly what a document looks like and what you have tried?
The major issue is that you stored your dates in the format
With this format you cannot compared 2 dates and determine directly if one is before the other. You need to convert the stored date into the ISO format before you are able to compare, sort, … That will be slow since you will compare on a computed field.
The only smart thing to do is to migrate your data to ISO date.
I want to save time as my dataset is so large and it will take alot og time to convert the date format and then find my required data out of my converted data.
This is my document
{
“_id”: {
“$oid”: “63c933d4cf7b48bfa00875g1”
},
“date_time”: “01/18/2023 03:16:02 PM”, // date format is mm/dd/yyyy hh/mm/ss AM
“order_no”: “63942369”,
}
I want to get all the documents that occured after 8th august 2023
i tried ISODate and new Date query with various styles and formats but didn’t get the right answer.
I think you’re out of luck using that date_time field as stevej pointed out, it’s in a horrible format for any date comparrison.
Going forward I think the best option would be to convert them all to dates and store it as an actual date going forward. This will be searchable and also require less space for storage.
An alternative may be to use the object id, this has the date time embedded within it, so assuming you’re just pushing data into the server without doing anything funky with the objectIds (i.e. letting the driver or server generate them as opposed to hand crafting them) you could find the document ID that was created on that date, and get anything with an ID higher.
See:
Your date format is so bad for your use case that you cannot save time.
You have 2 choices
1 - permanently convert and migrate your data to an appropriate date format and waste time once for the migration
2 - dynamically convert your data to an appropriate date format and waste time every time you query your data
WHY because your date format does not have a natural chronological order so there is not way to say find date_time greater than 8th august 2023. Because in your date format 9th august 2022 is greater than 8th august 2023.
This topic was automatically closed 5 days after the last reply. New replies are no longer allowed.