MongoDB $second

In MongoDB, the $second aggregation pipeline operator returns the second portion of a date as a number between 0 and 59. The value can also be 60 to account for leap seconds whenever applicable.

You can optionally specify a timezone to use for the result.

The $second operator accepts either a date (as either a Date, a Timestamp, or an ObjectId), or a document that specifies the date and timezone to use.

Example

Suppose we have a collection called pets with the following document:

{
	"_id" : ObjectId("600631c7c8eb4369cf6ad9c8"),
	"name" : "Fetch",
	"born" : ISODate("2020-12-31T23:30:15.123Z")
}

We can run the following code to extract the second portion from the born field in that document.

db.pets.aggregate(
  [
    {
      $project:
        {
          _id: 0,
          birthSecond: { $second: "$born" }
        }
    }
  ]
)

Result:

{ "birthSecond" : 15 }

Here, I used birthSecond as the field name to return, but this could have been anything (such as secondsAtBirth, seconds, etc).

The _id field is returned by default when using projections in MongoDB, but in this example I explicitly hid the _id field using _id: 0.

Specify a Timezone

You can specify a timezone to use for the output of the $second operator.

When you do this, the argument passed to $second must be of the following form:

{ date: <dateExpression>, timezone: <tzExpression> }

Where <dateExpression> is the date to use, and <tzExpression> is the timezone to use.

The timezone can be specified using either the Olson timezone identifier (e.g. "Europe/London", "GMT") or the UTC offset (e.g. "+02:30", "-1030").

Olson Timezone Identifier

Here’s an example that outputs the seconds in two different timezones, each using the Olson timezone IDs:

db.pets.aggregate(
  [
    {
      $project: {
          _id: 0,
          kabul: { 
            $second: { date: "$born", timezone: "Asia/Kabul" }
            },
          brisbane: { 
            $second: { date: "$born", timezone: "Australia/Brisbane" }
            }
        }
    }
  ]
)

Result:

{ "kabul" : 15, "brisbane" : 15 } 

Seeing as the timezone change doesn’t affect the seconds portion, the result is the same.

UTC Offset

In this example we use the UTC offset.

db.pets.aggregate(
  [
    {
      $project: {
          _id: 0,
          "utcOffset+04:30": { 
            $second: { date: "$born", timezone: "+04:30" }
            },
          "utcOffset+05:45": { 
            $second: { date: "$born", timezone: "+05:45" }
            }
        }
    }
  ]
)

Result:

{ "utcOffset+04:30" : 15, "utcOffset+05:45" : 15 }

Return the Seconds from an ObjectId

You can use $second to return the seconds portion from an ObjectId.

ObjectId values are 12 byte hexadecimal values that consist of:

• A 4 byte timestamp value, representing the ObjectId’s creation, measured in seconds since the Unix epoch.
• A 5 byte is a random value
• A 3 byte incrementing counter, initialised to a random value.

To recap, our document looks like this:

{
	"_id" : ObjectId("600631c7c8eb4369cf6ad9c8"),
	"name" : "Fetch",
	"born" : ISODate("2020-12-31T23:30:15.123Z")
}

This document contains an ObjectId. We can therefore use $second to return the second that our document was created (or more specifically, when the _id field’s ObjectId value was created).

Example:

db.pets.aggregate(
  [
    {
      $project:
        {
          "timeStamp": { $toDate: "$_id"},
          "second": { $second: "$_id" }
        }
    }
  ]
).pretty()

Result:

{
	"_id" : ObjectId("600631c7c8eb4369cf6ad9c8"),
	"timeStamp" : ISODate("2021-01-19T01:11:35Z"),
	"second" : 35
}

We can see that the document was created on the 35th second (of the 11th minute of the 1st hour of the day).

In this case, I also used the $toDate aggregation pipeline operator to return the timestamp portion of the ObjectId.