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

Starting in Mongo 5, your wish has been granted with the $dateTrunc operator.

For instance, to truncate dates to their year:

// { date: ISODate("2021-12-05T13:20:56Z") }
// { date: ISODate("2019-04-27T05:00:32Z") }
db.collection.aggregate([
  { $project: { year: { $dateTrunc: { date: "$date", unit: "year" } } } }
])
// { year: ISODate("2021-01-01T00:00:00Z") }
// { year: ISODate("2019-01-01T00:00:00Z") }

You can truncate at different levels of units (year, months, day, hours, ... even quarters) using the unit parameter. And for a given unit at different multiples of units (for instance 3 years, 6 months, ...) using the binSize parameter.

And you can also specify the day at which weeks start:

// { date: ISODate("2021-12-05T13:20:56Z") } <= Sunday
// { date: ISODate("2021-12-06T05:00:32Z") } <= Monday
db.collection.aggregate([
  { $project: {
    week: { $dateTrunc: { date: "$date", unit: "week", startOfWeek: "monday" } }
  }}
])
// { week: ISODate("2021-11-29T00:00:00Z") }
// { week: ISODate("2021-12-06T00:00:00Z") }