_time being treated as a string when run with table command

[termcap]

Hi,

I have the following CSV data that I've uploaded into Splunk

iso_code,continent,location,date,total_cases
USA,North America,United States,2020-01-22,1.0
USA,North America,United States,2020-01-23,1.0
USA,North America,United States,2020-01-24,2.0
USA,North America,United States,2020-01-25,2.0

My props.conf is as below

[csv-c1]
BREAK_ONLY_BEFORE_DATE =
DATETIME_CONFIG =
INDEXED_EXTRACTIONS = csv
KV_MODE = none
LINE_BREAKER = ([\r\n]+)
NO_BINARY_CHECK = true
SHOULD_LINEMERGE = false
TIMESTAMP_FIELDS = date
TIME_FORMAT = %Y-%m-%d
category = Structured
description = Comma-separated value format. Set header and other settings in "Delimited Settings"
disabled = false
pulldown_type = true

Now, when I perform the search on this data the events are listed with the expected _time values

But when I try to create a table with _time field, it appears as if Splunk treats the _time field as a string and not as a date object. Notice that the chronological order of the dates is lost as well as the HH:MM::SS part.

Can someone please point out why Splunk is behaving this way ? Surprisingly if I index the same data with the current timestamp, everything works fine and _time does not lose its chronological order or its HH:MM:SS part its displayed using the table command

[termcap]

Hi @gcusello kindly have a look at this behavior, your comment would be highly appreciated.

[ITWhisperer · Accepted solution]

The dates are in chronological order, just reversed. The time is shown as midnight when displayed in the event list, but all the times are just dates so splunk optimises the display as only dates are present.

[termcap]

@ITWhisperer 

The latest date I have is for yesterday: 08/04/2021 and the oldest date I have is 01/01/2020. In the below screenshot you will see that dates are mixed up with 2021 dates appearing between 2020 dates.

[gcusello]

Hi @termcap,

as @ITWhisperer said, the dates are in chronological order, but probably in the second search they are displayed in the order of your ingestion csv file.

You can check this analyzing your csv file, probably the order that you see in you second search is the same of the csv file.

If you want to order them, you can sort them.

If instead you use the current timestamp, obviously they will be in index order, but if you index using current time as timestamp and you display _time and timestamp, probably you'll have the _time column ordered by time and the timestamp column in the sare order of now.

Ciao.

Giuseppe

[termcap]

Thank you for your comments @gcusello @ITWhisperer Is there a way to force Splunk to display the HH:MM:SS part when using the table command ?

[ITWhisperer]

You could try adding:

| fieldformat _time=strftime(_time,"%Y-%m-%d %H:%M:%S")