NRQL: Group results across time

With NRQL, you can create queries that group results across time. For example, you can group results together based on timestamps by separating them into buckets that cover a specified range of dates and times.

When using time functions in NRQL queries, the results are returned in UTC. To adjust the results to your time zone, include the WITH TIMEZONE clause in your query.

Facet your NRQL query time range

To create your NRQL query, use a FACET clause with a bucket function that works with a timestamp attribute. Run a standard FACET query, but instead of faceting by an attribute, facet by time. For example:

SELECT count(*) FROM PageView SINCE 1 day ago FACET monthOf(account_created)

To perform multiple functions within the same query, use NRQL's multi-facet capability:

SELECT count(*) FROM PageView SINCE 1 day ago FACET dateOf(account_created), monthOf(account_created)

Time-based functions	Description
`yearOf(attr)`	Returns the year of a timestamp.
`quarterOf(attr)`	Returns the quarter of the year. The returned value includes both the quarter and the year. Example: `Q1 2014`
`monthOf(attr)`	Returns the month and year of the timestamp. Example: `July 2014`
`weekOf(attr)`	Returns the week the timestamp occurred by naming the month and day of that week's Monday. Example: `Week of January 15`.
`weekdayOf(attr)`	Returns the day of the week of the timestamp. The returned value loops back at the end of the week, allowing you to look at trends by weekday over time.
`dateOf(attr)`	Returns the date of the timestamp. The returned value includes month, day and year. Example: `July 15, 2014`
`dayOfMonthOf(attr)`	Returns the numeric date within a single month of the timestamp, a value from 1 to 31. The returned value does not include the month.
`hourOf(attr)`	Returns the hour of the timestamp. The returned value does not include a prepended 0 for hours between 1am and 9am. This differs from functions and clauses such as `SINCE`, which accept these hours with a 0 at the start. Examples: `6:00`, `12:00`, `18:00`

To group all results based on the month, use the monthOf function. In this example, the NRQL query includes a function (count(*)), a data type (PageView), a time frame (SINCE 1 day ago), and a time facet (monthOf(attribute)).

SELECT count(*) FROM PageView SINCE 1 day ago FACET monthOf(account_created)

Running the query returns a table of results by month.

You need to be aware of UNTIL you add the TIMESERIES function and use the time function in a time series chart. The default value of UNTIL is NOW, so if you do not specify anything, the values of the time functions may be separated or combined. By specifying UNTIL today, you can create a chart that ends at 12:00 AM on the same day.

SELECT count(*) FROM PageView TIMESERIES 1 day  WITH TIMEZONE '<localTimeZone>' SINCE 4 week ago UNTIL today

If you want to visualize data from last month instead of the past four weeks, you can use since last month until this month.

SELECT count(*) FROM PageView TIMESERIES 1 day  WITH TIMEZONE '<localTimeZone>' since last month until this month

Facet your NRQL query time range

Group results by month

Other grouping examples with FACET clause

Example of creating a chart by specifying TIMESERIES