Convert seconds to minutes in MongoDB

Here are some examples of conversions from seconds to minutes using MongoDB's aggregation pipeline framework.

Some arithmetic operators used in the following examples require MongoDB version 3.2 or higher.

Version used:

> db.version()
3.2.1

First, insert some random data:

> docs = [];
[ ]
> for (i=0; i<10; i++) docs.push({seconds: (Math.random()*1000)|0});
10
> docs
[
    {
        "seconds" : 425
    },
    {
        "seconds" : 12
    },
    {
        "seconds" : 213
    },
    {
        "seconds" : 474
    },
    {
        "seconds" : 143
    },
    {
        "seconds" : 254
    },
    {
        "seconds" : 299
    },
    {
        "seconds" : 768
    },
    {
        "seconds" : 102
    },
    {
        "seconds" : 558
    }
]
> db.times.insertMany(docs);
{
    "acknowledged" : true,
    "insertedIds" : [
        ObjectId("575f51593e04b847286b2bb6"),
        ObjectId("575f51593e04b847286b2bb7"),
        ObjectId("575f51593e04b847286b2bb8"),
        ObjectId("575f51593e04b847286b2bb9"),
        ObjectId("575f51593e04b847286b2bba"),
        ObjectId("575f51593e04b847286b2bbb"),
        ObjectId("575f51593e04b847286b2bbc"),
        ObjectId("575f51593e04b847286b2bbd"),
        ObjectId("575f51593e04b847286b2bbe"),
        ObjectId("575f51593e04b847286b2bbf")
    ]
}

Just seconds to minutes:

> db.times.aggregate([
    {
        $project: {
            seconds: 1,
            minutes: { $divide: [ "$seconds", 60 ] }
        }
    }
]);
{ "_id" : ObjectId("575f51593e04b847286b2bb6"), "seconds" : 425, "minutes" : 7.083333333333333 }
{ "_id" : ObjectId("575f51593e04b847286b2bb7"), "seconds" : 12, "minutes" : 0.2 }
{ "_id" : ObjectId("575f51593e04b847286b2bb8"), "seconds" : 213, "minutes" : 3.55 }
{ "_id" : ObjectId("575f51593e04b847286b2bb9"), "seconds" : 474, "minutes" : 7.9 }
{ "_id" : ObjectId("575f51593e04b847286b2bba"), "seconds" : 143, "minutes" : 2.3833333333333333 }
{ "_id" : ObjectId("575f51593e04b847286b2bbb"), "seconds" : 254, "minutes" : 4.233333333333333 }
{ "_id" : ObjectId("575f51593e04b847286b2bbc"), "seconds" : 299, "minutes" : 4.983333333333333 }
{ "_id" : ObjectId("575f51593e04b847286b2bbd"), "seconds" : 768, "minutes" : 12.8 }
{ "_id" : ObjectId("575f51593e04b847286b2bbe"), "seconds" : 102, "minutes" : 1.7 }
{ "_id" : ObjectId("575f51593e04b847286b2bbf"), "seconds" : 558, "minutes" : 9.3 }

Completed minutes:

> db.times.aggregate([
    {
        $project: {
            seconds: 1,
            minutes: { $floor: { $divide: [ "$seconds", 60 ] } }
        }
    }
]);
{ "_id" : ObjectId("575f51593e04b847286b2bb6"), "seconds" : 425, "minutes" : 7 }
{ "_id" : ObjectId("575f51593e04b847286b2bb7"), "seconds" : 12, "minutes" : 0 }
{ "_id" : ObjectId("575f51593e04b847286b2bb8"), "seconds" : 213, "minutes" : 3 }
{ "_id" : ObjectId("575f51593e04b847286b2bb9"), "seconds" : 474, "minutes" : 7 }
{ "_id" : ObjectId("575f51593e04b847286b2bba"), "seconds" : 143, "minutes" : 2 }
{ "_id" : ObjectId("575f51593e04b847286b2bbb"), "seconds" : 254, "minutes" : 4 }
{ "_id" : ObjectId("575f51593e04b847286b2bbc"), "seconds" : 299, "minutes" : 4 }
{ "_id" : ObjectId("575f51593e04b847286b2bbd"), "seconds" : 768, "minutes" : 12 }
{ "_id" : ObjectId("575f51593e04b847286b2bbe"), "seconds" : 102, "minutes" : 1 }
{ "_id" : ObjectId("575f51593e04b847286b2bbf"), "seconds" : 558, "minutes" : 9 }

Rounded minutes (no $round operator in MongoDB):

