Convert sql date query to mongodb type query

Working with Data
, ,
1

Hi guys.
I have 2 queries where i find very hard to convert them to mongodb type language.
The first one is this:

cursor=execute("select date_trunc('day',timestamp1) as day,avg(id13) from timestamppsql where timestamp1 >='2010-01-01 00:05:00' and timestamp1<='2011-01-01 00:05:00' group by day")

And the second one is this:

 cursor=execute("select date_trunc('hour',timestamp1) as hour,avg(id13) from timestamppsql where timestamp1 >='2010-01-01 00:05:00' and timestamp1<='2010-01-01 10:05:00' group by hour order by hour desc")

I cant find out whats the equivalent date_trunc in mongodb.Any help would be very much appreciated.Thanks in advance!

2

Hey Harris,

Take a look at the $dateToString aggregation function :slight_smile:

Specifically, if you want just the hour, you could use the %d for day and %H for hour do something like:

{ $dateToString: { format: “%d”, date: “$date” } } // for day of the month
{ $dateToString: { format: “%H”, date: “$date” } } // for hour

More on the options can be found here: https://docs.mongodb.com/manual/reference/operator/aggregation/dateToString/#format-specifiers

Let me know if that helps :slight_smile:

3 Likes
3

I am sorry for possible mistakes i am still new in mongodb:
This is what i have wrote:
(timestamp1 is a datetime object)

cursor = mydb1.mongodbtime.aggregate(
  [
      {
        "$match": {
           "timestamp1": {"$gte": datetime.strptime("2010-01-01 00:05:00", "%Y-%m-%d %H:%M:%S"),
                          "$lte" :datetime.strptime("2011-01-01 00:05:00", "%Y-%m-%d %H:%M:%S")}
        }
      },
      {
        "$group": {

          "_id": "null",
            "day":{"$dateToString": { format: "%d", "timestamp1": "$timestamp1" }},
          "avg_id13": {
            "$avg": "$id13"
          }
        }
      }
    ]
)

The output is this:

documents must have only string keys, key was <built-in function format>
5

Hey Harris,

You are receiving the error documents must have only string keys, key was <built-in function format> because you are missing quotes around the format field.

Also you don’t need to have the “null” id. You only need that if you are creating a total for all documents.

Try this:

cursor = mydb1.mongodbtime.aggregate(
  [
      {
        "$match": {
           "timestamp1": {"$gte": datetime.strptime("2010-01-01 00:05:00", "%Y-%m-%d %H:%M:%S"),
                          "$lte" :datetime.strptime("2011-01-01 00:05:00", "%Y-%m-%d %H:%M:%S")}
        }
      },
      {
        "$group": {

          "_id": {"$dateToString": { "format": "%d-%m-%Y", "date": "$timestamp1" }},
          "avg_id13": {
            "$avg": "$id13"
          }
        }
      },
     {
        "$project": {
            "_id":0,
            "day":"$_id",
            "avg_id13":1
         }
     }
    ]
)
6

This topic was automatically closed 5 days after the last reply. New replies are no longer allowed.