Splunk Search

How to find the week number of a date in a month and same week(number) in previous month

mani
Explorer

I have a date field in "%m/%d/%Y" format.

I need to find the week number of this date and find the same week number of previous month.

For example:

my_date = 07/13/2020 - day in 3rd week of July 2020 (07/12/2020 to 07/18/2020)

I need to get values of 3rd week of June 2020 (06/14/2020 to 06/20/2020)

@All, Please help. Appreciate quick response.

 @niketnilay  @lspringer  @carasso 

Labels (2)
1 Solution

javiergn
SplunkTrust
SplunkTrust

Hi @mani ,

 

See if the following helps:

| makeresults | fields - _time
| eval my_date = strptime("07/13/2020", "%m/%d/%Y")
| eval my_date_week = strftime(my_date, "%V")

| eval my_date_month = relative_time(my_date, "@mon")
| eval my_date_month_week = strftime(my_date_month, "%V")
| eval my_date_weekNumber_inMonth = my_date_week - my_date_month_week

| eval my_date_previous_month = relative_time(my_date, "-1mon@mon")
| eval my_date_previous_month_week_start = relative_time(my_date_previous_month + (7 * my_date_weekNumber_inMonth * 86400), "@w0")
| eval my_date_previous_month_week_end =  my_date_previous_month_week_start + (7 * 86399)

| fieldformat my_date = strftime(my_date, "%m/%d/%Y")
| fieldformat my_date_previous_month_week_start = strftime(my_date_previous_month_week_start, "%m/%d/%Y")
| fieldformat my_date_previous_month_week_end = strftime(my_date_previous_month_week_end, "%m/%d/%Y")

| table my_date, my_date_previous_month_week_start, my_date_previous_month_week_end

 

Output: 

Screenshot 2020-07-22 at 08.31.45.png

Couple of things: 

* I am assuming your week starts on Sunday

* 1 day has 86400 seconds but I am subtracting 1 second on line 9 to ensure your date ends on the last second of that week. That is, 06/20/2020 at 23:59:59, instead of ending at 06/21/2020 at 00:00:00 and therefore displaying 21 instead of 20.

 

Let me know if that helps.

Regards,

J

 

View solution in original post

javiergn
SplunkTrust
SplunkTrust

Hi @mani ,

 

See if the following helps:

| makeresults | fields - _time
| eval my_date = strptime("07/13/2020", "%m/%d/%Y")
| eval my_date_week = strftime(my_date, "%V")

| eval my_date_month = relative_time(my_date, "@mon")
| eval my_date_month_week = strftime(my_date_month, "%V")
| eval my_date_weekNumber_inMonth = my_date_week - my_date_month_week

| eval my_date_previous_month = relative_time(my_date, "-1mon@mon")
| eval my_date_previous_month_week_start = relative_time(my_date_previous_month + (7 * my_date_weekNumber_inMonth * 86400), "@w0")
| eval my_date_previous_month_week_end =  my_date_previous_month_week_start + (7 * 86399)

| fieldformat my_date = strftime(my_date, "%m/%d/%Y")
| fieldformat my_date_previous_month_week_start = strftime(my_date_previous_month_week_start, "%m/%d/%Y")
| fieldformat my_date_previous_month_week_end = strftime(my_date_previous_month_week_end, "%m/%d/%Y")

| table my_date, my_date_previous_month_week_start, my_date_previous_month_week_end

 

Output: 

Screenshot 2020-07-22 at 08.31.45.png

Couple of things: 

* I am assuming your week starts on Sunday

* 1 day has 86400 seconds but I am subtracting 1 second on line 9 to ensure your date ends on the last second of that week. That is, 06/20/2020 at 23:59:59, instead of ending at 06/21/2020 at 00:00:00 and therefore displaying 21 instead of 20.

 

Let me know if that helps.

Regards,

J

 

mani
Explorer

Thanks @javiergn.

It's working as expected.

 

Tags (1)
0 Karma
Get Updates on the Splunk Community!

Maximize the Value from Microsoft Defender with Splunk

<P style=" text-align: center; "><span class="lia-inline-image-display-wrapper lia-image-align-center" ...

This Week's Community Digest - Splunk Community Happenings [6.27.22]

<FONT size="5"><FONT size="5" color="#FF00FF">Get the latest news and updates from the Splunk Community ...