extract_time ↗

Use the extract_time() function to isolate and return specific components of a timestamp, such as the year, month, or hour.

Important Note: The values returned by extract_time() are always based on GMT (Greenwich Mean Time), even if your server settings for Timezone or Timestamp Format are adjusted.

Syntax

extract_time (<timestamp>, <part>)

Parameters

Name Type Required Description
timestamp timestamp Yes The input timestamp value, originating from a field or the result of another function.
part string Yes The specific unit of the timestamp to extract.

Returns

The extract_time() function returns a numerical value (Integer or Number) representing the extracted portion of the timestamp.

Usage notes

  • The values returned by extract_time() are always based on GMT (Greenwich Mean Time) even if you've adjusted the Timezone or Timestamp Format server settings. For more information on the server settings, see Configure server settings.
  • The part parameter is not case-sensitive, but the string must match one of the supported keywords.
  • The supported keywords for the part parameter are:
    • DAY: Day of the month (1-31).
    • DAYOFWEEK: Day of the week (1=Monday, 7=Sunday).
    • DAYOFYEAR: Day of the year (1-366).
    • HOUR: Hour of the day (0-23).
    • MICROSECOND: Microseconds.
    • MILLISECOND: Milliseconds.
    • MINUTE: Minute of the hour (0-59).
    • MONTH: Month of the year (1-12).
    • QUARTER: Quarter of the year (1-4).
    • SECOND: Second of the minute (0-59).
    • YEAR: Year (for example, 2023).

Examples

Example 1: Extracting the YEAR

Goal: Extract the year from the _time field.

XQL code:

config timeframe = 1d 
| dataset = sample_xql_raw 
| alter event_year = extract_time(_time, "YEAR") 
| fields event_id, _time, event_year 
| limit 3

Explanation: The event_year field shows the integer year (2023) extracted from each event's _time timestamp.

Output:

EVENT_ID _TIME EVENT_YEAR
101 Oct 26th 2023 10:00:00 2023
102 Oct 26th 2023 10:05:30 2023
103 Oct 26th 2023 10:15:15 2023

Example 2: Extracting the MONTH

Goal: Extract the month from the _time field.

XQL code:

config timeframe = 1d 
| dataset = sample_xql_raw 
| alter event_month = extract_time(_time, "MONTH") 
| fields event_id, _time, event_month 
| limit 3

Explanation: The event_month field shows the integer month (10 for October) extracted from each event's _time timestamp.

Output:

EVENT_ID _TIME EVENT_MONTH
101 Oct 26th 2023 10:00:00 10
102 Oct 26th 2023 10:05:30 10
103 Oct 26th 2023 10:15:15 10

Example 3: Extracting the DAY

Goal: Extract the day of the month from the _time field.

XQL code:

config timeframe = 1d 
| dataset = sample_xql_raw 
| alter event_day = extract_time(_time, "DAY") 
| fields event_id, _time, event_day 
| limit 3

Explanation: The event_day field shows the integer day of the month (26) extracted from each event's _time timestamp.

Output:

EVENT_ID _TIME EVENT_DAY
101 Oct 26th 2023 10:00:00 26
102 Oct 26th 2023 10:05:30 26
103 Oct 26th 2023 10:15:15 26

Example 4: Extracting the DAYOFWEEK

Goal: Extract the day of the week from the _time field.

XQL code:

config timeframe = 1d 
| dataset = sample_xql_raw 
| alter event_day_of_week = extract_time(_time, "DAYOFWEEK") 
| fields event_id, _time, event_day_of_week 
| limit 3

Explanation: The event_day_of_week field shows the integer day of the week (4 for Thursday) extracted from each event's _time timestamp.

Output:

EVENT_ID _TIME EVENT_DAY_OF_WEEK
101 Oct 26th 2023 10:00:00 4
102 Oct 26th 2023 10:05:30 4
103 Oct 26th 2023 10:15:15 4

Example 5: Extracting the DAYOFYEAR

Goal: Extract the day of the year from the _time field.

XQL code:

config timeframe = 1d 
| dataset = sample_xql_raw 
| alter event_day_of_year = extract_time(_time, "DAYOFYEAR") 
| fields event_id, _time, event_day_of_year 
| limit 3

Explanation: The event_day_of_year field shows the integer day of the year (299) extracted from each event's _time timestamp.

