How to get the last Sunday of each month?

[ettaly]

Hello,

Can I know how to get the last Sunday of each month? For example, 31st is last Sunday of Jan 2021, 28th is last Sunday of Feb 2021, 28th is last Sunday of March 2021... Thank you.

[bowesmana · Accepted solution]

Here's another one that calculates sundays from 1971 to 2030

It simply gets the first of the month then finds the previous Sunday for each month

| makeresults 
| fields - _time
| eval month=mvrange(1,721)
| mvexpand month
| eval month=month % 12 + 1
| streamstats c as group
| eval year=(floor((group / 12)) + 1971)
| eval first=strptime(year."-".month."-01", "%Y-%-m-%d")-1
| eval sunday=relative_time(first,"@w0")
| fieldformat sunday = strftime(sunday,"%A %Y-%m-%d")
| eval date=strftime(sunday, "%b %Y")
| table date sunday

[bnikhil0584]

Hi,

I've a similar use case but to get the second and last Saturday's. I tried to use your search but couldn't get there. Can you please help me with that. TIA

[ITWhisperer]

Assuming you have some events covering these times, you can try this sort of thing

| where date_wday = "sunday" | stats max(date_mday) as lastSunday by date_month

[pclewis]

I am sure there is an elegant mathematical way to do it but generally I just solve things like this the same way I'd do it with a spreadsheet..   Brute force...

| makeresults 
| rename REM as "decide your start date and end date" 
| eval start=strptime("2020-06-12","%Y-%m-%d") 
| eval end=strptime("2021-06-12","%Y-%m-%d") 
| rename REM as "%w	Weekday as a decimal number. (0 = Sunday, ..., 6 = Saturday)" 
| rename REM as "figure out date of the sunday prior to start date" 
| eval date_start_sunday=start- 86400*tonumber(strftime(start,"%w")) 
| rename REM as "figure out days between start and end - round up/down or whatever you like" 
| eval date_end_sunday=end +(7- 86400*tonumber(strftime(end,"%w")) ) 
| rename REM AS "use mvrange to make the list of sundays"
| eval date_sunday=mvrange(date_start_sunday,date_end_sunday,7*86400) 
| mvexpand date_sunday

| eval year=strftime(date_sunday,"%Y"), month=strftime(date_sunday,"%m"), day=strftime(date_sunday,"%d")
| sort +date_sunday
| stats  max(day) as maxday by year month
| eval date_sunday=strptime(year . "-" . month . "-" . maxday,"%Y-%m-%d")

| fieldformat date_sunday = strftime(date_sunday,"%A %Y-%m-%d")