given a date find which week of the year

[ajaylowes]

given a date find which week of the year

For example : if date is "27-Feb-17" the result will be "09".
As 27th Feb 2017 comes in 9th Week of that year.

Another example: 8-Mar-17 will give "10" result.

[ajaylowes]

Sorry but i missed one information earlier.
I am working on .csv file which has column "date" on which we need to apply the above logic .
eval WeekOfYear= strftime(date,"%V") - this doesn't work for me

[cmerriman]

try adding this:

|eval weeknum=strftime(_time,"%V")

I believe it runs Monday-Sunday, regardless of the day the year started. I also don't believe it's supported by Splunk, as it's not listed here: http://docs.splunk.com/Documentation/Splunk/latest/SearchReference/Commontimeformatvariables

[ajaylowes]

Sorry but i missed one information earlier.
I am working on .csv file which has column "date" on which we need to apply the above logic .
eval WeekOfYear= strftime(date,"%V") - this doesn't work for me

[cmerriman]

you'll need to make that into epoch time.

| eval date=strptime(date,"%d-%b-%y")
| eval yearNum=strftime(date,"%V")

[niketn]

@cmerriman, seemed like %U and %V were missing in Splunk Documentation. I have submitted a feedback for adding the same.

%U- Week number of the year [0-53], Sunday is the first day of the week. Used in calculating the day of the year.
%V- ISO week number of the year [1-53]. Monday is the first day of the week. If the week containing January 1st has four or more days in the new year, it is considered week 1. Otherwise, it is the last week of the previous year, and the next week is week 1 of the new year. Used in calculating the day of the year.

[adonio]

not sure how to do it but you can quickly create an excel spreadsheet like explained below:
https://exceljet.net/formula/get-week-number-from-date
save as csv -> upload as a lookup -> use as you wish
hope it helps

[niketn · Accepted solution]

@ajaylowes, you can try %U date time conversion for Week of the Year
Following is run anywhere search:

| makeresults
| eval WeekOfYear=strftime(_time,"%U")

[ajaylowes]

for eval WeekOfYear=strftime(_time,"%U") , instead of _time can it take value from a column say "SubmitDate"

[niketn]

Yes, however, SubmitDate should be epoch time and not string time.

Following is run anywhere search to convert to epoch time first prior to converting to Week Of Year

| makeresults
| eval SubmitDate="27-Feb-17"
| eval SubmitDate=strptime(SubmitDate,"%d-%b-%y")
| eval WeekOfYear=strftime(SubmitDate,"%U")

[niketn]

@ajaylowes, have you tried above example. strptime need to be run first on String Time.

[ajaylowes]

Does eval SubmitDate="17-Feb-17" accepts column name ( example: "date") and then apply this to all the values of that column ?

[niketn]

First two lines here are sample to generate mock data since you had not provided the name of the field earlier. 3rd and 4th lines perform the actual conversion that your need. Following is the query that you should apply to your base search:

 | eval date=strptime(date,"%d-%b-%y")
 | eval WeekOfYear=strftime(date,"%U")

However, I can see that @cmerriman has already suggested you this. Please try out and let us know if this does not work. If you face any issue please provide further details. Kindly upvote all comments that helped and accept any one of the solution once the issue is resolved.

[ajaylowes]

Thanks @niketnilay ....its working now

[richgalloway]

You'll probably have to write a custom command to do that.