CODEWITHSUNDEEP
pythonmongodbmongodb-queryaggregation-frameworkmongoengine
funkifunki
funkifunki

Reputation: 1169

Return Date Object when Aggregating by Hour

I am trying to aggregate records by reducing datetime to hours. Is there a way to do it in mongoengine and to save the field type?

My data is:

{'spent': 7, 'time_started': datetime.datetime(2015, 4, 21, 16, 2, 16, 661000)}
{'spent': 3, 'time_started': datetime.datetime(2015, 4, 21, 17, 8, 5, 415000)}
{'spent': 3, 'time_started': datetime.datetime(2015, 4, 21, 15, 52, 45, 917000)}
{'spent': 1, 'time_started': datetime.datetime(2015, 4, 21, 16, 42, 32, 313000)}
{'spent': 8, 'time_started': datetime.datetime(2015, 4, 21, 16, 35, 46, 863000)}
{'spent': 5, 'time_started': datetime.datetime(2015, 4, 21, 15, 55, 1, 217000)}
{'spent': 10, 'time_started': datetime.datetime(2015, 4, 20, 17, 41, 50, 5000)}

Here is what i come out with so far:

pipeline =[
    'match': {
        "time_started": {
            "$gte": datetime.datetime(2015, 4, 21, 0, 0, 0),
            }
        },
    'project': {
        "spent": "$spent",
         "time_started": {"$dateToString": {
                "format": "%Y-%m-%dT%H:00:00",
                "date": "$time_started"
            }}
        },
    'group': {
        "_id": {
            "time_started": "$time_started",
            },
        "spent_total": {"$sum": "$spent"}
        }
    ]

It works well, but "time_started" is a string in the result, while i need datetime, like this:

{'spent_total': 16, 'time_started': datetime.datetime(2015, 4, 21, 16, 0, 0)}
{'spent_total': 3, 'time_started': datetime.datetime(2015, 4, 21, 17, 0, 0)}
{'spent_total': 8, 'time_started': datetime.datetime(2015, 4, 21, 15, 0, 0)}

Upvotes: 3

Views: 74

Answers (1)

Blakes Seven
Blakes Seven

Reputation: 50416

Yes. You can use "date math" with the objects instead and then they will be retained as BSON date types and translate to the native type in your driver:

pipeline = [
    { '$match': {
        "time_started": {
            "$gte": datetime.datetime(2015, 4, 21, 0, 0, 0),
            }
        }
     }},
     { "$group": {
        "_id": {
             "$add": [
                 { "$subtract": [
                     { "$subtract": [
                         "$time_started", datetime.datetime(1970, 1, 1) 
                     ]},
                     { "$mod": [
                         { "$subtract": [
                             "$time_started", datetime.datetime(1970, 1, 1) 
                         ]},
                         1000 * 60 * 60
                     ]}
                 ]},
                 datetime.datetime(1970, 1, 1)
             ]
        },
        "spent_total": { "$sum": "$spent" }
     }}
 ];

Class._get_collection().aggregate(pipeline);

Where the basic concept is that when you subtract the "epoch date" from the value of a date field then the value returned is numeric. Here you apply a modulo $mod to work out the remainder from the milliseconds in an hour and round the date down to the hour.

Then the reverse is true when you "add" the "epoch date" to the number, then it returns a new Date object equal to the represented value in milliseconds.

Since it is just a date already, the driver treats it accordingly and there is no need to translate. Much better than using strings or other operators. Also note, you don't need a $project and just apply such a conversion directly to the _id in $group, and that will even speed things up.

Upvotes: 2

Related Questions