Splunk Search

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

mrfredman
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

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

mschellhouse
Path Finder

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

0 Karma

martin_mueller
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

somesoni2
Revered Legend

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

Raghav2384
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

Raghav2384
Motivator

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

0 Karma
Get Updates on the Splunk Community!

Splunk Forwarders and Forced Time Based Load Balancing

Splunk customers use universal forwarders to collect and send data to Splunk. A universal forwarder can send ...

NEW! Log Views in Splunk Observability Dashboards Gives Context From a Single Page

Today, Splunk Observability releases log views, a new feature for users to add their logs data from Splunk Log ...

Last Chance to Submit Your Paper For BSides Splunk - Deadline is August 12th!

Hello everyone! Don't wait to submit - The deadline is August 12th! We have truly missed the community so ...