Hello there. I am currently using the full calendar plugin to manage events stored in one of my vaults. The existing “list” view in full calendar wasn’t exactly what I wanted, so I tried playing with Dataview to make a summary list of the current and coming entries.
Where I’m at
I managed to get a basic queries for current and pending events running.
This is my ‘current events’ code:
TABLE WITHOUT ID title as "Entry", date as "Start", dateEnd as "End"
FROM "Calendar"
WHERE dateEnd >= date(now)
SORT dateEnd
This is my ‘pending events’ query:
TABLE WITHOUT ID title as "Event", date as "Start", endDate as "End"
FROM "Calendar"
WHERE date >= date(now)
SORT date, endDate
The issues
The current roadblock is handling non-full day events. For those, Full Calendar stores times in two separate metadata (startTime and endTime, respectively) and also uses boolean metadata allDay.
Ideally I’d want both lists to more or less update in real time, an event finishing at 17:00 on 08/11 would be included on the current events list when checking it on that day at 16:00, but would be gone if checking it at 17:15. And a pending event starting at 16:30 on 08/11 would only show on the current events list after its stored start time.
I have tried various ways of concatenating the date+time fields, but comparing them using the date(now) function hasn’t worked yet (my SQL is quite rusty).
First of all using date(now) includes the time portion, while date(today) only includes the date part. This can trip up comparisons related to current dates since this means that date(now) > date(today).
Secondly, I’ve found doing time comparisons to be a little icky, since if you’ve got a field with just 16:30 or similar you’ll need to prepend an actual date to make it a fully qualified date.
I’m on mobile now, so I’m not able to produce examples easily, but you’ll need to play around with adding the date part, or possibly using the format parameter (the 2nd parameter) to date() to build a proper date to use for time comparisons.
Some progress.
After a bit too many tries (and a lot of bruteforcing thanks to having no kind of error console or such), I managed to get to this:
TABLE WITHOUT ID
link(file.name,title) as "Event",
dateformat(dateStart,"HH:mm dd/LL/yyyy") as "Start",
dateformat(dateEnd,"HH:mm dd/LL/yyyy") as "End",
FROM "Calendar"
FLATTEN (date((dateformat(endDate, "yyyy-LL-dd")+"T"+endTime))) as dateEnd
FLATTEN (date((dateformat(date, "yyyy-LL-dd")+"T"+startTime))) as dateStart
WHERE dateStart < date(now)
AND dateEnd >= date(now)
SORT dateEnd
There’s probably a more elegant way to do it, but it seems to work. For some time I though about replacing the end date with “ends in X days, Y hours, Z minutes” and making a second query for the next events with “starts in X days, Y hours, Z minutes” but it’s way above my skill level.