Converting Date to first day of the week

[raysonjoberts]

I am analyzing some .csvs which have a "date" field present. The .csvs are indexed, but the index time is pretty irrelevant, however, the "date" field is important.

I am trying to create a new field which would represent the first day of the week relative to the "date" field in my data. Ultimately I am going to create some charts over time which will use this new field. Below is an example of my desired outcome - from the date present as a field in the .csv, create a new field (Summary Date) which shows the date of Monday for that week.

Date (present in .csv)	Summary Date  (new field)
6/15/2024	6/10/2024
6/16/2024	6/10/2024
6/18/2024	6/17/2024

* realizing there may be more than one way to skin the cat, ultimately I am looking to group results by week in Line Charts. The query will be very basic, something like this:

<base search>
| chart sum(results) by "Summary Date"

And I want the date shown on the X-axis to be the first day (Monday in my case) of every week. Maybe there is an easier solution than creating a new "Summary Date" field via an eval expression, but that is where my head goes first.
Any suggestions are appreciated!

[richgalloway · Accepted solution]

There is more than one way to do that.  If you want to create a new field, use eval with relative_time and strptime.

<<base search>>
| eval SummaryDate = relative_time(strptime(Date, "%d/%m/%Y"), "@w+1d")
| chart sum(results) over SummaryDate

Since "@w" snaps to Sunday, we use "+1d" to start the week on Monday.

Here's another way using the bin command.

<<base search>>
| bin _time span=1w
``` Convert _time from Sundays to Mondays ```
| eval SummaryDate = _time+86400
| chart sum(results) over SummaryDate

[bowesmana]

bin command also needs snap to, i.e.

| bin _time span=1w@w

or 

| bin _time span=1w aligntime=@w+1d

otherwise it just gives you 7 day chunks.

[raysonjoberts]

Works perfectly, thank you! I was dancing all around it but could not nail it down. That did it for me!