> db.times.aggregate([
    {
        $project: {
            seconds: 1,
            minutes: {
                $divide: [
                    {
                        $subtract: [
                            { $add: [ 30, "$seconds" ] },
                            { $mod: [ { $add: [ 30, "$seconds" ] }, 60 ] }
                        ]
                    },
                    60
                ]
            }
        }
    }
]);
{ "_id" : ObjectId("575f51593e04b847286b2bb6"), "seconds" : 425, "minutes" : 7 }
{ "_id" : ObjectId("575f51593e04b847286b2bb7"), "seconds" : 12, "minutes" : 0 }
{ "_id" : ObjectId("575f51593e04b847286b2bb8"), "seconds" : 213, "minutes" : 4 }
{ "_id" : ObjectId("575f51593e04b847286b2bb9"), "seconds" : 474, "minutes" : 8 }
{ "_id" : ObjectId("575f51593e04b847286b2bba"), "seconds" : 143, "minutes" : 2 }
{ "_id" : ObjectId("575f51593e04b847286b2bbb"), "seconds" : 254, "minutes" : 4 }
{ "_id" : ObjectId("575f51593e04b847286b2bbc"), "seconds" : 299, "minutes" : 5 }
{ "_id" : ObjectId("575f51593e04b847286b2bbd"), "seconds" : 768, "minutes" : 13 }
{ "_id" : ObjectId("575f51593e04b847286b2bbe"), "seconds" : 102, "minutes" : 2 }
{ "_id" : ObjectId("575f51593e04b847286b2bbf"), "seconds" : 558, "minutes" : 9 }

Minutes and remaining seconds:

> db.times.aggregate([
    {
        $project: {
            minutes: { $floor: { $divide: [ "$seconds", 60 ] } },
            seconds: { $mod: ["$seconds", 60] }
        }
    }
]);
{ "_id" : ObjectId("575f51593e04b847286b2bb6"), "seconds" : 5, "minutes" : 7 }
{ "_id" : ObjectId("575f51593e04b847286b2bb7"), "seconds" : 12, "minutes" : 0 }
{ "_id" : ObjectId("575f51593e04b847286b2bb8"), "seconds" : 33, "minutes" : 3 }
{ "_id" : ObjectId("575f51593e04b847286b2bb9"), "seconds" : 54, "minutes" : 7 }
{ "_id" : ObjectId("575f51593e04b847286b2bba"), "seconds" : 23, "minutes" : 2 }
{ "_id" : ObjectId("575f51593e04b847286b2bbb"), "seconds" : 14, "minutes" : 4 }
{ "_id" : ObjectId("575f51593e04b847286b2bbc"), "seconds" : 59, "minutes" : 4 }
{ "_id" : ObjectId("575f51593e04b847286b2bbd"), "seconds" : 48, "minutes" : 12 }
{ "_id" : ObjectId("575f51593e04b847286b2bbe"), "seconds" : 42, "minutes" : 1 }
{ "_id" : ObjectId("575f51593e04b847286b2bbf"), "seconds" : 18, "minutes" : 9 }

Formatted time:

> db.times.aggregate([
    {
        $project: {
            time: {
                $let: {
                    vars: {
                        minutes: { $floor: { $divide: [ "$seconds", 60 ] } },
                        seconds: { $mod: [ "$seconds", 60 ] }
                    },
                    in: {
                        $concat: [
                            // integer to string
                            { $substr: [ "$$minutes", 0, -1 ] },
                            ':',
                            { $cond: [
                                { $lt: [ "$$seconds", 10 ] },
                                // integer to string and zero padding
                                { $concat: [ "0", { $substr: [ "$$seconds", 0, -1 ] } ] },
                                // integer to string
                                { $substr: [ "$$seconds", 0, -1 ] }
                            ] }
                        ]
                    }
                }
            }
        }
    }
]);
{ "_id" : ObjectId("575f51593e04b847286b2bb6"), "time" : "7:05" }
{ "_id" : ObjectId("575f51593e04b847286b2bb7"), "time" : "0:12" }
{ "_id" : ObjectId("575f51593e04b847286b2bb8"), "time" : "3:33" }
{ "_id" : ObjectId("575f51593e04b847286b2bb9"), "time" : "7:54" }
{ "_id" : ObjectId("575f51593e04b847286b2bba"), "time" : "2:23" }
{ "_id" : ObjectId("575f51593e04b847286b2bbb"), "time" : "4:14" }
{ "_id" : ObjectId("575f51593e04b847286b2bbc"), "time" : "4:59" }
{ "_id" : ObjectId("575f51593e04b847286b2bbd"), "time" : "12:48" }
{ "_id" : ObjectId("575f51593e04b847286b2bbe"), "time" : "1:42" }
{ "_id" : ObjectId("575f51593e04b847286b2bbf"), "time" : "9:18" }