How to Convert Timestamp Into Date in MongoDB
- Convert Timestamp Into Date in MongoDB
- Convert Timestamp Into Date When Timestamp Is of Type Number
- Convert Timestamp Into Date When Timestamp Is of Type String
- Convert Timestamp Into Date When Timestamp Is of Type Object
This tutorial demonstrates how we can convert the timestamp into date in MongoDB. It also illustrates how to count the entries for a specific date.
Convert Timestamp Into Date in MongoDB
Converting from timestamp into date depends on what type we have saved the timestamp. Is it of the type object, number, or string?
We can check the type of field using the following command on the mongo shell. In this tutorial, we will learn how to convert the timestamp to date if it is of type number, string, or object.
Check Type of the Field:
// MongoDB 5.0.8
> typeof db.collection_name.findOne().fieldName;
Convert Timestamp Into Date When Timestamp Is of Type Number
Example Code (for collection1
):
// MongoDB 5.0.8
> db.collection1.insertMany([
{"_id": 1, "datetime": new Date().getTime()}, //saves timestamp in milliseconds
{"_id": 2, "datetime": new Date().getTime()},
{"_id": 3, "datetime": new Date().getTime()},
{"_id": 4, "datetime": new Date().getTime()},
{"_id": 5, "datetime": new Date().getTime()}
]);
> db.collection1.find();
OUTPUT:
{ "_id" : 1, "datetime" : 1655448286502 }
{ "_id" : 2, "datetime" : 1655448286502 }
{ "_id" : 3, "datetime" : 1655448286502 }
{ "_id" : 4, "datetime" : 1655448286502 }
{ "_id" : 5, "datetime" : 1655448286502 }
Check Type of the datetime
Field:
// MongoDB 5.0.8
> typeof db.collection1.findOne().datetime;
OUTPUT:
number
Once the collection is ready and we know the field type, we can use the following approach to convert from timestamp to date and count the entries per date.
Example Code (for collection1
):
// MongoDB 5.0.8
> db.collection1.aggregate([
{
"$project": {
"_id": { "$toDate": "$datetime" }
}
},
{
"$group": {
"_id": { "$dateToString": { "format": "%Y-%m-%d", "date": "$_id" }},
"count": { "$sum": 1 }
}
}
]);
OUTPUT:
{ "_id" : "2022-06-17", "count" : 5 }
Here, we use the $project
aggregation stage that takes the documents from the specified collection and tells the fields’ inclusion, _id
field’s suppression, new fields’ addition, and resetting the existing fields’ values.
Inside the $project
stage, we convert the value of the datetime
field to date using the $toDate
aggregation and save it in the _id
field, which is further passed to the $group
aggregation stage.
At this stage, we use the $dateToString
aggregation pipeline operator to convert the specified date
object to string according to the specified format and save it in the _id
field, which is further used to group the documents.
The $dateToString
takes either timestamp
, date
, or ObjectId
that is further transformed considering the user-specified format, while $sum
returns the collective sum of the numeric values only.
Finally, we group the documents by the item, which is _id
here. Remember that the _id
now contains a string value because we converted the specified date to a string per user-specified format.
Convert Timestamp Into Date When Timestamp Is of Type String
Example Code (for collection2
):
// MongoDB 5.0.8
> db.collection2.insertMany([
{"_id": 1, "datetime": "1655445247168"},
{"_id": 2, "datetime": "1522838153324"},
{"_id": 3, "datetime": "1513421466415"},
{"_id": 4, "datetime": "1515488183153"},
{"_id": 5, "datetime": "1521571234500"}
]);
> db.collection2.find();
OUTPUT:
{ "_id" : 1, "datetime" : "1655445247168" }
{ "_id" : 2, "datetime" : "1522838153324" }
{ "_id" : 3, "datetime" : "1513421466415" }
{ "_id" : 4, "datetime" : "1515488183153" }
{ "_id" : 5, "datetime" : "1521571234500" }
Check Type of the datetime
Field:
// MongoDB 5.0.8
> typeof db.collection2.findOne().datetime;
OUTPUT:
string
In this collection, we have the timestamp in the string format. So, we can use the following solution to convert it from timestamp to date and group them as per date.
Example Code (for collection2
):
// MongoDB 5.0.8
> db.collection2.aggregate([
{
"$project": {
"_id": { "$toDate": { "$toLong": "$datetime" }}
}
},
{
"$group": {
"_id": { "$dateToString": { "format": "%Y-%m-%d", "date": "$_id" } },
"count": { "$sum": 1 }
}
}
]);
OUTPUT:
{ "_id" : "2018-03-20", "count" : 1 }
{ "_id" : "2017-12-16", "count" : 1 }
{ "_id" : "2022-06-17", "count" : 1 }
{ "_id" : "2018-04-04", "count" : 1 }
{ "_id" : "2018-01-09", "count" : 1 }
This code is the same as the previous example except for one difference. Here, we are using $toLong
to convert the datetime
field from string to number type first, and then use that converted value to transform to date using $toDate
.
Convert Timestamp Into Date When Timestamp Is of Type Object
Example Code (for collection3
):
// MongoDB 5.0.8
> db.collection3.insertMany([
{"_id":1, "datetime": new Timestamp()},
{"_id":2, "datetime": new Timestamp()},
{"_id":3, "datetime": new Timestamp()},
{"_id":4, "datetime": new Timestamp()},
{"_id":5, "datetime": new Timestamp()}
]);
> db.collection3.find();
OUTPUT:
{ "_id" : 1, "datetime" : Timestamp(1655448393, 1) }
{ "_id" : 2, "datetime" : Timestamp(1655448393, 2) }
{ "_id" : 3, "datetime" : Timestamp(1655448393, 3) }
{ "_id" : 4, "datetime" : Timestamp(1655448393, 4) }
{ "_id" : 5, "datetime" : Timestamp(1655448393, 5) }
Check Type of the datetime
Field:
// MongoDB 5.0.8
> typeof db.collection3.findOne().datetime;
OUTPUT:
object
This time we can use the following solution to convert timestamp to date and count the entries per date.
Example Code (for collection3
):
// MongoDB 5.0.8
> db.collection3.aggregate([
{
"$project": { "_id": "$datetime" }
},
{
"$group": {
"_id": { "$dateToString": { "format": "%Y-%m-%d", "date": "$_id" } },
"count": { "$sum": 1 }
}
}
]);
OUTPUT:
{ "_id" : "2022-06-17", "count" : 5 }