I have a date field in MongoDB formatted as, e.g., "01 Aug 2020 06:26:09 GMT". I've created an index on this field, and my queries with filters don't throw an error, but they filter improperly — for example filtering with db.collection.find({'date' : {'$gte' : '01 Jan 2020'}}) will still return results from before 2020. The same happens if I use '2020-01-01', or '01-01-2020'.
Is there a way I can format by date on this field given its format?
As commented by Charchit Kapoor and Wernfried Domscheit, you should avoid storing date values as Strings.
Nevertheless, for your specific case, you can convert your existing data into proper date objects under 2 assumptions:
With proper date objects, you can perform correct filtering.
Steps:
$split the date string into tokens for processing$indexOfCP with :. If it is a time string, $split into tokens and put them back into the original array$indexOfArray to convert them into int values(i.e. Jan to 1, Feb to 2 ...); Meanwhile, convert other string tokens into int$dateFromParts with tokens to construct proper date object$merge back to the collection for updatedb.collection.aggregate([
// break into tokens for processing
{
"$addFields": {
"tokens": {
"$split": [
"$date",
" "
]
}
}
},
// try to parse time part and break into hh, mm, ss
{
"$addFields": {
"tokens": {
"$reduce": {
"input": "$tokens",
"initialValue": [],
"in": {
"$cond": {
"if": {
$ne: [
-1,
{
"$indexOfCP": [
"$$this",
":"
]
}
]
},
"then": {
"$concatArrays": [
"$$value",
{
"$split": [
"$$this",
":"
]
}
]
},
"else": {
"$concatArrays": [
"$$value",
[
"$$this"
]
]
}
}
}
}
}
}
},
// try to 1. parse month part and 2. convert into int
{
"$addFields": {
"tokens": {
$let: {
vars: {
tokens: "$tokens",
monthArray: [
"dummy",
"Jan",
"Feb",
"Mar",
"Apr",
"May",
"Jun",
"Jul",
"Aug",
"Sep",
"Oct",
"Nov",
"Dec"
]
},
in: {
"$map": {
"input": "$$tokens",
"as": "t",
"in": {
"$switch": {
"branches": [
{
"case": {
"$in": [
"$$t",
"$$monthArray"
]
},
"then": {
"$indexOfArray": [
"$$monthArray",
"$$t"
]
}
}
],
default: {
"$convert": {
"input": "$$t",
"to": "int",
"onError": "$$t"
}
}
}
}
}
}
}
}
}
},
{
"$addFields": {
"parsedDate": {
"$dateFromParts": {
"year": {
"$arrayElemAt": [
"$tokens",
2
]
},
"month": {
"$arrayElemAt": [
"$tokens",
1
]
},
"day": {
"$arrayElemAt": [
"$tokens",
0
]
},
"hour": {
"$arrayElemAt": [
"$tokens",
3
]
},
"minute": {
"$arrayElemAt": [
"$tokens",
4
]
},
"second": {
"$arrayElemAt": [
"$tokens",
5
]
}
}
}
}
},
// cosmetics
{
"$project": {
"date": "$parsedDate"
}
},
// update back to collection
{
"$merge": {
"into": "collection",
"on": "_id",
"whenMatched": "merge"
}
}
])
Dateobjects. MongoDB does not support months names, either you write a $switch stage with 12 branches, or you use a 3rd party library like moment or Luxon