Help with Dataview Query to Compare Timestamps

[armansultanov]

What I’m trying to do

I am trying to create a Dataview query in Obsidian that compares timestamps of notes from the same month and day but from previous years relative to the current file’s timestamp. The timestamp is stored in the front matter of each note in the format YYYYMMDDHHMM.

Things I have tried

TABLE timestamp AS "Timestamp", 
       substring(timestamp, 0, 4) AS "Year", 
       substring(timestamp, 4, 6) AS "Month", 
       substring(timestamp, 6, 8) AS "Day"
FROM "Zettelkasten"
WHERE substring(timestamp, 4, 6) = substring(this.file.timestamp, 4, 6) 
AND substring(timestamp, 6, 8) = substring(this.file.timestamp, 6, 8) 
AND substring(timestamp, 0, 4) < substring(this.file.timestamp, 0, 4)

[holroy]

Dataview is very picky that the dateformat needs to be in a given format. If not in a pure ISO8601 format (with the T inbetween the date and day part), it’s not recognised as a date. Luckily you can use date(text, format) to convert a number/texts back to a proper date, as illustrated in the code below:

---
timestamp: 202407291227
---

`= date(string(this.timestamp), "yyyyMMddhhmm")`

Alternatively in a query context you could do something like:

```dataview
TABLE aDate, aDate.year, aDate.month, aDate.day
WHERE file = this.file
FLATTEN date(string(timestamp), "yyyyMMddhhmm") as aDate
```

[armansultanov]

Thank you, holroy!

Could you please modify my code with your corrections, becouse I have no idea how to implement them myself. I tried, hornestly. You will do me the greates favor ir you do so

[holroy]

Something like the following could possibly work for comparing the date of the current file versus similar files which only differs in the day part of the date:

```dataview
TABLE aDate
FROM "Zettelkasten"
FLATTEN date(string(timestamp), "yyyyMMddhhmm") as aDate
FLATTEN date(string(this.timestamp), "yyyyMMddhhmm") as thisDate
WHERE aDate.year = thisDate.year
  AND aDate.month = thisDate.month
  AND aDate.day < thisDate.day
```

[armansultanov]

True hero…
This works 100%
Thank you for your time!
My best!