Hi guys.I am trying to convert an sql query into mongodb type.
The sql query look like this:
"select date_trunc('day',timestamp1) as day,avg(id9) from oneindextwocolumns where timestamp1>='2010-01-01 00:00:00' and timestamp1<='2020-12-31 00:55:00' group by day,id13 order by day asc "
This is what i have tried for mongodb:
cursor=mydb1.mongodbtime.aggregate([
{
"$match": {
"timestamp1": {"$gte": datetime.strptime("2010-01-01 00:00:00", "%Y-%m-%d %H:%M:%S"),
"$lte" :datetime.strptime("2020-12-31 00:55:00", "%Y-%m-%d %H:%M:%S")}
}
},
{
"$group": {
"_id":{
"date": {"$dateToString": { "format": "%Y-%m-%d ", "date": "$timestamp1" }},
"id13":"$id13",
},
"avg_id9": {
"$avg": "$id9"
}
}
},
{
"$project": {
"_id": 0,
"day":"$_id.date",
"avg_id9":1
}
},
{"$sort": {"day": 1}}
])
But i dont get the same results in mongodb.What am i doing wrong?Does this have any thing to do with indexing?i have one compound index in psql table timestamp1 desc,id13 asc?I dont have index in mongodb table yet!
Edit:It seems tha the results where the same but not in the right order!
Thanks in advance!