NQL datetime functions

Datetime functions return specific time components— such as hour, day of the week, day of the month—from the timestamp field. 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 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 occurred 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

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 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

Timezone parameter

By default, the system returns time values in your local timezone, and datetime functions return values in your local time. Provide different timezones using the timezone parameter in the function. Datetime functions return values in the specified timezone. Refer to Supported timezones in datetime functions for more information.

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

Refer to Timezones for more information on how specific time selection formats affect the timeframe included in your query.

Example

You are in Helsinki (EET timezone) at 8:00 AM and want to retrieve device performance data during business hours in London (GMT timezone). 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

The image below shows the same query, but with different time formats included in a list clause. The timeframe and values returned depend on the time format.

• The timeframe reflects time in your current timezone.

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

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

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

Ensure your timeframe selection in the query is large enough to include the timezone offset used in the datetime functions.