to_timestamp ↗

Use the to_timestamp() function to convert an integer value representing Unix epoch time into a human-readable TIMESTAMP data type.

Syntax

to_timestamp (<integer>, <units>)

Parameters

Name Type Required Description
integer integer Yes The numerical value representing epoch time. This can be a literal integer or a field containing an integer.
units string No A string literal that specifies the unit of the epoch integer. Supported values are "SECONDS", "MILLIS" (milliseconds), or "MICROS" (microseconds). The default value is "SECONDS" if the parameter is omitted.

Returns

The to_timestamp() function returns a TIMESTAMP compatible value.

Usage notes

  • The function is essential when you need to display or use epoch-based numerical time representations as standard timestamps for analysis, filtering, or display.
  • If the <units> parameter is not supplied, it defaults to "SECONDS".
  • to_timestamp() is often used after a function like parse_epoch() (which converts a string representation of a timestamp into an epoch integer) or to_epoch() (which converts a timestamp into an epoch integer).
  • If the input integer field or literal is NULL, the function will return NULL.
  • If the integer input value does not match the units parameter (for example, a non-sensical value for the given unit), this function will return NULL.

Examples

Example 1: Converting a numeric field to a timestamp (default seconds unit)

Goal: Convert an integer field to a timestamp, relying on the default behavior to treat the value as epoch seconds.

XQL code:

config timeframe = 1d 
| dataset = sample_xql_raw 
| alter epoch_timestamp_default_seconds = to_timestamp(to_integer(event_id)) 
| fields event_id, epoch_timestamp_default_seconds 
| limit 3

Explanation: The event_id is passed through to_integer() to guarantee data type compliance, and then converted by to_timestamp() into a TIMESTAMP value corresponding to the seconds after the Unix epoch because no unit was specified, defaulting to SECONDS.

Output:

EVENT_ID EPOCH_TIMESTAMP_DEFAULT_SECONDS
101 1970-01-01 00:01:41 UTC
102 1970-01-01 00:01:42 UTC
103 1970-01-01 00:01:43 UTC

Example 2: Converting a numeric field to a timestamp (explicit seconds unit)

Goal: Explicitly convert an integer field to a timestamp, treating its value as epoch seconds.

XQL code:

config timeframe = 1d 
| dataset = sample_xql_raw 
| alter epoch_timestamp_explicit_seconds = to_timestamp(to_integer(event_id), "SECONDS") 
| fields event_id, epoch_timestamp_explicit_seconds 
| limit 3

Explanation: The event_id is passed through to_integer() to guarantee data type compliance, and is then explicitly converted using the "SECONDS" unit, yielding the same result as the default behavior.

Output:

EVENT_ID EPOCH_TIMESTAMP_EXPLICIT_SECONDS
101 1970-01-01 00:01:41 UTC
102 1970-01-01 00:01:42 UTC
103 1970-01-01 00:01:43 UTC

Example 3: Converting a literal integer to a timestamp (milliseconds unit)

Goal: Convert a static integer literal, representing a specific point in time in milliseconds, to a timestamp.

XQL code:

config timeframe = 1d 
| dataset = sample_xql_raw 
| alter literal_timestamp_millis = to_timestamp(1672531200000, "MILLIS") 
| fields event_id, literal_timestamp_millis 
| limit 3

Explanation: The literal integer 1672531200000 is explicitly converted as milliseconds, resulting in the constant timestamp for January 1, 2023, 00:00:00 UTC across all records.

Output:

EVENT_ID LITERAL_TIMESTAMP_MILLIS
101 2023-01-01 00:00:00 UTC
102 2023-01-01 00:00:00 UTC
103 2023-01-01 00:00:00 UTC

Example 4: Converting a literal integer to a timestamp (microseconds unit)

Goal: Convert a static integer literal, representing a specific point in time in microseconds, to a timestamp.

XQL code:

config timeframe = 1d 
| dataset = sample_xql_raw 
| alter literal_timestamp_micros = to_timestamp(1672531200000000, "MICROS") 
| fields event_id, literal_timestamp_micros 
| limit 3

Explanation: The literal integer 1672531200000000 is explicitly converted as microseconds, resulting in the constant timestamp for January 1, 2023, 00:00:00 UTC across all records.

Output:

EVENT_ID LITERAL_TIMESTAMP_MICROS
101 2023-01-01 00:00:00 UTC
102 2023-01-01 00:00:00 UTC
103 2023-01-01 00:00:00 UTC

Example 5: Converting a derived integer from an array field to a timestamp (milliseconds unit)

Goal: Extract an integer from an array, treat it as epoch milliseconds, and convert it to a timestamp.

XQL code:

config timeframe = 1d 
| dataset = sample_xql_raw 
| alter first_numeric_code = to_integer(arrayindex(numeric_codes, 0)) 
| alter derived_timestamp_micros = to_timestamp(first_numeric_code, "MILLIS") 
| fields event_id, numeric_codes, derived_timestamp_micros 
| limit 3

Explanation: For each event, the first value from its numeric_codes array is extracted and explicitly converted to an integer. This integer value is then interpreted as milliseconds from the Unix epoch and converted into a timestamp. Note that negative epoch values represent times before 1970-01-01 00:00:00 UTC.

Output:

EVENT_ID NUMERIC_CODES DERIVED_TIMESTAMP_MICROS
101 [13, -47, 29, 82, -15] 1970-01-01 00:00:00.013 UTC
102 [-21, 56, 13, -88, 42] 1969-12-31 23:59:59.979 UTC
103 [90, -33, 7, 51, -62, 18] 1970-01-01 00:00:00.090 UTC

Example 6: Handling NULL input

Goal: Demonstrate behavior when the input integer is NULL.

XQL code:

config timeframe = 1d 
| dataset = sample_xql_raw 
| alter null_integer_field = NULL 
| alter converted_null_timestamp = to_timestamp(null_integer_field, "SECONDS") 
| fields event_id, converted_null_timestamp 
| limit 3

Explanation: When the input to to_timestamp() is NULL, the function consistently returns NULL.

Output:

EVENT_ID CONVERTED_NULL_TIMESTAMP
101 NULL
102 NULL
103 NULL