What I’m trying to do
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"
WHERE dateEnd >= date(now)
This is my ‘pending events’ query:
TABLE WITHOUT ID title as "Event", date as "Start", endDate as "End"
WHERE date >= date(now)
SORT date, endDate
The current roadblock is handling non-full day events. For those, Full Calendar stores times in two separate metadata (
endTime, respectively) and also uses boolean metadata
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).
Any help would be appreciated.
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.
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",
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)
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.