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
select max(used_time)
from litter_uses
and I had imagined using tags or inline fields in my daily notes, which might look like
Woke up
#showered
Litterbox
#usedtime 2:30pm
or
usedtime:: 2:30pm
Ate #lunch
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.
key2:: value
#mycat
key:: valueB
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)
I’m surprised to hear that converting a string to a time isn’t a thing (especially with DataviewJS), but it seems like this answers the OP so thank you for your time.
I guess you can convert any string in js side to “build” a time value, but in raw format that value isn’t a time value! My point is related with your expression max(used_time)!
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…
This can be done in a more simple way, with the flatten command. But this example is for test purposes and to demonstrate a way - via bullet-point lists - to create “groups” of metadata.
Hehe, there you go, with a proper example on how to do it!
I reckon it could be slightly simpler given a unique field for the litterbox_time, or similar, which would eliminate the need for combining multiple fields, and just depend on a single field.
(Not at computer right now so I can’t produce an example)
TABLE
rows.L AS StringTime,
rows.Ltime AS StringToDate,
max(rows.Ltime) AS MaxStringToDate,
dateformat(max(rows.Ltime), "HH:mm") AS FormatMaxStringToDate
FROM #cat
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.
I’m not versed in JS, but maybe you need to force an array for all cases. For example,
dv.paragraph("Time since playtime 🦋: " +
(
dv.array(dv.page('Cats daily journal 2022-12-14')
.playtime)
.map(t => new Date("2022-12-14 " + t))
...
It helps if you define a templater to use every time you want to enter a playtime value… with the needed format. Be it 2022-12-14 16:42 or 2022-12-14T16:42.
