I’ve really struggled with learning Dataview. I feel like I have a simple question, “When was the last time my cat used the litterbox?” but it’s difficult to find examples that provide both a query and data (e.g. the official docs show inline fields, but not how to query them).
A regular SQL query might look like this
and I had imagined using tags or inline fields in my daily notes, which might look like
When I tried asking this on reddit, someone suggested Frontmatter. Unless I’m missing something, that would require creating a new file for each litter use. Creating new files would add a lot of noise, when I thought it would be as simple as manually putting the timestamp on a tagged line.
Can someone provide an example of this? Ideally in DataviewJS but the SQL-like language might be fine for getting started. If I’m fundamentally misunderstanding Dataview here you’re welcome to correct me as well, since it feels like I’m missing something obvious.
Query inline fields or frontmatter fields is the same thing, no difference. The differences are in implicit data or custom data.
Dataview works with data, not with the full content of your note. And in a generic way, the common ground for the multiple fields you create in a specific note is the page level, i.e, I can write this:
This is a line with (key:: valueA) and a #tag.
and the only related thing between this group of data (inline fields, tags, …) is the page level (the note):
there’s no direct relation between #mycatand key:: valueB
at page level, key has two values (a list): valueA and valueB
So, the first thing to clarify is: are we talking about a specific metadata with a unique or multiple values in each file?
You can try multiple things, but if you don’t understand how metadata is structured in dataview you won’t know how to write and query them.
Another point. To explore the max() or the min(), in the case you’re interested in time values. But the thing is: with the used format the values are strings, not dates, times or durations. So, even with the right metadata structure and the right query, the result will fail because you’re asking for the max of a string (where it’ll apply the alphabetic order, not the time value)
One question, you say daily notes, are they named with a date, like in yyyy-MM-dd? If so, they could (somewhat) easily be combined with your time stamp into a complete date+time which should be readily available for comparison to see which is the last of them.
And I reckon that with max(used_time) you do mean the latest available time present in that note?
This should be doable, even though I/we need to thinker a little to get it written…
rows.L AS StringTime,
rows.Ltime AS StringToDate,
max(rows.Ltime) AS MaxStringToDate,
dateformat(max(rows.Ltime), "HH:mm") AS FormatMaxStringToDate
FLATTEN litterbox_time AS L
FLATTEN date("2022-01-01T" + L) AS Ltime
GROUP BY file.link
When there is only one playtime for a day, an additional dummy value needs to be added to get a list instead of a scalar
I have to update the date manually in two places (x3 for wet food and litter use)
It cannot cross midnight
This is fine for now, although long-term I want to implement more complicated logic with those three things such that a collapsed call out “everything is fine” is the default and more detail shows automatically if too much time passes.