Output:

EVENT_ID _TIME EVENT_DAY_OF_YEAR
101 Oct 26th 2023 10:00:00 299
102 Oct 26th 2023 10:05:30 299
103 Oct 26th 2023 10:15:15 299

Example 6: Extracting the HOUR

Goal: Extract the hour from the _time field.

XQL code:

config timeframe = 1d 
| dataset = sample_xql_raw 
| alter event_hour = extract_time(_time, "HOUR") 
| fields event_id, _time, event_hour 
| limit 3

Explanation: The event_hour field shows the integer hour (10) extracted from each event's _time timestamp.

Output:

EVENT_ID _TIME EVENT_HOUR
101 Oct 26th 2023 10:00:00 10
102 Oct 26th 2023 10:05:30 10
103 Oct 26th 2023 10:15:15 10

Example 7: Extracting the MINUTE

Goal: Extract the minute from the _time field.

XQL code:

config timeframe = 1d 
| dataset = sample_xql_raw 
| alter event_minute = extract_time(_time, "MINUTE") 
| fields event_id, _time, event_minute 
| limit 3

Explanation: The event_minute field shows the integer minute extracted from each event's _time timestamp.

Output:

EVENT_ID _TIME EVENT_MINUTE
101 Oct 26th 2023 10:00:00 0
102 Oct 26th 2023 10:05:30 5
103 Oct 26th 2023 10:15:15 15

Example 8: Extracting the SECOND

Goal: Extract the second from the _time field.

XQL code:

config timeframe = 1d 
| dataset = sample_xql_raw 
| alter event_second = extract_time(_time, "SECOND") 
| fields event_id, _time, event_second 
| limit 3

Explanation: The event_second field shows the integer second extracted from each event's _time timestamp.

Output:

EVENT_ID _TIME EVENT_SECOND
101 Oct 26th 2023 10:00:00 0
102 Oct 26th 2023 10:05:30 30
103 Oct 26th 2023 10:15:15 15

Example 9: Extracting the MILLISECOND

Goal: Extract the milliseconds from a timestamp. Because the sample data does not explicitly display milliseconds, this example uses current_time() for demonstration.

XQL code:

config timeframe = 1d 
| dataset = sample_xql_raw 
| alter current_ts_val = current_time() // Conceptual: Jul 25th 2024 14:30:45.123 
| alter ms_part = extract_time(current_ts_val, "MILLISECOND") 
| fields event_id, current_ts_val, ms_part 
| limit 3

Explanation: The ms_part field shows the millisecond component (123) extracted from the current time. This illustrates the function's capability with higher precision timestamps.

Output:

EVENT_ID CURRENT_TS_VAL MS_PART
101 Jul 25th 2024 14:30:45 123
102 Jul 25th 2024 14:30:45 123
103 Jul 25th 2024 14:30:45 123

Example 10: Extracting the MICROSECOND

Goal: Extract the microseconds from a timestamp. Because the sample data does not explicitly display microseconds, this example uses current_time() for demonstration.

XQL code:

config timeframe = 1d 
| dataset = sample_xql_raw 
| alter current_ts_val = current_time() // Conceptual: Jul 25th 2024 14:30:45.123456 
| alter us_part = extract_time(current_ts_val, "MICROSECOND") 
| fields event_id, current_ts_val, us_part 
| limit 3

Explanation: The us_part field shows the microsecond component (123456) extracted from the current time, demonstrating the function's precision.

Output:

EVENT_ID CURRENT_TS_VAL US_PART
101 Jul 25th 2024 14:30:45 123456
102 Jul 25th 2024 14:30:45 123456
103 Jul 25th 2024 14:30:45 123456

Example 11: Extracting the QUARTER

Goal: Extract the quarter of the year from the _time field.

XQL code:

config timeframe = 1d 
| dataset = sample_xql_raw 
| alter event_quarter = extract_time(_time, "QUARTER") 
| fields event_id, _time, event_quarter 
| limit 3

Explanation: The event_quarter field shows the integer quarter of the year (4) extracted from each event's _time timestamp.

Output:

EVENT_ID _TIME EVENT_QUARTER
101 Oct 26th 2023 10:00:00 4
102 Oct 26th 2023 10:05:30 4
103 Oct 26th 2023 10:15:15 4