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