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...

[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