convert iso date to timestamp in mongo query
Use $subtract
arithmetic aggregation operator with your Date as minuend and new Date("1970-01-01")
as subtrahend.
db.collection.aggregate(
{
$project: { "timestamp": { $subtract: [ "$createdAt", new Date("1970-01-01") ] } }
}
);
For document
{ "_id": 1, "createdAt": ISODate("2016-09-01T14:35:14.952Z") }
the result is
{ "_id": 1, "timestamp": NumberLong("1472740514952") }
If you want to group both by timestamp and (year, month, date) you can divide timestamp by the amount of milliseconds in a day, so that it will be unique for each day (and not for each millisecond)
db.collection.aggregate(
{
$project:
{
"timestampByDay":
{
$floor:
{
$divide:
[
{ $subtract: [ "$createdAt", new Date("1970-01-01") ] },
24 * 60 * 60 * 1000
]
}
},
"date": "$createdAt"
}
},
{
$group:
{
"_id": "$timestampByDay",
"date": { $first: "$date" }
}
}
);
Mongodb 4.0 has introduced $toLong
aggregation which convert date to timestamp
db.collection.aggregate([
{ "$project": {
"createdAt": {
"$toLong": "$createdAt"
}
}}
])
You can try it here