How to use YAML fields "created" und "changed" instead of ctime and mtime for base queries?

[merlinuwe]

I have a base query for notes that are created or edited today or yesterday. I sync between different file systems (Android, Linux, Windows, …) and so I trust the creation (erstellt) and modified (geändert) in YAML more than the date of the file systems.

This is my typical YAML front matter for notes (we have DSP in Germany; “Sommerzeit” and “Normalzeit”):

---
tags: [Notiz]
erstellt: 2025-08-19T10:42:31+02:00
geändert: 2025-08-19T20:08:47+02:00
---

This is my base, which works, but by using ctime and mtime:

```base
formulas:
  Untitled: ""
properties:
  file.name:
    displayName: Dateiname
  file.folder:
    displayName: Ordner
  file.size:
    displayName: Größe
  file.tags:
    displayName: Tags
views:
  - type: table
    name: Heute oder gestern geändert oder erstellt
    filters:
      or:
        - file.ctime == today()  # heute
        - file.ctime == today() - ("1 day")  # gestern
        - file.mtime == today()  # heute
        - file.mtime == today() - ("1 day")  # gestern
    order:
      - file.name
      - file.ext
      - file.tags
      - file.size
      - erstellt
      - geändert
      - file.ctime
      - file.mtime
    sort:
      - property: geändert
        direction: DESC
      - property: erstellt
        direction: DESC
    columnSize:
      file.tags: 106
      file.size: 81
      note.erstellt: 200
      note.geändert: 280
      file.ctime: 149
      file.mtime: 146
  - type: table
    name: View
    filters:
      or:
        - erstellt == today()
        - geändert == today()
    order:
      - file.name
      - geändert
      - erstellt
      - formula.Untitled
    columnSize:
      note.geändert: 194
      note.erstellt: 194

```

How can I use the YAML fields “erstellt” und “geändert” instead of ctime and mtime for this query?

Is a ctime/mtime fallback possible for files without markdown YAML (images, pdf, …)?

The first lime frame shows, why I cannot trust the file systems dates. The second shows, why I need a fallback.

[dawni]

How about this:

```base
formulas:
  erstellt: if(erstellt,erstellt,file.ctime)
  geändert: if(geändert,geändert,file.mtime)
views:
  - type: table
    name: Heute oder gestern geändert oder erstellt
    filters:
      or:
        - date(erstellt.toString().slice(0,19)) == today()
        - date(geändert.toString().slice(0,19)) == today()
    order:
      - file.name
      - formula.erstellt
      - formula.geändert

```

Explanation…

When I include a +02:00 Sommerzeit in a datetime property and try to use it as a date in a base formula, Bases gives an error message that says it’s a string, not a date. So you have some options:

• Edit all the erstellt and geändert values to remove daylight saving.

Or

• Use a base formula to turn them into dates for comparison. For example, slice off the last six characters then wrap it in date():

date(geändert.toString().slice(0,19)) == today()

Or

• Use a base formula to turn today’s date into a string for comparison. For example:

geändert.toString().startsWith(today().toString())

In both of those , I made sure to string-ify geändert too in case you have some notes with DST (already a string) and some without (seen as a date).

As for falling back to file system dates, you can use the if() function:

if(geändert,geändert,file.mtime)

[merlinuwe]

Magic. Thank you very much!