Displaying all files created from a particular day?

LIST " | <small>*Modified: "+file.mtime+"*</small> 
| <small>Created: "+file.ctime+"</small>"  
WHERE file.name != this.file.name
SORT file.ctime DESC LIMIT 15

This gives

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.

Please help :pray:

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).

I took “file.ctime” of the current file as the pivot, and display files till last 7 days.

TABLE file.ctime AS "Date"
WHERE file.name != this.file.name 
AND file.ctime >= this.file.ctime - dur(7 day)
SORT file.ctime ASC LIMIT 10

This is working, but i need to fetch files till last Wednesday ?

eg: If I created a weekly file today Friday, then the query should fetch files from Wednesday, Thursday & Friday only.

Is this possible ?

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.

1 Like

I’m not secure about this way (many “if” in play), but try this:

TABLE file.ctime AS "Date"
WHERE file.path != this.file.path
WHERE file.cday >= this.file.cday - dur(7 days)
FLATTEN number(dateformat(this.file.cday, "c")) AS Cday
WHERE (Cday > 3 AND file.cday >= this.file.cday - dur(Cday - 3 + " days")) OR (Cday <= 3 AND file.cday >= this.file.cday - dur(4 + Cday + " days"))
SORT file.ctime ASC
1 Like


This topic was automatically closed 7 days after the last reply. New replies are no longer allowed.