db.test.insert({"dateString" : "2020-07-24T11:06:53.975+02:00[GMT+02:00]"})
So i want to pull a report, but formatted it to date, so i can do some difference down the pipeline:
tried:
db.test.aggregate([{$project: {date: {"$dateFromString": {"dateString": "$dateString", "format": "yyyy-MM-dd'T'HH:mm:ss", "timezone": "Etc/GMT+2"}}}}])
But i cant seem to get format right:
"errmsg" : "Error parsing date string '2020-07-24T11:06:53.975+02:00[GMT+02:00]'; 0: Format literal not found '2'; 1: Format literal not found '0'; 2: Format literal not found '2'; 3: Format literal not found '0'; 5: Format literal not found '0'; 6: Format literal not found '7'; 8: Format literal not found '2'; 9: Format literal not found '4'; 10: Format literal not found 'T'; 11: Format literal not found '1'; 12: Format literal not found '1'; 13: Format literal not found ':'; 14: Format literal not found '0'; 15: Format literal not found '6'; 16: Format literal not found ':'; 17: Format literal not found '5'; 18: Format literal not found '3'; 19: Format literal not found '.'; 20: Format literal not found '9'; 21: Format literal not found '7'; 22: Format literal not found '5'; 23: Trailing data '+'"
The date operators will not understand custom format, and also it will not understand the offset timezone
$substr
to select only date from 0 to 23 charactersdb.test.aggregate([
{
$project: {
date: {
"$dateFromString": {
"dateString": {
"$substr": [ "$dateString", 0, 23 ]
},
"timezone": "GMT"
}
}
}
}
])