How to group the records based upon the DateTime field

Low-code
, ,
#1

We have seen how to perform aggregation on a group of records

In this last post on Aggregation, we will see grouping the records based upon the DateTime field.

  • Group the records using groupBy filter and DateTime field.
  • dateGranularity of the field can be set to DAYS, HOURS, MINUTES, or SECONDS. It gives flexibility to capture trends over time.
  • With gets attached to the data type to form the groupBy query function.
  • If the data type is AggObj, use the function aggObjWith function using the groupBy clause.
  • groupValues provide the key and value of the groups. key is the grouping field with distinct values.
  • Hypi object has the dateTime fields like hypi_created or hypi_updated. You may group the records based upon these fields.

We will work with the same schema.

type AggObj {
    intFld: Int
    floatFld: Float
}

We have inserted a few records at different times in the above data type.

Sample Query

Let’s group the AggObj objects based upon the time of creation and calculate the average, sum and count of the values in the groups.

{
  aggregate {
    aggObjWith(groupBy: [{ field: hypi_created, dateGranularity: DAYS }]) {
      floatFld {
        count
        avg
        sum
        groupValues {
          key
          value
        }
      }
    }
  }
}
#result
{
  "data": {
    "aggregate": {
      "aggObjWith": [
        {
          "floatFld": {
            "count": 2,
            "avg": 1.2000000000000002,
            "sum": 2.4000000000000004,
            "groupValues": [
              {
                "key": "hypi_created",
                "value": "2021-11-16"
              }
            ]
          }
        },
        {
          "floatFld": {
            "count": 7,
            "avg": 1.2571428571428573,
            "sum": 8.8,
            "groupValues": [
              {
                "key": "hypi_created",
                "value": "2021-11-09"
              }
            ]
          }
        }
      ]
    }
  }
}

Check the POSTMAN collection for the DateTime aggregation query in different programming languages! Click </> and choose the programming language of your choice.

Don’t forget to insert your own Authorization key and Hypi Domain under Headers to test the results!

Run in Postman