Dateview combining two non-empty results via OR returns empty result?

Help
1

What I’m trying to do

Hi everyone,
I am stuck on a weird issue with a dataview query that I’m hoping someone can help me with. I have read the documentation on how dataview is different from SQL but I still don’t understand what could be wrong here.

I have a Dataview query aimed at retrieving notes with a specific date across two different Notetypes (“Readwise” and “Literature”). The dates in those notes have different formats (which is a whole other issue =). Anyway, individually, the sub-queries work as expected and return a non-empty result, but when combined with the OR operator, the query returns empty results. This is unexpected to me at least.

Things I have tried

This is the combined query.


LIST title
WHERE (
    (contains(Notetype, "Readwise") AND dateformat(date-created, "yyyy-MM-dd")="2024-04-22") OR 
    (contains(Notetype, "Literature") AND dateformat(date(replace(date-created, ",", "-"), "yyyy-M-d"), "yyyy-MM-dd")="2024-04-22")
)

I have tried various different ways of grouping the conditions, always with the same result, so I am assuming now that I am fundamentally misunderstanding something here?

  • The query aims to find notes created on “2024-04-22” in either the “Readwise” or “Literature” Notetypes.
  • Both individual sub-queries within the WHERE clause produce non-empty results.
  • I’ve double-checked the data to ensure that notes with the specified date exist in both Notetypes.

Any insights or suggestions on how to troubleshoot this issue would be greatly appreciated.
thanks, pepepe

2

I would tend to bracket anything I give to an OR to avoid ambiguity

((contains(Notetype, "Readwise") AND dateformat(date-created, "yyyy-MM-dd")="2024-04-22")) OR 
    ((contains(Notetype, "Literature") AND dateformat(date(replace(date-created, ",", "-"), "yyyy-M-d"), "yyyy-MM-dd")="2024-04-22"))

Does that work?
Otherwise I would test each term individually making sure it made sense and building up the logic until it breaks so you can pinpoint what isn’t working

EDIT: sorry I misread the initial query, I see you’ve already done that! A headscratcher then.

The next thing that occurs to me is that maybe “date-created” is being interpreted as “date” minus “created”. Would using ctime work instead?

3

thanks! the additional brackets sadly didn’t do anything. I think I have tried nearly all possible valid ways to bracket the statements.

So here is a step by step how it breaks. Both sides of the OR individually give results, for example for the second part the query:

LIST title
WHERE ((contains(Notetype, "Literature") AND dateformat(date(replace(date-created, ",", "-"), "yyyy-M-d"), "yyyy-MM-dd")="2024-04-22"))

generates all expected results, i.e. all “Literature” notes created on April 22nd. If I remove the date part from this part in the overall query it still works:

LIST title
WHERE ((contains(Notetype, "Readwise") AND dateformat(date-created, "yyyy-MM-dd")="2024-04-22")) OR 
   ((contains(Notetype, "Literature")))

Now I am getting all Readwise notes from April 22nd and all Literature notes.
However, if I remove the “Literaure” part, and just leave the date, like so:

LIST title
WHERE ((contains(Notetype, "Readwise") AND dateformat(date-created, "yyyy-MM-dd")="2024-04-22")) OR 
    ((dateformat(date(replace(date-created, ",", "-"), "yyyy-M-d"), "yyyy-MM-dd")="2024-04-22"))

I get the empty set again.

I might have found a clue but not sure what it means. If I leave out the date part in the first clause like so:

LIST title
WHERE ((contains(Notetype, "Readwise"))) OR 
    ((contains(Notetype, "Literature") AND dateformat(date(replace(date-created, ",", "-"), "yyyy-M-d"), "yyyy-MM-dd")="2024-04-22"))

I get the the correct literature notes but it only returns the Readwise notes that do not have a “date-created” property. This seems significant, but how? =)

4

my apologies, I misread the initial query and the extra brackets wouldn’t help at all.

I do think the date-created may be the issue. I would be tempted to test changing “date-created” to “date_created” in a note or two and in your query to see if that makes a difference to what gets picked up.
A simple query could be WHERE date-created to check that the property is working as intended.
It might also be worth changing the LIST to TABLE while debugging so you can expose properties like date-created to check.

