MongoDB Marvels - Slicing Data by Dates - Series #14

Intro

Let's look at a simple query first as we prep ourselves for more thorough queries in this Series:

Query for how many user actions per hour block.
This will report which hour blocks the site is busiest.

  mgArr(dbEnum.nlpdb, collEnum.users_actions,
    {
      $addFields: {
        hourUtc: {
          $hour: { date: "$_id" }
        },
        hourPst: {
          $hour: { date: "$_id", timezone: "-08:00" },
        },
      },
    },
    groupByKey("hourPst"),
    sortDesc("count"),
  )

Notes

The server timestamp is in UTC Time (Coordinated Universal Time).

The db query code also converts the timezone from UTC to PST.
(In another post we'll extract the user's timezone from their device.)

The query has 3 stages:

The $addFields stage adds two computed fields to our resultset, "hourUtc" and "hourPst". (Just to show an example of how to get dates without and with an offset)

The Group stage will pivot on the hour component of the date, for the PST timezone.

Sort Descending, so we see the busiest hours at the top.

We will get back 24 records (24 hours in each day), so we don't need to Paginate.

The output:

/*
 { count: 610, hourPst: 15 },
  { count: 58, hourPst: 20 },
  { count: 55, hourPst: 21 },
  { count: 51, hourPst: 23 },
  { count: 49, hourPst: 14 },
  { count: 41, hourPst: 10 },
  { count: 38, hourPst: 13 },
  { count: 34, hourPst: 22 },
  { count: 33, hourPst: 18 },
  { count: 32, hourPst: 16 },
  { count: 27, hourPst: 17 },
  { count: 24, hourPst: 11 },
  { count: 23, hourPst: 0 },
  { count: 17, hourPst: 19 },
  { count: 17, hourPst: 1 },
  { count: 15, hourPst: 12 },
  { count: 9, hourPst: 3 },
  { count: 5, hourPst: 2 },
  { count: 5, hourPst: 8 },
  { count: 3, hourPst: 6 }
*/

Notes

Looks like 3:00:00 to 3:59:59 PM is very busy for some reason. :)

Though this is an example query, you can see how you can get meaningful and sometimes surprising insights into your data by carving it up into timeseries dimensions.

What's Next

We'll cover more power with dates and time series in this Series of Articles.

As always, if you have an questions or input, let me know.