datetrunc()

Truncates a date to the start of the interval defined by the specified time unit.

Syntax

Parameters

DATE

An expression that returns the date to truncate.

SPAN

A string representing the truncation unit and size. Write the number and unit together without spaces. The supported units are as follows.

OFFSET

(Optional) A string representing an offset to shift the truncation boundary. Uses the same unit format as SPAN. For example, with SPAN="1d" and OFFSET="8h", the day boundary is set at 08:00 instead of 00:00.

Description

The datetrunc() function returns the start of the time interval that contains DATE. The return type is timestamp.

When 1mon is specified, the date is truncated to the first day of the month at 00:00:00. 2mon, 3mon, 4mon, and 6mon truncate to the first day of the respective multi-month interval. 1y truncates to January 1 of the year at 00:00:00. 1w truncates to Monday of the week at 00:00:00.

Returns null if DATE is null or not a timestamp type. Returns DATE unchanged if the amount in SPAN is 0.

Error codes

90640

Raised when fewer than 2 arguments are provided.

Usage examples

To prepare the WEB_APACHE_SAMPLE table used in these examples, refer to Preparing sample data.

1. Truncate the access log timestamp to 1-hour intervals.

   table limit=5 WEB_APACHE_SAMPLE | eval result = datetrunc(_time, "1h")
   | fields _time, result
   

2. Truncate the access log timestamp to 5-minute intervals.

   table limit=5 WEB_APACHE_SAMPLE | eval result = datetrunc(_time, "5m")
   | fields _time, result
   

3. Truncate to hourly intervals and aggregate request counts per hour.

   table WEB_APACHE_SAMPLE | eval hour = datetrunc(_time, "1h")
   | stats count by hour
   | # Outputs the request count per hour.
   

4. Use an offset to truncate by business day starting at 08:00.

   table WEB_APACHE_SAMPLE | eval soc_day = datetrunc(_time, "1d", "8h")
   | stats count by soc_day
   | # Outputs the request count per business day starting at 08:00.
   

5. NULL input

   json "{}" | eval result = datetrunc(null, "1h")
   | # result: null
   

Compatibility

The datetrunc() function has been available since before Sonar 4.0.