parse_timestamp ↗

Use the parse_timestamp() function to convert a string representation of a timestamp into a TIMESTAMP object.

Syntax

parse_timestamp ("<format_time_string>", "<time_string>" | format_string(<time field>) | <time string field> [, "<time zone>"])

Parameters

Name Type Required Description
<format_time_string> string Yes A format string that defines the layout of the input timestamp string (for example, %Y-%m-%d %H:%M:%S).
<time_string> <time_string_field> string Yes
<time_zone> string No An optional time zone specified as an hours offset (for example, +08:00) or a time zone name (for example, America/Chicago). If omitted, the default is UTC.

Returns

The parse_timestamp() function returns a TIMESTAMP object.

Usage notes

  • The <format_time_string> parameter must define the format elements that match how the input string is formatted. Every element in the input string must have a corresponding element in the format string, and their positions must match.
  • If no time zone is configured via the optional parameter, the function defaults to using the UTC time zone.
  • This function is vital for transforming human-readable date-time strings into a structured timestamp format that can be used for time-based analysis, filtering, or display.
  • parse_timestamp() can be used within an alter stage to create new timestamp fields, or within a filter stage for time-based comparisons.

Examples

Example 1: Without a time zone configured (implicit UTC)

Goal: Convert a timestamp string into a TIMESTAMP object, relying on the default UTC time zone for interpretation.

XQL code:

config timeframe = 1d 
| dataset = sample_xql_raw 
| alter new_time_field = parse_timestamp("%Y-%m-%d %H:%M:%S", "2023-10-26 10:00:00") 
| fields event_id, new_time_field 
| limit 3

Explanation: The input string "2023-10-26 10:00:00" is parsed using the format "%Y-%m-%d %H:%M:%S". Because no time zone is specified, parse_timestamp() interprets it as a UTC timestamp, which is then displayed in the specified output format.

Output:

event_id new_time_field
101 Oct 26th 2023 10:00:00
102 Oct 26th 2023 10:00:00
103 Oct 26th 2023 10:00:00

Example 2: With a time zone configured using an hours offset

Goal: Convert a timestamp string, interpreting it relative to a +03:00 hours offset.

XQL code:

config timeframe = 1d 
| dataset = sample_xql_raw 
| alter new_time_field = parse_timestamp("%Y-%m-%d %H:%M:%S", "2023-10-26 10:00:00", "+03:00") 
| fields event_id, new_time_field 
| limit 3

Explanation: The input string 10:00:00 is interpreted as being in a +03:00 time zone. When converted to UTC (which all timestamps are internally based on), it effectively becomes 10:00:00 - 03:00 = 07:00:00 UTC. This UTC time is then returned as the TIMESTAMP object, displayed in the specified format.

Output:

event_id new_time_field
101 Oct 26th 2023 07:00:00
102 Oct 26th 2023 07:00:00
103 Oct 26th 2023 07:00:00

Example 3: With a time zone name configured

Goal: Convert a timestamp string, interpreting it relative to the "America/Chicago" time zone.

XQL code:

config timeframe = 1d 
| dataset = sample_xql_raw 
| alter new_time_field = parse_timestamp("%Y-%m-%d %H:%M:%S", "2023-10-26 10:00:00", "America/Chicago") 
| fields event_id, new_time_field 
| limit 3

Explanation: The input string 10:00:00 is interpreted as being in the "America/Chicago" time zone. To convert this local time to UTC, you add the offset. If Chicago is UTC-5 (CDT), then 10:00:00 + 05:00 = 15:00:00 UTC. This UTC time is then returned as the TIMESTAMP object, displayed in the specified format.

Output:

event_id new_time_field
101 Oct 26th 2023 15:00:00
102 Oct 26th 2023 15:00:00
103 Oct 26th 2023 15:00:00

Example 4: Converting a time string that contains milliseconds

Goal: Convert a time string that includes fractional seconds (milliseconds) using specific format elements.

XQL code:

config timeframe = 1d 
| dataset = sample_xql_raw 
// Use %E3S to capture 3 digits of fractional seconds, commonly used for milliseconds 
| alter time_string_with_millis = "2023-10-26 10:00:00.723" // Example string with milliseconds
| alter new_time_field = parse_timestamp("%Y-%m-%d %H:%M:%E3S", time_string_with_millis) //"%H:%M:%E3S" is also equivalent to "%R:%E3S", both matching "10:00:00.723" 
| fields event_id, time_string_with_millis, new_time_field 
| limit 3

Explanation: The parse_timestamp() function successfully processes the input string time_string_with_millis using the format string that includes %E3S to account for seconds and three digits of fractional seconds. Although the internal TIMESTAMP object will store the milliseconds, the final displayed new_time_field conforms to the default format, truncating the fractional part in the output.

Output:

event_id time_string_with_millis new_time_field
101 2023-10-26 10:00:00.723 Oct 26th 2023 10:00:00
102 2023-10-26 10:00:00.723 Oct 26th 2023 10:00:00
103 2023-10-26 10:00:00.723 Oct 26th 2023 10:00:00