Evaluation functions specific to sdselect

This topic describes evaluation functions that are specific to the sdselect command, for use with federated searches of remote datasets. You use evaluation functions to evaluate an expression, based on your events, and return a result.

timestamp_from_unixtime(<time>)

Description

This function takes a numeric UNIX time format timestamp or a field with a numeric UNIX time format timestamp as its argument and converts that input into an equivalent SQL timestamp data type value.

Usage

Use the timestamp_from_unixtime evaluation function to directly compare timestamps in the numeric UNIX time format with SQL timestamp data type values. This evaluation function is necessary because Splunk software cannot apply type casting to evaluate searches like this:

| sdselect * FROM table WHERE time_dt < 1728348383

The timestamp_from_unixtime function is especially useful for federated searches of Amazon Security Lake datasets that take advantage of partition time fields. The default partition time field for Amazon Security Lake events is time_dt, which has SQL timestamp values.

You can use the timestamp_from_unixtime function only with the sdselect command. You can apply the timestamp_from_unixtime function to fields that you select with sdselect, as well as to WHERE clause arguments in sdselect searches.

You cannot apply the timestamp_from_unixtime function to GROUPBY or ORDERBY clause arguments in sdselect searches.

For more information about running federated searches of Amazon Security Lake datasets, see About Federated Analytics.

Using timestamp_from_unixtime for search optimization

Because timestamp_from_unixtime does not transform SQL timestamp data type field values, it is better at optimizing searches that involve partition fields than timestamp_to_unixtime.

In other words, although the following two searches return the same results, the timestamp_from_unixtime option might offer better search performance.

| sdselect * FROM table WHERE time_dt < timestamp_from_unixtime(1728348383)

| sdselect * FROM table WHERE 1728348383 >= timestamp_to_unixtime(time_dt)

Basic examples

When you run an sdselect search against an Amazon Security Lake dataset, and you select a specific time window for that search, Splunk software applies timestamp_from_unixtime to the WHERE clause in that search when it processes the search.

However, you can also run a search against an Amazon Security Lake dataset that explicitly adds filters, including the partition time field, time_dt, to the WHERE clause, like this:

| sdselect * FROM my_ASL_data WHERE time_dt < timestamp_from_unixtime(1728348383)

Like many other evaluation functions, timestamp_from_unixtime supports nested functions:

| sdselect * FROM my_ASL_data WHERE time_dt < timestamp_from_unixtime(relative_time(now(), "-1h@h"))

timestamp_to_unixtime(<time>)

Description

This function takes a field with a SQL timestamp data type as its main argument and converts its value into an equivalent numeric UNIX time format timestamp.

Usage

Use the timestamp_to_unixtime evaluation function to directly compare time fields that have SQL timestamp values with timestamps in the numeric UNIX time format.

The timestamp_to_unixtime function is especially useful for federated searches of Amazon Security Lake datasets that take advantage of partition time fields. The default partition time field for Amazon Security Lake events is time_dt, which has SQL timestamp values.

The timestamp_to_unixtime function fails if its argument is a field with a data type other than SQL timestamp.

Basic example

This example converts the SQL timestamp data type values of time_dt to numeric UNIX time format timestamp values and compares them to the provided UNIX time format timestamp.

| sdselect * FROM my_ASL_data WHERE 1728348383 >= timestamp_to_unixtime(time_dt)

See also

Function information

Evaluation functions quick reference

Related functions

now function

relative time function

sdselect command

sdselect command overview

sdselect command syntax details

sdselect command usage

sdselect command WHERE clause operations

Use time fields in sdselect searches

sdselect command examples for Amazon S3