I am would like to query credit card payments of the past months. Each payment is registered in a daily note using a template with inline fields (e.g. product ,bank account, card, etc.).
Things I have tried
I am using an approach derived from this post which looks like this:
Table product, bank, amount, card
FROM #creditcard AND -"Templates"
WHERE file.cdate >= date(today)-dur(2 month)
I have tried several combinations of the where condition, fooling around with file.cmonth, file.cdate.month, dateformat and more functions and also varying the number of past months and the direction of the greater/lesser symbols. All approaches yielded the two results below:
either all payments were listed
or no payment at all
What might be the problem
Reading Dataview Manual did not help me understand howimplicit dates such as file.cdate are collected. Obtaining the two results shown above despite using several different conditions, makes me think that all my files have the same (incorrect) file.cdate value.
My questions to the community
How can I check the values of implicit metadata such as file.cdate or file.mdate?
Are these implicit dates generated based on the file names? My daily notes are named following this date format “dd.MM.yyyy”. Could this be the problem?
Is my query simply wrong? Do you have other suggestions?
*Hint:* `file.day` returns a valid date when *either* of these conditions are true:
* an “understandable” date in the file title (=file name), for instance “YYYY-MM-DD” (Daily Notes format), or “YYYYMMDD” (à la Zettelkasten date),
* a `date: YYYY-MM-DD` (or longer, ISO format) in the note’s YAML frontmatter.
My YAML frontmatter has an entry in a close to ISO format (e.g. “creation date: 2023-03-14 08:54”). However, I realized my frontmatter was missing the framing “—” at the beginning and end of the YAML block. So I used the following terminal command (in Linux) to correct this mistake in all my daily notes.
sed -i '1s/^/---/' *.md && sed -i '6s/^/---/' *.md
Again replying, since I don’t have permissions to edit my own post…
I answered question 1. using the following query:
TABLE file.day, file.cdate
Interestingly, file.cdate is empty (showing “-”) in all files, while file.day is a valid date only in my excelidraw files. All daily notes show “-”. Confirming my first guess on why the query does not work. My daily notes are named in the format “dd.MM.yyyy” as stated above. The YAML frontmatter blocks reads
Be very careful when referencing metadata variables. The implicit fields for creation and last modification date are file.cday and file.mday, notcdate and mdate.
No the implicit dates mentioned above are based on the file stats. However the file.day which is also an implicit date related to a file does depend on the date format. More on that later on.
So the query isn’t wrong, it’s just referencing the wrong stuff…
Sadly, close to ISO format isn’t good enough. It has to be that format, or a portion of that format. The minimum needs to be something like 2023-03, and a fuller variant could be like 2023-03-13T21:31.
Also, if the field is not within a section limited by the --- on separate lines, it’s not in the YAML (or frontmatter) section.
And on a side note, if you want to define fields in the normal text, that would require double colons, ::. (That is outside of the YAML, use double colons)
As mentioned, the file.cday and file.mday are picked up from file statistics. Neither of these are affected by any field in the frontmatter or not. A field like creation date is a totally different field, and could be referenced using file["creation date"]
Some more on the file.day
The file.day is a little more interesting as to how it’s set. It can be set using the date field in the frontmatter, or by using a valid date, aka YYYY-MM-DD somewhere in the file name. This can be rather useful in some circumstances.
For a little more on this subject, and some examples see my answer in the thread below: