How to translate this SQL to MongoDB (GoLang)

user_4232577 0
Viewed 38

I have this SQL query:

SELECT SUM(TIME_TO_SEC(TIMEDIFF(IFNULL(userpresences.datefinished, NOW()), userpresences.dateadded))) FROM userpresences WHERE clientid = 12 GROUP BY userpresences.presenceid

Is there a way that I can translate this to MongoDB query? I am having trouble with Aggregate and group parameter.

Here's Go code:

groupStage := bson.D{
        {"$group", bson.D{
            {"_id", "$presenceid"},
            {"count", bson.M{"$sum": "$datefinished"}},
        }},
    }

This count should return that SUM(...) from MySQL..

go mongodb
1 Answers

I managed to figure it out somehow.

Here's the full code in case anybody else might need it:

currentTime := time.Now()
matchStage := bson.D{
        {"$match", bson.D{
            {"clientid", clientID},
        }},
    }

    ifNullDateFinished := bson.M{"$ifNull": []interface{}{"$datefinished", currentTime}}
    dateFinishedToSec := bson.M{"$add": []bson.M{
        {"$second": ifNullDateFinished},
        {"$multiply": []interface{}{bson.M{"$minute": ifNullDateFinished}, 60}},
        {"$multiply": []interface{}{bson.M{"$hour": ifNullDateFinished}, 3600}},
    }}

    dateAddedToSec := bson.M{"$add": []bson.M{
        {"$second": "$dateadded"},
        {"$multiply": []interface{}{bson.M{"$minute": "$dateadded"}, 60}},
        {"$multiply": []interface{}{bson.M{"$hour": "$dateadded"}, 3600}},
    }}

    groupStage := bson.D{
        {"$group", bson.D{
            {"_id", "$presenceid"},
            {"count", bson.M{"$sum": bson.M{"$subtract": []interface{}{dateFinishedToSec, dateAddedToSec}}}},
        }},
    }
user_42325770
Related