Splunk Search
Highlighted

How to convert Week of Year number to a standard Date format?

Path Finder

Hi,

I have a set of data where the date is stored as Year/Week of Year.

For example: this week would be 14/43 while the first week of January first would be 14/1.

I would like to convert this into something easily readable, likely Month/Day/Year.

As Week of Year doesn't seem to be a standard time/date variable, I'm having trouble figuring out a solution using time conversion methods. Any pointers would be welcome.

Tags (4)
0 Karma
Highlighted

Re: How to convert Week of Year number to a standard Date format?

Motivator

Is this based on Fiscal calendar? what i am going to suggest might be the hardest way. Here's what i think should do.
Going by your example, 14/43
i would extract it rex "^.*\/(\d+)\s". Now i should have Week field = 43
A giant if loop , Eval Month = if((Week<=5),"January",if(Week>5 AND Week <=10),February..........)
Now you have both Week and Month fields. Concatenate Year and Month.

0 Karma
Highlighted

Re: How to convert Week of Year number to a standard Date format?

Motivator

Sorry the rex is ended up all wrong. "^.*\/(?\d+)\s"

0 Karma
Highlighted

Re: How to convert Week of Year number to a standard Date format?

SplunkTrust
SplunkTrust

Try something like this (runanywhere sample)

|gentimes start=-1 | eval temp="14/43" | table temp |  eval newDate=relative_time(strptime(mvindex(split(temp,"/"),0)."/01/01","%y/%m/%d") + tonumber(mvindex(split(temp,"/"),1))*86400*7 - 86400,"@w") | convert ctime(newDate)

You can use the expression into a macro (eval newDate).

Highlighted

Re: How to convert Week of Year number to a standard Date format?

SplunkTrust
SplunkTrust

Assuming your week starts with a Monday, here's a pretty way of doing this:

| stats count | eval in = "2014/43" | eval out = strptime(in." 1", "%Y/%U %w") | eval formatted = strftime(out, "%+")

That yields Monday, October 27th 00:00:00 for me. Change the 1 in the strptime() to 0 if your weeks start with Sunday.

Highlighted

Re: How to convert Week of Year number to a standard Date format?

Path Finder

How about if the format is YYYY-MM-WW? ex. 2017-10-41.

0 Karma
Highlighted

Re: How to convert Week of Year number to a standard Date format?

SplunkTrust
SplunkTrust

Strip out the month and go from there? The week number should be sufficient.

Alternatively, add the month to the strptime.

0 Karma
Speak Up for Splunk Careers!

We want to better understand the impact Splunk experience and expertise has has on individuals' careers, and help highlight the growing demand for Splunk skills.