.. meta:: :robots: noindex, nosnippet ======================== $dateTrunc (aggregation) ======================== .. default-domain:: mongodb .. contents:: On this page :local: :backlinks: none :depth: 1 :class: singlecol Definition ---------- .. expression:: $dateTrunc .. versionadded:: 5.0 Truncates a date. :expression:`$dateTrunc` syntax: .. code-block:: none :copyable: false { $dateTrunc: { date: , unit: , binSize: , timezone: , startOfWeek: } } .. list-table:: :header-rows: 1 :widths: 20 20 60 * - Field - Required / Optional - Description * - :ref:`date ` - Required - .. _dateTrunc-date: The date to truncate, specified in UTC. The :ref:`date ` can be any :ref:`expression ` that resolves to a :ref:`Date `, a :ref:`Timestamp `, or an :ref:`ObjectID `. * - :ref:`unit ` - Required - .. _dateTrunc-unit: The unit of time, specified as an :ref:`expression ` that must resolve to one of these strings: - ``year`` - ``quarter`` - ``week`` - ``month`` - ``day`` - ``hour`` - ``minute`` - ``second`` .. include:: /includes/dateTrunc-binSize-unit.rst * - :ref:`binSize ` - Optional - .. _dateTrunc-binSize: The numeric time value, specified as an :ref:`expression ` that must resolve to a positive non-zero number. Defaults to 1. .. include:: /includes/dateTrunc-binSize-unit.rst * - :ref:`timezone ` - Optional - .. _dateTrunc-timezone: The timezone for the :expression:`$dateTrunc` calculation, specified as an :ref:`expression ` that must resolve to a string that contains one of these values: - `Olson Timezone Identifier `_ - `UTC Offset `_ If no :ref:`timezone ` is provided, the :expression:`$dateTrunc` calculation is performed in UTC. .. list-table:: :header-rows: 1 :widths: 20 80 * - Format - Examples * - Olson Timezone Identifier - - ``America/New_York`` - ``Europe/London`` - ``GMT`` * - UTC Offset - - ``+/-[hh]:[mm]`` (example, ``+04:45``) - ``+/-[hh][mm]`` (example, ``-0530``) - ``+/-[hh]`` (example, ``+03``) * - :ref:`startOfWeek ` - Optional - .. _dateTrunc-startOfWeek: The start of the week. Used when :ref:`unit ` is ``week``. Defaults to ``Sunday``. :ref:`startOfWeek ` is an :ref:`expression ` that must resolve to one of these case insensitive strings: - ``monday`` (or ``mon``) - ``tuesday`` (or ``tue``) - ``wednesday`` (or ``wed``) - ``thursday`` (or ``thu``) - ``friday`` (or ``fri``) - ``saturday`` (or ``sat``) - ``sunday`` (or ``sun``) .. seealso:: - :ref:`aggregation-expressions` - :ref:`bson-types` Behavior -------- :expression:`$dateTrunc`: - Returns ``null`` if: - any of the input fields except :ref:`startOfWeek ` is missing or set to ``null``, or - if :ref:`unit ` is ``week`` and :ref:`startOfWeek ` is missing or set to ``null``. - Uses the :wikipedia:`proleptic Gregorian calendar ` for dates preceding the year 1583. - Accounts for Daylight Savings Time, but does not account for :wikipedia:`leap seconds `. ``binSize`` and ``unit`` Fields ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ .. include:: /includes/dateTrunc-binSize-unit.rst For example: - If :ref:`binSize ` is ``1`` and :ref:`unit ` is ``hour``, the time period is one hour. For the :ref:`date ` ``2021-03-20T11:30:05Z``, :expression:`$dateTrunc` returns ``2021-03-20T11:00:00Z``. - If :ref:`binSize ` is ``2`` and :ref:`unit ` is ``hour``, the time period is two hours. For the :ref:`date ` ``2021-03-20T11:30:05Z``, :expression:`$dateTrunc` returns ``2021-03-20T10:00:00Z``. :expression:`$dateTrunc`: - Divides the time for the :expression:`$dateTrunc` calculation into :ref:`binSize ` time periods in the specified time :ref:`unit `. The time periods start at a reference date, which is determind by :ref:`unit `. If :ref:`unit ` is: - A string other than ``week``, :expression:`$dateTrunc` uses a reference date of ``2000-01-01T00:00:00.00Z``. For example, if :ref:`binSize ` is ``10`` and :ref:`unit ` is ``year``, example time periods are: - ``2000-01-01T00:00:00.00Z`` - ``2010-01-01T00:00:00.00Z`` - ``2020-01-01T00:00:00.00Z`` - Equal to ``week``, :expression:`$dateTrunc` uses a reference date that is set to the earliest first day of the week that is greater than or equal to ``2000-01-01``. The first day is set using :ref:`startOfWeek ` (the default is Sunday). - Returns the lower boundary of the time period that the :ref:`date ` is in. The boundary is returned as an :term:`ISODate`. If the :ref:`binSize ` field is ``1``, :expression:`$dateTrunc` sets the least significant parts (as determined by :ref:`unit `) of the returned :term:`ISODate` to ``0`` and keeps the rest of the :term:`ISODate` the same. If :ref:`unit ` is: - ``year``: :expression:`$dateTrunc` returns the :term:`ISODate` for the start of January 1 for the year in :ref:`date `. - ``quarter``: :expression:`$dateTrunc` returns the :term:`ISODate` for the start of the first day of the calendar quarter in :ref:`date `. The quarters are: - January to March - April to June - July to September - October to December - ``month``: :expression:`$dateTrunc` returns the :term:`ISODate` for the start of the first day of the month in :ref:`date `. - ``week``: :expression:`$dateTrunc` returns the :term:`ISODate` for the start of the :ref:`startOfWeek ` day in :ref:`date `. The default for :ref:`startOfWeek ` is Sunday. - ``day``: :expression:`$dateTrunc` returns the :term:`ISODate` for the start of the day in :ref:`date `. - ``hour``: :expression:`$dateTrunc` returns the :term:`ISODate` for the start of the hour in :ref:`date `. - ``minute``: :expression:`$dateTrunc` returns the :term:`ISODate` for the start of the minute in :ref:`date `. - ``second``: :expression:`$dateTrunc` returns the :term:`ISODate` for start of the second in :ref:`date `. ``unit`` and ``startOfWeek`` Fields ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ If :ref:`unit ` is: - A string other than ``week``, :ref:`startOfWeek ` is ignored. - Equal to ``week`` and :ref:`startOfWeek ` is: - Specified: :expression:`$dateTrunc` uses :ref:`startOfWeek ` as the first day of the week for the calculation. - Omitted: :expression:`$dateTrunc` uses Sunday as the start of the week for the calculation. Examples -------- .. include:: /includes/cakeSales-example-collection.rst The ``cakeSales`` collection is used in the following examples. Truncate Order Dates in a ``$project`` Pipeline Stage ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ This example uses :expression:`$dateTrunc` in a :pipeline:`$project` stage to truncate the cake sales ``orderDate`` values to two weeks: .. code-block:: javascript db.cakeSales.aggregate( [ { $project: { _id: 1, orderDate: 1, truncatedOrderDate: { $dateTrunc: { date: "$orderDate", unit: "week", binSize: 2, timezone: "America/Los_Angeles", startOfWeek: "Monday" } } } } ] ) In the example: - ``$project`` includes the ``_id``, ``orderDate``, and ``truncatedOrderDate`` fields in the output. - ``$dateTrunc`` truncates the ``orderDate`` field to a ``2`` :ref:`binSize ` ``week`` :ref:`unit ` time period in the ``America/Los_Angeles`` :ref:`timezone ` with :ref:`startOfWeek ` set to ``Monday``. In this example output, the truncated ``orderDate`` is shown in the ``truncatedOrderDate`` field: .. code-block:: javascript :copyable: false [ { _id: 0, orderDate: ISODate("2020-05-18T14:10:30.000Z"), truncatedOrderDate: ISODate("2020-05-11T07:00:00.000Z") }, { _id: 1, orderDate: ISODate("2021-03-20T11:30:05.000Z"), truncatedOrderDate: ISODate("2021-03-15T07:00:00.000Z") }, { _id: 2, orderDate: ISODate("2021-01-11T06:31:15.000Z"), truncatedOrderDate: ISODate("2021-01-04T08:00:00.000Z") }, { _id: 3, orderDate: ISODate("2020-02-08T13:13:23.000Z"), truncatedOrderDate: ISODate("2020-02-03T08:00:00.000Z") }, { _id: 4, orderDate: ISODate("2019-05-18T16:09:01.000Z"), truncatedOrderDate: ISODate("2019-05-13T07:00:00.000Z") }, { _id: 5, orderDate: ISODate("2019-01-08T06:12:03.000Z"), truncatedOrderDate: ISODate("2019-01-07T08:00:00.000Z") } ] Truncate Order Dates and Obtain Quantity Sum in a ``$group`` Pipeline Stage ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ This example uses :expression:`$dateTrunc` in a :pipeline:`$group` stage to truncate the cake sales ``orderDate`` values to six months and return the sum of the ``quantity`` values: .. code-block:: javascript db.cakeSales.aggregate( [ { $group: { _id: { truncatedOrderDate: { $dateTrunc: { date: "$orderDate", unit: "month", binSize: 6 } } }, sumQuantity: { $sum: "$quantity" } } } ] ) In the example: - ``$group`` has the ``_id`` field set to the ``truncatedOrderDate`` field to group the ``cakeSales`` documents, and returns the sum of the ``quantity`` values for each group using :group:`$sum`. - ``$dateTrunc`` truncates the ``orderDate`` field to a ``6`` :ref:`binSize ` ``month`` :ref:`unit ` time period. In this example output, the truncated ``orderDate`` is shown in the ``truncatedOrderDate`` field and the ``quantity`` sum is shown in the ``sumQuantity`` field: .. code-block:: javascript :copyable: false [ { _id: { truncatedOrderDate: ISODate("2020-01-01T00:00:00.000Z") }, sumQuantity: 224 }, { _id: { truncatedOrderDate: ISODate("2021-01-01T00:00:00.000Z") }, sumQuantity: 285 }, { _id: { truncatedOrderDate: ISODate("2019-01-01T00:00:00.000Z") }, sumQuantity: 296 } ]