Now, for a “Weekly Note”, I want to fetch all notes from Last Wednesday till Today.
eg: If I am writing a weekly note on 25th October Tuesday, it should display all the files created from Last Wednesday ( 19th October, Wednesday ) till today.
I tried
TABLE file.ctime AS "Date"
WHERE file.name != this.file.name
AND file.ctime >= date(today) - dur(7 day)
SORT file.ctime ASC LIMIT 100
It fetches last 7 days, but I want files from last Wednesday only.
The main point is: what’s date(today)? It’s a current date, not any date related to the current note (your weekly note). So, you need to define a fixed date (a new custom field, an implicit metadata as file creation date, etc.), not a dynamic date as date(today).
Well, if the “Wednesday” is a fixed start point, there’s a no easy solution. For example, if your weekly note is created until Sunday (the 7th and the last day of the week in my local date format), then you can use this query:
TABLE file.ctime AS "Date"
WHERE file.name != this.file.name
WHERE file.ctime >= this.file.ctime - dur(7 days) AND dateformat(file.ctime, "c") >= "3"
SORT file.ctime ASC
(because Wednesday = 3th day of the week)
But if you create the weekly note after the last day of the week, then the query above remove Monday and Thursday (because 1 and 2 < that 3). And this is the main difficulty.
I think that is possible to solve (taken also the week number in stake), but now I don’t have time to see that. Maybe later.