When you define a federated index for an AWS Glue table dataset, you define the Time field, Time format, and Unix time field for the dataset. The Time field is the actual time field in your AWS Glue table data, and the Time format is the format of that time field.

The Unix time field is a field alias of the Time field that accurately converts the Time field values into numeric UNIX time format values, even if the Time field has a string UNIX time format, a custom string format, or a SQL timestamp data type.

The Unix time field defaults to _time. The _time field is useful because Splunk software treats _time as a field with both numeric and string properties.

• Because _time values are in numeric UNIX time format, you can use _time in functions and operations that require numeric values.
• However, when it makes sense to do so, Splunk Web translates _time values into human-readable timestamp strings that display with the time zone of the Splunk platform deployment running the search.

The following table shows you how different kinds of Time field fields match up against Unix time field fields when you apply time fields to sdselect use cases such as numeric stats aggregation functions, WHERE clauses, and GROUPBY clauses with span arguments.

¹ Lexicographical order sorts items based on the values used to encode the items in computer memory. In Splunk software, this is almost always UTF-8 encoding, which is a superset of ASCII. Lexicographical order sorts numbers before letters, based on the first digit. For example, the numbers 10, 9, 70, 100 are sorted lexicographically as 10, 100, 70, 9.

To learn more about defining the Time field, Time format, and Unix time field, see Map a federated index to an AWS Glue Data Catalog dataset.

For more information about applying time fields to sdselect WHERE clauses, see Apply date and time eval functions to fields in the WHERE clause.

AWS Glue table datasets might include time fields with SQL timestamp data type values. The sdselect command does not natively support the timestamp data type. The sdselect command converts timestamp values into a string with a yyyy-mm-dd hh:mm:ss[.nnnnnnnnn] format, which means that timestamp values do not work for aspects of sdselect searches that require numeric values, such as stats aggregation functions like avg() and sum().

The sdselect command displays timestamp values in the Coordinated Universal Time (UTC) time zone.

If the Time field for your federated index has a SQL timestamp data type, your best option is to set the Unix time field for that federated index to _time. The Unix time field is an alias for the Time field. The Unix time field converts the SQL timestamp data type values of the Time field into numeric UNIX time format. This conversion is perfect for functions that require numeric time field values.

Splunk Web displays _time values in human-readable string format where it is appropriate to do so, in the local time zone for your Splunk platform deployment.

This example involves a federated index named Time_Field_Timestamp_Data_Type. The time field values for this federated index are as follows:

The Time format variable of %ST indicates that SQLTimestampTime, the declared Time field for this federated index, has values of the SQL timestamp data type.

Here is a search that summarizes the information contained in the AWS Glue table dataset to which the federated index maps.

| sdselect scan_count, user, SQLTimestampTime, _time FROM Time_Field_Timestamp_Data_Type

Here are the results of that search. Notice that the timestamp values for SQLTimestampTime, the Time field from the AWS Glue table, have the UTC time zone, while the timestamps for _time, the Unix time field, are the same values in Pacific Standard Time (PST), the time zone local to the Splunk platform deployment the search ran upon.

Now, here is an sdselect search that is run against that dataset. Notice that this search uses the _time field in several spots, and that the WHERE clause compares _time against a time value in numeric UNIX time format. This UNIX time value is equivalent to March 9, 2023 4:51:21 PM (PT), which means that the search filters events for Larry and Ramon out of its results.

| sdselect count, values(user), values(scan_count), avg(_time), min(SQLTimestampTime), max(SQLTimestampTime) FROM Time_Field_Timestamp_Data_Type WHERE _time < 1678409481 GROUPBY _time span=4h

The search returns the following results.

If the search uses SQLTimestampTime for the avg() function, the WHERE clause, or for the GROUPBY clause with the span argument, the search fails and returns error messages. This happens for the following reasons:

• SQLTimestampTime has time field values with the SQL timestamp data type, which sdselect cannot apply to stats aggregation functions that require numeric values, such as avg() and sum().
• sdselect cannot apply time field values with the SQL timestamp data type to WHERE clauses.
• sdselect requires that you apply the Unix time field to GROUPBY clauses with a span argument.

When you use _time, the Unix time field alias of SQLTimestampTime, the search works as expected.