5

I have used table and created a new property called “tmp” assuming you meant the dash to potentially cause problems. What I have found is that treating “tmp” differently in both sub-conditions seems to cause the problem. So having

dateformat(date-created, “yyyy-MM-dd”)=“2024-04-22”)

in the first one and

dateformat(date(replace(date-created, “,”, “-”), “yyyy-M-d”), “yyyy-MM-dd”)=“2024-04-22”)

in the second one. If I use the first version in both sub-conditions it correctly returns the results of first and, correctly, nothing from the second. Unfortunately I need to do this awkward replace conversion because I get the date in my zotero import (via Citations plugin), unhelpfully, in this format “2024,4,22”, so the date-created property differs across zotero and readwise notes.
I still do not understand why those sub-conditions would interact with each other but I guess it might be easier to try and fix how the date is imported from zotero in the first place. Unless this puzzle piece helps you solve it? =)

6

could you post an example of the yaml header for a note of each type that should satisfy the query that i could test with?

it does feel like the problem has something to do with interpretation of dates, as it often tends to be!

7

Sure, thanks again for your time. This should work as a minimal example:

Note 1 yaml

Notetype: Literature
tmp: 2024,4,22

Note 2 yaml

Notetype: Readwise
tmp: 2024-03-25, 13:45

Query that I think should work but doesn not.

LIST title
WHERE ((contains(Notetype, "Readwise") AND dateformat(tmp, "yyyy-MM-dd")="2024-04-22") OR (contains(Notetype, "Literature") AND dateformat(date(replace(tmp, ",", "-"), "yyyy-M-d"), "yyyy-MM-dd")="2024-04-22"))
8

I also thought it might have to do with the date interpretation because it usually does. That was my reasoning for converting them to strings I have more control over first.

9

thanks, I’ll have a tinker with this later today. One thing that may be causing trouble is whether the month is 4 or 04.

I tend to prefer to let a function turn a date string into an internal date, since then I’m relying on someone much smarter than me to make sure the dates match haha

this is also why I religiously use ISO date wherever possible…

10

thanks!
Yes totally get where you are coming from and ideally the dates would all be the same format to begin with. That being said, I do think that the conversion to a well defined string actually is not the issue here.
You can have a look at the result of this conversion with this query on the test notes:

TABLE title, tmp, dateformat(date(replace(tmp, ",", "-"), "yyyy-M-d"), "yyyy-MM-dd") as t
WHERE (contains(Notetype, "Literature") AND dateformat(date(replace(tmp, ",", "-"), "yyyy-M-d"), "yyyy-MM-dd")="2024-04-22")
11

OK well one part it’s failing on is the readwise datestring: 2024-03-25, 13:45
If you are going to combine date and time in a string, you want the following format: 2024-03-25T13:45.

Do you have control over the date formatting of your readwise stuff?
You might want to use a different conversion operation depending on note type before feeding it to a date function, because in isolation, =date("2024-03-25, 13:45") does not work.

the choice function will likely come in handy:
https://blacksmithgu.github.io/obsidian-dataview/reference/functions/#choicebool-left-right

12

I think I figured something out which doesn’t help me solve my problem but at least helps me understand what is going on. Firstly, apologies, yes the condition I gave you did not work for that date string and I am really sorry for that. In the yaml example I gave you also the readwise note date should have been the same as in the other, so 2024-04-22, 13:45

This condition works for the readwise string that includes the time in the weird comma format.

TABLE title
WHERE ((contains(Notetype, "Readwise")) AND dateformat(date(tmp, "yyyy-MM-dd, HH:mm"), "yyyy-MM-dd")="2024-04-22")

Anyway, so what I am pretty sure is happening is that dataview first calls the functions across the entire query in some way I do not expect, before is actually executes the query. The reason I am quite sure this is happening is because the above query works but simply adding this other conversion via OR then returns empty again:

TABLE title
WHERE ((contains(Notetype, "Readwise")) AND dateformat(date(tmp, "yyyy-MM-dd, HH:mm"), "yyyy-MM-dd")="2024-04-22") OR dateformat(date(replace(tmp, ",", "-"), "yyyy-M-d"), "yyyy-MM-dd")

