Splunk Search

How to calculate time for a given day of week?

yuanliu
SplunkTrust
SplunkTrust

How to perform calculations on a given day of week?  Specifically, I want to compare a given time value, say given_date, with a given day of week of a given week, say, next Friday.

If I want to perform the calculation, say, for a week from now, I can use

if(given_date > time() + 7 * 86400, "later", "earlier")

(This can be easily adjusted for beginning of day, etc.)  If today is Friday, the above will tell me whether given_date is earlier than or later than next Friday.  But if today is Monday, it only tells me whether it is earlier or later than next Monday.

For event data, I can extract day of week from date_wday.  Is there a calculator/function to do that for arbitrary time value?  I suppose I can use strftime(time(), "%w")  to determine delta from desired day of week, then add/subtract whole weeks to the desired day of week. (Unlike date_wday, strftime() gives a numeric value that is easy for calculation.)  Is there a more direct way?

Labels (1)
Tags (1)
0 Karma
1 Solution

ITWhisperer
SplunkTrust
SplunkTrust

Surely 19th is later than 17th?

| makeresults
| eval givendate=strptime("2021-12-19","%F")
| eval week=mvrange(-2,3)
| mvexpand week
| eval week=printf("%+d",week)
| eval date=relative_time(givendate,week."w@w-1w+5d")
| eval date_comp=if(date<givendate,"later","earlier")
| fieldformat date=strftime(date,"%A %F")
| fieldformat givendate=strftime(givendate,"%A %F")

View solution in original post

ITWhisperer
SplunkTrust
SplunkTrust

I am not sure I understand what you are trying to do. If today is a Wednesday, and your given day is Friday, do you want to determine the date of the next Friday (this week), or the Friday next week? Similarly, if the given day is Monday, do you want to determine the date of the next Monday (next week) or the Monday at least a week away (the following week)?

0 Karma

yuanliu
SplunkTrust
SplunkTrust

Here is an example.  If my given_date is 2021-12-19, I want to know if this is earlier than/equal to, or later than any December Fridays.  In the calendar:

Fridays of December 20212021-12-19
-2 weeks (12-3)later than
-1 week (12-10)later than
this week (12-17)earlier than
+1 week (12-24)earlier than
+2 weeks (12-31)earlier than

As mentioned, it is possible to do

| eval date_comp = if((5 - strftime(time(), "%w") + offset_wk * 7) * 86400 > given_date, "later", "earlier")

Here, offset_wk is the look-ahead or look-back value from today as shown in the illustrated table; current week is 0, previous week is -1, next week is 1, and so on.  But this seems to be a lot of code for a simple ask.

0 Karma

ITWhisperer
SplunkTrust
SplunkTrust

Surely 19th is later than 17th?

| makeresults
| eval givendate=strptime("2021-12-19","%F")
| eval week=mvrange(-2,3)
| mvexpand week
| eval week=printf("%+d",week)
| eval date=relative_time(givendate,week."w@w-1w+5d")
| eval date_comp=if(date<givendate,"later","earlier")
| fieldformat date=strftime(date,"%A %F")
| fieldformat givendate=strftime(givendate,"%A %F")

yuanliu
SplunkTrust
SplunkTrust

@ITWhisperer wrote:

Surely 19th is later than 17th?


😂my bad

relative_time() is exactly the syntax shortcut I was looking for!

For anyone who may have a similar need, the key point is that relative_time() accepts expressive syntax like "w@w-1w+5d", so intention is no longer obscured. ("w-1w" is not exactly needed.)  Necessary code is captured in these two lines:

 

| eval date=relative_time(givendate,week."w@+5d")
| eval date_comp=if(date<givendate,"later","earlier")

 

 Here, givendate is a numeric representation of the date, and week is a string representation of the desired offset (in weeks); 5 (numeric for Friday) can also be any other number that represents a day of week.

Tags (1)
0 Karma

ITWhisperer
SplunkTrust
SplunkTrust

Actually, it is just the -1w that is not needed if the calculation is done against now instead of givendate (my bad). The @w is still need to snap to the beginning of the week that now() is in before adjusting by -2 through +2 weeks plus 5 (for Friday)

| makeresults
| fields - _time
| eval givendate=strptime("2021-12-19","%F")
| eval week=mvrange(-2,3)
| mvexpand week
| eval week=printf("%+d",week)
| eval date=relative_time(now(),week."w@w+5d")
| eval date_comp=if(date<givendate,"later","earlier")
| fieldformat date=strftime(date,"%A %F")
| fieldformat givendate=strftime(givendate,"%A %F")

 

Tags (1)
Get Updates on the Splunk Community!

Introduction to Splunk Observability Cloud - Building a Resilient Hybrid Cloud

Introduction to Splunk Observability Cloud - Building a Resilient Hybrid Cloud  In today’s fast-paced digital ...

Observability protocols to know about

Observability protocols define the specifications or formats for collecting, encoding, transporting, and ...

Take Your Breath Away with Splunk Risk-Based Alerting (RBA)

WATCH NOW!The Splunk Guide to Risk-Based Alerting is here to empower your SOC like never before. Join Haylee ...