How to calculate time for a given day of week?

[yuanliu]

How to perform calculations on a given day of week?  Specifically, I want to compare a given time value, say given_date, with a given day of week of a given week, say, next Friday.

If I want to perform the calculation, say, for a week from now, I can use

if(given_date > time() + 7 * 86400, "later", "earlier")

(This can be easily adjusted for beginning of day, etc.)  If today is Friday, the above will tell me whether given_date is earlier than or later than next Friday.  But if today is Monday, it only tells me whether it is earlier or later than next Monday.

For event data, I can extract day of week from date_wday.  Is there a calculator/function to do that for arbitrary time value?  I suppose I can use strftime(time(), "%w")  to determine delta from desired day of week, then add/subtract whole weeks to the desired day of week. (Unlike date_wday, strftime() gives a numeric value that is easy for calculation.)  Is there a more direct way?

[ITWhisperer]

I am not sure I understand what you are trying to do. If today is a Wednesday, and your given day is Friday, do you want to determine the date of the next Friday (this week), or the Friday next week? Similarly, if the given day is Monday, do you want to determine the date of the next Monday (next week) or the Monday at least a week away (the following week)?

[yuanliu]

Here is an example.  If my given_date is 2021-12-19, I want to know if this is earlier than/equal to, or later than any December Fridays.  In the calendar:

Fridays of December 2021	2021-12-19
-2 weeks (12-3)	later than
-1 week (12-10)	later than
this week (12-17)	earlier than
+1 week (12-24)	earlier than
+2 weeks (12-31)	earlier than

As mentioned, it is possible to do

| eval date_comp = if((5 - strftime(time(), "%w") + offset_wk * 7) * 86400 > given_date, "later", "earlier")

Here, offset_wk is the look-ahead or look-back value from today as shown in the illustrated table; current week is 0, previous week is -1, next week is 1, and so on.  But this seems to be a lot of code for a simple ask.

[ITWhisperer · Accepted solution]

Surely 19th is later than 17th?

| makeresults
| eval givendate=strptime("2021-12-19","%F")
| eval week=mvrange(-2,3)
| mvexpand week
| eval week=printf("%+d",week)
| eval date=relative_time(givendate,week."w@w-1w+5d")
| eval date_comp=if(date<givendate,"later","earlier")
| fieldformat date=strftime(date,"%A %F")
| fieldformat givendate=strftime(givendate,"%A %F")

[yuanliu]

---

@ITWhisperer wrote:

Surely 19th is later than 17th?

---

😂my bad

relative_time() is exactly the syntax shortcut I was looking for!

For anyone who may have a similar need, the key point is that relative_time() accepts expressive syntax like "w@w-1w+5d", so intention is no longer obscured. ("w-1w" is not exactly needed.)  Necessary code is captured in these two lines:

| eval date=relative_time(givendate,week."w@+5d")
| eval date_comp=if(date<givendate,"later","earlier")

 Here, givendate is a numeric representation of the date, and week is a string representation of the desired offset (in weeks); 5 (numeric for Friday) can also be any other number that represents a day of week.

[ITWhisperer]

Actually, it is just the -1w that is not needed if the calculation is done against now instead of givendate (my bad). The @w is still need to snap to the beginning of the week that now() is in before adjusting by -2 through +2 weeks plus 5 (for Friday)

| makeresults
| fields - _time
| eval givendate=strptime("2021-12-19","%F")
| eval week=mvrange(-2,3)
| mvexpand week
| eval week=printf("%+d",week)
| eval date=relative_time(now(),week."w@w+5d")
| eval date_comp=if(date<givendate,"later","earlier")
| fieldformat date=strftime(date,"%A %F")
| fieldformat givendate=strftime(givendate,"%A %F")