Dataview query data from past month(s) / How to verify implicit data?

What I’m trying to do

Hi all,
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 how implicit 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

  1. How can I check the values of implicit metadata such as file.cdate or file.mdate?
  2. 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?
  3. Is my query simply wrong? Do you have other suggestions?

Thank you so much in advance!

Best
Siggi

Sorry, could not edit my own post

Edits

According to Moonbase59

*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 

Where 1s and 6s are the lines where “—” will be written (Addresses - sed, a stream editor).

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

---
creation date: <% tp.file.creation_date() %>
tags: #DailyNote  
modification date: <%+ tp.file.last_modified_date("dddd Do MMMM YYYY HH:mm:ss") %> 
---

which is rendered

---
creation date: 2023-03-14 08:54
tags: #DailyNote  
modification date: NaN 
---

So now this question remains
4. Why dataview fails in extracting valid dates from my daily notes? Should I change the fields key names (e.g. “day” instead of “creation date”) or something?

Would love some help here :slight_smile:
Best
Siggi

Finally I found a solution.
The problem was caused by an invalid YAML frontmatter.

  • Apparently, the creation key needs to be named “date” (cdate or creation date did not work).
  • Furthermore, only ISO format dates are recognized.

I solved this in two steps:

  1. I updated my Daily template’s frontmatter to the following
---
date: <%tp.file.creation_date("YYYY-MM-DDTHH:mm")%>
tags: #DailyNote  
mdate: <% tp.file.last_modified_date("YYYY-MM-DDTHH:mm:ss") %> 
---
  1. I corrected the frontmatter of all my daily notes using following sed commands.

Careful here! Backup your notes!

  1. Add missing --- at beginning and end of frontmatter
sed -i '1s/^/---/' *.md && sed -i '6s/^/---/' *.md 
  1. Replace blank space in between date and time by T
sed -i '2s/\(.\{25\}\)\(.\{1\}\)/\1T/' *.md
  1. Delete "creation " at the beginning of every second line
sed -i '2s/^creation //' *.md

Hope this helps!
Best
Siggi

Be very careful when referencing metadata variables. The implicit fields for creation and last modification date are file.cday and file.mday, not cdate 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… :slight_smile:

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. :slight_smile:

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:

This topic was automatically closed 7 days after the last reply. New replies are no longer allowed.