However, if I rename the property of the the readwise node to tmp2 and change the query accordingly:

TABLE title
WHERE ((contains(Notetype, "Readwise")) AND dateformat(date(tmp2, "yyyy-MM-dd, HH:mm"), "yyyy-MM-dd")="2024-04-22") OR dateformat(date(replace(tmp, ",", "-"), "yyyy-M-d"), "yyyy-MM-dd")

It returns the correct result again. That is why I think something happens before the actual query with these function calls that behaves differently from what I would expect, probably somehow overwriting the first conversion with the result of the second or whatever.

Not sure yet how you meant to use the choice function, will think about this a little =)
thanks, pepepe

13

here’s working code:

TABLE Notetype as "note type", tmp AS "old date", choice(Notetype="Literature",date(join([split(tmp,",")[0],padleft(split(tmp,",")[1],2,"0"),padleft(split(tmp,",")[2],2,"0")],"-")),split(tmp,",")[0]) AS "iso date"
FROM !"template" AND #foo
WHERE (contains(Notetype, "Literature") AND date(join([split(tmp,",")[0],padleft(split(tmp,",")[1],2,"0"),padleft(split(tmp,",")[2],2,"0")],"-")) = date("2024-04-22")) OR (Notetype="Readwise" AND date(split(tmp,",")[0]) = date("2024-03-25"))

I changed the dates it tests for so that both notes get picked to show it’s working.
I did a bunch of ugly string operations to make those dates work.

Ideally you’d get the tools that are importing these date formats to at least output something valid to the dataview date() function, and I’d even go as far as cleaning the data explicitly by hand, but I’m admittedly fussy about date format.

In the meantime, this code tears apart the dates and puts them back together in an unambiguous way, then compares them as date objects to make doubly sure.

the FROM line was just so I could easily point at the files I was testing with – I tagged them both with #foo

14

Wow, thanks. I can’t say I am sure why your version works while mine doesn’t but it does work!!
Thanks you so much.
As a long term plan I will definitely try to change the imported dates but I could not make this work with the citations plugin (the readwise plugin allows for more customization) and the linter plugin always creates problems with git.
But for now I will happily use your solution!
again, thanks, pepepe

1 Like
15

you’re welcome!

I think what was happening was the single digit months made it so that date() didn’t know what was a month and what was a day. I used padleft to add a zero to the month if it was <10. With the other format it was mostly correct, so I just divided at that comma and kept the date.

If you know how to use split, join, padleft, and choice in DQL, you can manipulate most incoming kinds of date string. Here I used choice to treat the string differently depending on if we were dealing with Literature or Readwise.

Whenever I’m working with DQL I’ll have a tab of this open in case something in there can help me:
https://blacksmithgu.github.io/obsidian-dataview/reference/functions/

1 Like
16

I think a more precise description of what’s happening is that when you do the date(text, format) conversion where text is an actual date, as for the Literature notes, this returns null, and is thusly not included in the end result.

Sadly, when using choice() it always does the calculation, so this behaviour of excluding the null entry is still seen. Various other incantations using choice() where either expression throws errors might also eliminate some results from the end table.

I’m not sure what’s the best way to work around this issue, but one way is to make sure neither functions throws errors. This is also why the suggested code actually works, since it’s causing a proper conversion of a string manipulated into a legal date text. This could also have been done using regexreplace(), but I’m not sure whether that solution is any nicer.

Alternate solution using regexreplace():

FLATTEN date(regexreplace(regexreplace(
    file.frontmatter.date-created,
    "(\d{4}),(\d+),(\d+)(,.*)?", 
    "$1-0$2-0$3"), 
  "-.(\d\d)",
  "-$1")) as created

Using this beast you’ll always look at the date as a string, and we use regexreplace() to first move it into the format 2024-012-24 for December 24th, and then the second replace removes any three digits month or days. Now created is a proper date, and you can compare or format it to your liking, and use in WHERE-clauses.

17

once it gets down to string hacking it feels more proper to take care of things with regex. I need to practice it more – one week away and it all falls out of my head. Thanks for the alternate version!

1 Like