Aggregate trunc date function?

Question

I worked with a bunch of SQL databases before; like Postgres and BigQuery and they have date truncation function (for instance: date_trunc or TIMESTAMP_TRUNC ).

I wonder if mongodb has a DATE_TRUNC function?

I have found the $trunc operator but it works for numbers only.

I want a DATE_TRUNC function to truncate a given Date (the timestamp type in other SQL databases) to a particular boundary, like beginning of year, beginning of month, beginning of hour, may be ok to compose a new Date by getting its year, month, date, hour.

Does someone have some kinds of workaround? Especially for beginning moment of WEEK, and beginning of ISOWEEK, does anyone have a good workaround?

Answer 1

Its possible to get the start of ISO week by doing arithmetic on date or timestamp field, here the start of week is Monday (1) and end of week is Sunday (7)

db.dd.aggregate(
    [
        {
            $addFields : { 
                startOfWeek : 1, // Monday
                currentDayOfWeek : {$dayOfWeek : "$date"}, 
                daysToMinus : { $subtract : [{$dayOfWeek : "$date"} , 1] },
                startOfThisWeek : { $subtract : [ "$date", {$multiply : [{ $subtract : [{$dayOfWeek : "$date"} , 1 ] }, 24, 60, 60, 1000 ]  } ] }
            }
        }
    ]
).pretty()

document

> db.dd.find()
{ "_id" : ObjectId("5a62e2697702c6be61d672f4"), "date" : ISODate("2018-01-20T06:32:09.157Z") }

start of week

{
    "_id" : ObjectId("5a62e2697702c6be61d672f4"),
    "date" : ISODate("2018-01-20T06:32:09.157Z"),
    "startOfWeek" : 1,
    "currentDayOfWeek" : 7,
    "daysToMinus" : 6,
    "startOfThisWeek" : ISODate("2018-01-14T06:32:09.157Z")
}
>