How to write a search to get the week number of the month from the given _time?

[prachisaxena]

We need to extract the week number of the month for matching the SLA. Have SLA such as 2nd or 4th week of a month.
So it is needed to extract the week no from the event date.

My time format is something like below and we need to get output like "11 April 2016 is 2nd Monday of April." and "15 April is 3rd Friday of April"
eg:
_time= 2016/04/11 15:29:12

[gabriel_vasseur · Accepted solution]

How about:

... | eval day=strftime(_time,"%d") | eval day_number=floor(day/7)+1 | eval str=strftime(_time,"%A") . " number " . day_number . " of " . strftime(_time, "%B" )

Test it and see if it does what you want. Note: you will want to tweak that to have "second" instead of "number 2", "third" instead of "number 3", etc...

[netghod]

One small issue with this logic:

eval day_number=floor(day/7)+1

As it results in the 7th, 14th, 21st, and 28th reporting in the following week.  Week 1 should be days 1-7, 2 would be 8-14, etc.

You need to modify it slightly to land those days on the proper week because they're evenly divisible and result in a +1 to the week they're actually in.

eval day_number=floor((day-1)/7)+1

And this is an old post, but since I'm using this logic and much appreciate the solution, thought I'd point out the slight tweak needed for it to work 100% if anyone searches in the future.

[prachisaxena]

Hi Gabriel,

Thank you so much for answering.
However, the given query is giving me the day number of week and month as below. What i actually need is the week number. Like we have 4 weeks in a month - week1 -> day 1-7 ...... week 2 -> day 1-7

such that for _time = 2016-03-31 11:30:00 --> 31st march is 5th Thursday of March

_time=2016-03-31 11:30:02

day =31
day_number=5

str=Thursday number 5 of March

[gabriel_vasseur]

Just to be complete, here is one way to convert the day number in a rank:

... | eval day_rank=case(day_number=1,"first", day_number=2,"second", day_number=3,"third", day_number=4,"fourth", day_number=5,"fifth" ) | ...

You can then rearrange the final concatenation to use day_rank instead of day_number.

[somesoni2]

With some additional string concatenation, above works. See this run anywhere sample (line 1 to generate data)

| gentimes start=-1 | eval _time=strptime("03/31/2016","%m/%d/%Y") | table _time 
| eval day=strftime(_time,"%d") | eval day_number=floor(day/7)+1 | eval str=strftime(_time,"%F")." is number ".day_number." ".strftime(_time,"%A").  " of month " . strftime(_time, "%B" )

Output
2016-03-31 is number 5 Thursday of month March

[prachisaxena]

yes, its working .. thank you