How do I sort by date when using strftime?

[caffein]

I would like to group a bunch of data by date, but splunk doesn't seem to have a function to do this explicitly. So, I used this workaround:

index=foo
|eval date=strftime(_time, "%Y-%m-%d")
|stats avg(bar) AS BarAvg, avg(stuff) AS StuffAvg BY date,country
|table country, date, BarAvg, StuffAvg

Now, as far as grouping all the data appropriately this works fine. The problem is that I can't change the sort when I'm in table view. I click date and the little arrow will change direction, but all the rows stay in the same place. Sorting on all the other fields works fine though.

So, with all this in mind:

1) How do make the date column sortable?

2) Is the way I'm doing this correct, or is there a better method that I can use?

[caffein · Accepted solution]

Ok, I was able to solve this by combining both yannK's and Genti's answers. Here's what I did:

index=foo 
|eval date=strftime(_time, "%Y-%m-%d")
|convert timeformat="%Y-%m-%d" mktime(date) AS date
|fieldformat date=strftime(date,"%Y-%m-%d")  
|stats avg(bar) AS BarAvg, avg(stuff) AS StuffAvg BY date,country 
|table country, date, BarAvg, StuffAvg

Basically, this pulls out just the date from _time, converts the date into epoch time, then makes it look nice for viewing. I can then use the date epoch time for grouping and sorting, but when I view it, it's in a readable format.

[caffein]

lol. Unfortunately, splunk is a great robot and I still need to use date for grouping the data. However, this won't work because fieldformat doesn't alter the underlying data only how it's displayed. From what I can tell, your suggestion would be like saying "group by _time, but only show the date portion of _time in the results".

[yannK]

Epoch time is readable for robots
http://www.quickmeme.com/meme/3p89ty/

Maybe you only need to do the time to date conversion once, not 3 times.

| fieldformat date=strftime(_time,"%Y-%m-%d")

[Genti]

Instead of using eval, you should use fieldformat.

Here's an example search:

index=_internal | head 100 | eval raw=_raw | eval Time = _time | fieldformat Time=strftime(Time, "%D %H:%M:%S %p") |  table Time raw

[yannK]

there is a typo in your eval, please use | eval field=function(blah)

Sorting by date works fine, to do a presorting use try |sort -date
Also a workaround is to convert the date to seconds and use it to sort before defining the table columns

index=foo 
|eval date=strftime(_time, "%Y-%m-%d")
|stats avg(bar) AS BarAvg, avg(stuff) AS StuffAvg BY date,country 
| convert timeformat="%Y-%m-%d" mktime(date) AS date_epoch
|sort -date_epoch  |table country, date, BarAvg, StuffAvg

[caffein]

I've got about 40 rows aggregated from about 7 million logs. If I set the number of rows per page to 50, everything will be on one page. At some point I might want to do a larger number of values, which when this could become an issue.

[yannK]

I am surprised that the dymanic sorting using the date column is not working. the date is Y-m-d so alphabetically sortable.

How many pages of results to you have, are you on the first one when you sort ?

[caffein]

Ok, well this kind of worked. By inserting date_epoch into the table I can view the pretty print date, but dynamically sort using date_epoch. Part of what I wanted is to be able to sort by clicking on the column headers in the table view rather than having the sort set by the query. Although, thanks for letting me know how to do the presort, it will be nice to set the default sort. I still can't change the sort by clicking the date column header though.