Hey,
I’m trying to create a table of schedule in my weekly note from items scheduled on my daily notes.
My daily notes are titled yyyyMMdd-ddd
. In my daily notes I have the format:
[cal:: appt description] [schedule:: yyyy-MM-ddTHHmm]
In my weekly notes I have the field monday-date::yyyy-MM-dd
. In these, I’m trying to roll up the daily calendar fields so that I get a table with
| file/date | HH:mm | calendar item |
I’ve tried:
table dateformat(schedule,"HH:mm") as "Time", cal as "Item"
from "notes/2021"
where note-type="daily"
and dateformat(schedule,"yyyy-MM-dd") >= dateformat(this.monday-date,"yyyy-MM-dd") and dateformat(schedule,"yyyy-MM-dd") <= dateformat(this.monday-date,"yyyy-MM-dd") + dur(7 days)
sort file.day desc
This returns zero results.
A couple of notes:
- changing the first
>
to<
in the secondwhere
query (so that it readsdateformat(schedule,"yyyy-MM-dd") <= this.monday-date
shows the schedule from Monday… not sure why >= and <= the same day would not both show that day -
file.day.week
= the 1,2, etc week of that month (e.g. returns 1 for the first week of october, instead of the universal week of the year) -
file.date.week/month/etc
does not return anything - I don’t always create the file during the week, sometimes I create it before so
file.ctime
is not an option - If I remove the 3rd
where
clause so that thewhere
query readswhere note-type="daily" and dateformat(schedule,"yyyy-MM-dd") >= this.monday-date
I get returns from the prior Saturday (very weird) and the following Wednesday only, even though I have tasks Monday, Tues, Wed & Thurs -
dateformat(file.monday-date, "yyyy-MM-dd") + dur(1 day) does not return the following day as expected, and instead produces
2021-10-04null` which probably means it’s not really/always being treated as a date? but I don’t know enough to read through the lines and learn from this. - and small aside, when adding the date to the
schedule
field, e.g.schedule:: 2021-10-05T06:18:00
, the dateformat command reads the first value after the T as the hour instead of as the timezone, so I’ve adjusted the formatting for my scheduling (this may be a bug? I’m not sure)
Thanks for any help!