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
<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 analterstage to create new timestamp fields, or within afilterstage 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:
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:
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:
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:
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:
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:
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:
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:
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
Related articles
Functions:
to_timestamp,format_string
Last updated
Was this helpful?
