Project Timestamp as Date or ISO Date in Aggregation/Find

How can we project a Timestamp value in a collection as Date or ISO either in Find or Aggregate operations?

For example, the below is how the collection is:

db.DateTimeTest.insertOne(
{
		ts1:new Date()
		,ts2: Date()
		,ts3: new ISODate()
		,ts4:new Timestamp()
}

)

   [
  {
    _id: ObjectId("61308a87c9ed16df15b18ced"),
    ts1: ISODate("2021-09-02T08:25:43.009Z"),
    ts2: 'Thu Sep 02 2021 13:55:43 GMT+0530 (India Standard Time)',
    ts3: ISODate("2021-09-02T08:25:43.009Z"),
    ts4: Timestamp({ t: 1630571144, i: 1 })
  }
]

But the timestamp is not user friendly. Application may have the capability to convert to a user friendly date to display. But, how can we do the conversion to a ISO date from mongo shell using Find or Aggregate?

Also, 2nd question, what are the two parts of the timestamp? the " t " and " i " below?

Timestamp({ t: 1630560549, i: 1 })

You probably want to use $convert or its short-hand syntax $toDate to convert Timestamp to ISODate, unfortunately, due to SERVER-32842 it’s not possible to do that directly.

However, the system will implicitly convert a timestamp to a date if the context requires a date. Here’s an example:

db.DateTimeTest.find({}, {ts4:{$dateToString:{date:"$ts4"}}})
[
  {
    _id: ObjectId("613121935ef77cda29eea62b"),
    ts4: '2021-09-02T19:10:11.000Z'
  }
]

Timestamp type is only meant to be used by MongoDB internally - the oplog uses it to track cluster time, where the first part t is timestamp (seconds since Epoch) and the second part (i) is a counter to make it unique when combined with the timestamp.

Thanks @Asya_Kamsky. If I understand correctly, using $convert and $toDate in aggregation is not possible as of now due to SERVER-32842. Is that right? For example, if I try the below, it shows the below result.

db.DateTimeTest.aggregate(
  [
    {
      $project:
        { 
          result: 
          {
            $convert: { 
              input: "$ts4", 
              to: "date",
              onError: "An error occurred",
              onNull: "Input was null or empty" 
            }
          }
        }
    }
  ]
)

[
  {
    _id: ObjectId("61308a87c9ed16df15b18ced"),
    result: 'An error occurred'
  },
  {
    _id: ObjectId("6130c68cc9ed16df15b18cef"),
    result: 'An error occurred'
  }
]

Thanks for the information about implicit conversion. In worst case, if we need the date in ISO Date format, we can first convert to string (implicitly) and then convert to ISO Date as needed.

db.DateTimeTest.aggregate(
  [
    {
      $project:
        { 
          ts4use:
			{
				$dateToString:{date:"$ts4"}
			}
        }
    },
	{
      $project:
        { 
          result: 
          {
            $convert: { 
              input: "$ts4use", 
              to: "date",
              onError: "An error occurred",
              onNull: "Input was null or empty" 
            }
          }
        }
    }
  ]
)
1 Like