DEV Community

loading...
Cover image for MongoDB Marvels - Slicing Data by Dates - Series #14

MongoDB Marvels - Slicing Data by Dates - Series #14

functional_js profile image Functional Javascript Updated on ・2 min read

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"),
  )
Enter fullscreen mode Exit fullscreen mode

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 }
*/
Enter fullscreen mode Exit fullscreen mode

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.

Discussion (0)

Forem Open with the Forem app