date_floor ↗

Use the date_floor() function to return a new timestamp that is rounded down to the nearest whole value of a specified time unit.

Syntax

date_floor (<timestamp field>, "<time_unit>" [, "<time zone>"])

Parameters

Name Type Required Description
timestamp field timestamp Yes The input timestamp value, originating from a field or the result of another function.
time_unit string Yes The unit to which the timestamp should be rounded down. Supported values are y (year), mo (month), w (week), d (day), or h (hour). This parameter is not case-sensitive.
time_zone string No The time zone to apply for the calculation. This can be an hours offset (for example, +08:00) or a time zone name from the List of Supported Time Zones, (for example, America/Chicago). If omitted, the default is UTC.

Returns

The date_floor() function returns a TIMESTAMP value rounded down to the beginning of the specified time unit.

Usage notes

  • The function always rounds the timestamp down to the beginning of the specified time unit. For example, date_floor("2023-10-26 10:30:00 UTC", "h") results in 2023-10-26 10:00:00.
  • Supported time units (y, mo, w, d, h) are not case-sensitive.
  • This function is typically used within alter or filter stages to perform data transformations for time-based aggregation or analysis.

Examples

Example 1: Rounding _time to the nearest hour (default UTC)

Goal: Round the _time field to the beginning of the hour using the default UTC time zone.

XQL code:

config timeframe = 1d 
| dataset = sample_xql_raw 
| alter floored_hour = date_floor(_time, "h") 
| fields event_id, _time, floored_hour 
| limit 3

Explanation: The query rounds the _time field down to the start of the hour. For events occurring at 10:05:30 or 10:15:15, the result is 10:00:00.

Output:

event_id _time floored_hour
101 Oct 26th 2023 10:00:00 Oct 26th 2023 10:00:00
102 Oct 26th 2023 10:05:30 Oct 26th 2023 10:00:00
103 Oct 26th 2023 10:15:15 Oct 26th 2023 10:00:00

Example 2: Rounding _time to the nearest day (default UTC)

Goal: Round the _time field to the beginning of the day using the default UTC time zone.

XQL code:

config timeframe = 1d 
| dataset = sample_xql_raw 
| alter floored_day = date_floor(_time, "d") 
| fields event_id, _time, floored_day 
| limit 3

Explanation: All _time values are rounded down to the very beginning of the day (midnight UTC) on October 26th, 2023.

Output:

event_id _time floored_day
101 Oct 26th 2023 10:00:00 Oct 26th 2023 00:00:00
102 Oct 26th 2023 10:05:30 Oct 26th 2023 00:00:00
103 Oct 26th 2023 10:15:15 Oct 26th 2023 00:00:00

Example 3: Rounding _time to the nearest week (default UTC)

Goal: Round the _time field to the beginning of the week using the default UTC time zone.

XQL code:

config timeframe = 1d 
| dataset = sample_xql_raw 
| alter floored_week = date_floor(_time, "w") 
| fields event_id, _time, floored_week 
| limit 3

Explanation: Because October 26th, 2023, falls within the week starting Sunday, October 22nd, 2023, all timestamps are rounded down to the beginning of that week (midnight UTC on October 22nd).

Output:

event_id _time floored_week
101 Oct 26th 2023 10:00:00 Oct 22nd 2023 00:00:00
102 Oct 26th 2023 10:05:30 Oct 22nd 2023 00:00:00
103 Oct 26th 2023 10:15:15 Oct 22nd 2023 00:00:00

Example 4: Rounding _time to the nearest month (default UTC)

Goal: Round the _time field to the beginning of the month using the default UTC time zone.

XQL code:

config timeframe = 1d 
| dataset = sample_xql_raw 
| alter floored_month = date_floor(_time, "mo") 
| fields event_id, _time, floored_month 
| limit 3

Explanation: All _time values are rounded down to the very beginning of October 2023 (midnight UTC on October 1st).

Output:

event_id _time floored_month
101 Oct 26th 2023 10:00:00 Oct 1st 2023 00:00:00
102 Oct 26th 2023 10:05:30 Oct 1st 2023 00:00:00
103 Oct 26th 2023 10:15:15 Oct 1st 2023 00:00:00

Example 5: Rounding _time to the nearest year (default UTC)

Goal: Round the _time field to the beginning of the year using the default UTC time zone.

XQL code:

config timeframe = 1d 
| dataset = sample_xql_raw 
| alter floored_year = date_floor(_time, "y") 
| fields event_id, _time, floored_year 
| limit 3

Explanation: All _time values are rounded down to the very beginning of 2023 (midnight UTC on January 1st).

Output:

event_id _time floored_year
101 Oct 26th 2023 10:00:00 Jan 1st 2023 00:00:00
102 Oct 26th 2023 10:05:30 Jan 1st 2023 00:00:00
103 Oct 26th 2023 10:15:15 Jan 1st 2023 00:00:00

Example 6: Rounding current_time() to the nearest day with a specific time zone

Goal: Round the current time (assumed here as Jul 25th 2024 14:30:00 UTC) to the start of the day in a specific time zone ("America/Los_Angeles").

XQL code:

config timeframe = 1d 
| dataset = sample_xql_raw 
| alter current_time_val = current_time() 
| alter floored_day_la = date_floor(current_time_val, "d", "America/Los_Angeles") 
| fields event_id, current_time_val, floored_day_la 
| limit 3

Explanation: The function converts the UTC time to the Los Angeles time zone (PDT, UTC-7), rounds it to the start of that day (midnight PDT), and returns the timestamp. Midnight PDT translates to 07:00:00 UTC.

Output:

event_id current_time_val floored_day_la
101 Jul 25th 2024 14:30:00 Jul 25th 2024 07:00:00
102 Jul 25th 2024 14:30:00 Jul 25th 2024 07:00:00
103 Jul 25th 2024 14:30:00 Jul 25th 2024 07:00:00

Example 7: Filtering events relative to the start of the week with timezone adjustment

Goal: Return up to 100 records from the xdr_data dataset where the event time (_time) is earlier than a calculated timestamp. The calculation determines the start of the current week in the "America/Los_Angeles" timezone and subtracts exactly 24 days (2,073,600 seconds) from that point.

XQL Code:

dataset = sample_xql_raw
| filter _time < to_timestamp(add(to_epoch(date_floor(current_time(),"w", "America/Los_Angeles")),-2073600))
| limit 100

Explanation: This query performs a multi-step time transformation to create a dynamic filter:

  1. current_time() retrieves the present time.
  2. date_floor(..., "w", "America/Los_Angeles") rounds that time down to the beginning of the week based on Los Angeles time.
  3. to_epoch(...) converts that "start of week" timestamp into a Unix epoch integer (seconds).
  4. add(..., -2073600) subtracts 2,073,600 seconds (equivalent to 24 days) from the epoch value.
  5. to_timestamp(...) converts the resulting integer back into a standard timestamp format.
  6. The filter stage then compares the _time of every record against this calculated value.

Output:

_time event_id event_type
2023-10-01 14:20:00 UTC 88412 ENHANCED_EVENT_LOG
2023-09-28 09:15:30 UTC 88305 STORYLINE
2023-09-25 22:10:00 UTC 88112 BROWSER_QUERY