NQL datetime functions

The datetime functions return specific time components, such as hour, day of the week, or day of the month, from the timestamp fields. This enables you to identify patterns or trends within time windows for example, business hours, business week, or specific days of the month.

Example

Retrieve device performance data for the business hours within the last 24 hours.

device_performance.events during past 24h
| where start_time.hour() >= 9 and end_time.hour() <= 17

Available functions

hour()

Description: This function allows you to extract the hour from a given timestamp.

Returns: Numbers from 0 to 23.

NQL query example: View all events that happened during business hours (e.g., between 9 am and 5 pm)

device_performance.events during past 24h
| where start_time.hour() >= 9 and end_time.hour() <= 17

Note that to extract an hour from the date, you need to use a timeframe expressed in minutes or hours in your query (for example, use during past 168h instead of during past 7d).

day()

Description: This function allows you to extract the day of the month from a given date.

Returns: Numbers from 1 to 31.

NQL query example: Retrieve device performance data from the first week of the month.

device_performance.events during past 30d
| where start_time.day() >= 1 and end_time.day() <= 7

day_of_week()

Description: This function allows you to extract the day of the week from a given date.

Returns: Numbers from 1 to 7, where 1 represents Monday and 7 represents Sunday.

NQL query example: Retrieve device performance data from the working days (e.g., Monday to Friday)

device_performance.events during past 30d
| where start_time.day_of_week() >= 1 and end_time.day_of_week() < 6

Time zone parameter

By default, the system returns time values in your local time zone and datetime functions return values in your local time. You can provide a different time zones using the timezone parameter in the function. The datetime functions will return the values in the specified timezone.

While datetime functions retrieve the time component of a timestamp in the specified time zone, they do not alter the timeframe used for time selection.

Refer to Time zones for more information on how specific time selection format affect the time frame included in your query.

Example

You are in Helsinki (EET time zone) at 8:00 AM and want to retrieve device performance data during business hours in London (GMT time zone). Use the following query, to narrow the data to London's business hours.

device_performance.events during past 24h
| where start_time.hour(timezone = 'GMT') >= 9 and end_time.hour(timezone = 'GMT') <= 17

In the image below, you can see the same query, but with different time formats listed with a list clause. Look at the timeframe and values returned depending on the time format.

• The timeframe reflects time in your current time zone.

• The start_time returns the full timestamp of the event in your current time zone.

• The start_time.hour() returns the hour of the event in your current time zone.

• The start_time.hour(timezone = 'GMT') returns the hour of the event in London time zone.

Make sure your timeframe selection in the query is large enough to include the timezone offset used in the filters.