How to change cell color based on relative date?

[mistydennis]

I would like to change the color of a cell based on the date. If the date is in the future, then green. If the date is today, then yellow. If the date is in the past, then red. I have read many posts about how to do this based on _time and appending a word to the date ("overdue", for example), but I can't seem to make this work in my case. The field I'm working with is dueDate (not based on _time), and it's formatted as %Y-%m-%d. 

[mistydennis]

Yes, newduedate is now in epoch format. How do I now get it to:

1. Be in %Y-%m-%d format
2. Adjust the relative time so it's accounting for future dates (any date after today), today, and any date prior to today

[ITWhisperer · Accepted solution]

strftime() converts an epoch time to a string.

strptime() parses a string to an epoch time.

Both take a time format string

Date and time format variables - Splunk Documentation

You can easily compare epoch times since they are just numbers

| eval stringdate=if(epochdate=relative_time(now(),"@d"),mvappend(stringdate,"today"), if(epochdate<relative_time(now(),"@d"),mvappend(stringdate,"before today"),mvappend(stringdate,"after todate")))

[mistydennis]

Thank you, that got me close enough!

[ITWhisperer]

Make the dueDate field a multi-value field with the second value being the colour you want the cell coloured, then use CSS to hide the second value.

Have a look at this solution and see if you can adapt it to your situation

Solved: Re: Highlight row if unique values exist within dy... - Splunk Community

[mistydennis]

Yes, that is what I'm looking for. But I'm having trouble as well formatting my query correctly - I'm not sure what the correct syntax is. 

I read another post here which you answered (🙂) and said to try this:

| eval newtime=if(relative_time(_time,"@d")=relative_time(now(),"@d"),mvappend(strftime(_time,"%Y-%m-%d"),"today"), if(relative_time(_time,"@d")=relative_time(now(),"-7d@d"),mvappend(strftime(_time,"%Y-%m-%d"),"overdue"),strftime(_time,"%Y-%m-%d")))

But I'm not sure how to adjust this for my case - I tried plugging in dueDate instead of _time, it did not work. And I don't know how to format the strf and strptime correctly so that I am going beyond the past 7 days. 

[ITWhisperer]

Is dueDate a string or an epoch time?

[mistydennis]

It is a string.

[ITWhisperer]

You should convert it an epoch time with strptime() so you can compare with the time returned by relative_time()

[mistydennis]

Apologies, but dealing with timestamps has always been confusing for me. So still working with one of your answers from another post, I have:

| eval newduedate=strptime(dueDate, "%Y-%m-%d")
| eval newduedate=if(relative_time(dueDate,"@d")=relative_time(now(),"@d"),mvappend(strftime(dueDate,"%Y-%m-%d"),"today"),
if(relative_time(dueDate,"@d")=relative_time(now(),"-7d@d"),mvappend(strftime(dueDate,"%Y-%m-%d"),"overdue"),strftime(dueDate,"%Y-%m-%d")))

I understand I'm converting to epoch time with the first line, but after that I get lost. And this produces no results, so I'm clearly doing something wrong.

[ITWhisperer]

Try something like this

| eval newduedate=strptime(dueDate, "%Y-%m-%d")
| eval dueDate=if(newduedate=relative_time(now(),"@d"),mvappend(dueDate,"today"),
if(newduedate=relative_time(now(),"-7d@d"),mvappend(dueDate,"overdue"),dueDate))

newduedate is the epoch version of dueDate (which remains a string)