Unable to query 'Date & time' dates and Templater dates in same dataview table

Help
,
1

What I’m trying to do

I have set up a meeting note template that I can trigger from a meeting_MOC using the Meta Bind button builder. Upon creation, the date and time are filled into a ‘Date & time’ property field automatically. Sometimes however, I’ll be creating a note about a meeting that happened a while back, as I regularly still have to take manual notes because my device isn’t at hand on site. In this case, I just manually overwrite the autofilled date. Within the meeting_MOC note, I also created an overview of all meetings, which I want to be able to query within a certain date range (let’s say Jan. 2025).

The only way I managed to autofill date and time upon note creation using a button is by using the Templater plugin, using <% tp.date.now(“yyyy-MM-DD HH:mm”) %> (see below for MWE). This breaks my date query: either the query returns all notes with autofilled dates, or all notes with manually set dates. This is because the two formats are different, and I’m unable to reconsile both. Any help?

The solution should allow automatic dating (1) in a property field (2) which is manually overwritable (3) and can be queried in a dataview table (4). This poses 4 design requirements which I haven’t been able to reconcile. The methods for automatic dating and querying don’t matter.

Things I have tried

MWE:

  1. create a note ‘DummyMeetingTemplate’ in the default template folder, which only contains the following (in source mode):

Date: <% tp.date.now(“yyyy-MM-DD HH:mm”) %>

  1. Create a folder called DummyMeetingFolder
  2. Create a note ‘DummyMeetingMOC’, which contains 3 elements:
    i. A table which will hold one attempt to query the date, which will turn out to correspond to querying the default ‘Date & time’ dates:
TABLE
	Date
from "DummyMeetingFolder"
where Date >= date(2025-01-01) and Date <= date(2025-01-31)

ii. A table which will hold another attempt, which will turn out to correspond to querying tp-dates (not date type, but string type with very rigid formatting!):

TABLE
	dateformat(date(Date, "yyyy-MM-dd HH:mm"), "dd/MM/yyyy") as "Date"
from "DummyMeetingFolder"
where date(Date, "yyyy-MM-dd HH:mm") >= date(2025-01-01) and date(Date, "yyyy-MM-dd HH:mm") <= date(2025-01-31)

iii. A button to create a note from the template, using the Meta Bind plugin’s Button Builder (Install plugin > Ctrl+P > Meta Bind Button Builder > AddAction: TemplaterCreateNote > Template File: DummyMeetingMOC and Folder: DummyMeetingFolder > Copy to Clipboard > paste in DummyMeetingMOC)
4. From within the DummyMeetingMOC, click the button twice. Notice how two files are created in the DummyMeetingFolder. Both should have the dates autofilled. In the DummyMeetingMOC, both notes should appear in one of the tables (not both). Now manually change the date of one of both to any other date within the same month. This file jumps tables. Problem.

Note:

  • The MWE only works if you’re executing the steps in Jan. 2025. Adapt the dataview table queries according to your own month.
  • Using ctime or whatever does not work, as I want to be able to manually set the meeting date
  • I’ve tried a lot of combinations of time formatting, and none worked. I haven’t been systematic, but I doubt time formatting is the answer here.
  • I’ve tried several configurations. The MWE displays the one which highlights the problem best. The end result should clearly highlight the difference in date formatting, which is even a data typing problem (see step 3ii.).
3

Edit, because code blocks didn’t turn out as I wanted.

Step 1’s code should be:

---
Date: <% tp.date.now(“yyyy-MM-DD HH:mm”) %>
---

Step 3.i should be:

```dataview
TABLE
Date
from “DummyMeetingFolder”
where Date >= date(2025-01-01) and Date <= date(2025-01-31)
```

Step 3.ii should be:

```dataview
TABLE
dateformat(date(Date, “yyyy-MM-dd HH:mm”), “dd/MM/yyyy”) as “Date”
from “DummyMeetingFolder”
where date(Date, “yyyy-MM-dd HH:mm”) >= date(2025-01-01) and date(Date, "yyyy-MM-dd HH
```

4

Dataview is a little picky and requires a full ISO 8601 date to include the T between the date and time part. That format should also Templater and core Obsidian like.

If you insist on using it with the space, you need to do something like dateformat(Date, "yyyy-MM-dd hh:mm") to make dataview convert the date text back into a date.

Another note is that you might need to do striptime() around a date&time variable to make it actually equal to just the date.

5

Thanks for the reply. I have a few questions about some of your input:

Dataview is a little pickup and requires a full ISO 8601 date to include the T between the date and time part.

What exactly does this imply for my case? I have indeed noticed that the {{date}}T{{time}} format is often used (with the emphasis on the T between date and time), but that did not change my outcome. Have you made this work in some way? I’m curious to hear more about this.

That format should also Templater and core Obsidian like.

Could you clarify what this means?

If you insist on using it with the space, you need to do something like dateformat(Date, "yyyy-MM-dd hh:mm") to make dataview convert the date text back into a date.

Correct, and that’s indeed what I’ve done with step 3.ii in the MWE I laid out. The point of my post is that this does leads to an error for the manually set date and time*, leading to a part of relevant queries being left out of the dataview table. Additionally, I do not insist on any time and date format. The 4 functional requirements I explicitly listed are the only ones I care about. Am I missing something else from your suggestions?

Another note is that you might need to do striptime() around a date&time variable to make it actually equal to just the date.

I tried this as well, but didn’t find it to change anything. This is turning out te be a real conundrum.

To rephrase my problem in a more practical way: date(Date) exists for the manually set date, but does not return a correct date format for the automatically set time (of string type). Any way to correct this (e.g. using dateformat()) fixed the latter but breaks the former. I’m looking for a way to reconcile automatic AND manual dates in the same query, using whatever means necessary. I have no strong feelings about the input formats (as long as it fits in a property field), nor about the way in which the automatic setting is done (as long as it is triggered upon note creation).