Date Formatting and Difference of Dates in Dataview Query

[meet]

What I’m trying to do

I am trying to generate a dataview table with all the notes that fit a criteria which is dependent on date-time property of the note. For example, say if I want the list of notes from a folder that were created more than 30 days ago.

Things I have tried

I was trying to understand how dates are used in the dataview query. For that, I tried with the following query just to see whether it calculates the date difference correctly or not.

table round((date(today) - date(date-created)).days) as age
from "test folder"
limit 10

Results

The dataview query shows the calculated “age” column as “-” in each and every note, except one note.

Further Investigation

On checking that particular note, I came to know I had borrowed that note from some other template vault.
And in that particular vault, the date format was “yyyy-MM-ddTHH:mm”
While my vault date format in dataview settings is “yyyy-MM-dd HH:mm”

So, that means the date(today) is generating the date in the format with that extra “T” in between and it is not following my settings set date format.

Question / Help

I do not want to set the default datetime format to include that unnecessary T in the middle. It spoils the readability in source mode. Further, I don’t know if that will work if I ever export the .md note to some other program.

Q’s:

1. How do I generate date(today) in the same format as my vault’s dataview default settings?
2. Is there any better way to get notes that were generated 30 days ago?
3. Also, are there any roundup and rounddown functions here?

[Guapa]

Dataview understands dates and dates with times if they use ISO8601 format, which requires the ‘T’ in most time-related formats, including the date-and-time format used by Obsidian and Dataview. So your preferred personal setting is seen as a string of numbers, not as a date / date and time.

Data Types - Dataview

Text that matches the ISO8601 notation will be automatically transformed into a date object. ISO8601 follows the format YYYY-MM[-DDTHH:mm:ss.nnn+ZZ]. Everything after the month is optional.

Example:: 2021-04 
Example:: 2021-04-18
Example:: 2021-04-18T04:19:35.000
Example:: 2021-04-18T04:19:35.000+06:30

ISO 8601

Below is a thread about turning a string into a date. After originally starting with the same settings as you, I now only use ISO8601. Using ISO8601 with the ‘T’ is the way to go, in my experience. And being an international standard it is more likely to work when exported than a string of numbers.

Dataview from String to Date

Things I have tried What I’m trying to do I use the following yaml header. creation_date: <% tp.file.creation_date() %> to store the creation date independend of the OS informations. Noww, i want to use this variable as date in any dataview queries. The problem typeof(creation_date) = String, which means i can not use if with other date operators like =,<, etc. Is there any way i can convert a string (with a specified format) back to a date? Thx

[meet]

So, this dataview setting about default date+time format has no particular use except to show the datetime object, is it?

Shouldn’t this default setting be implemented such that dataview can continue to follow the ISO format in the backend, but for all purposes user interaction should be based on this default format… I interpreted this setting like this.

[dawni]

It doesn’t implement it that way. But there’s a quick solution for what you’re doing. Within the query, tell it what your date format is. In your case, use date(date-created, "yyyy-MM-dd HH:mm"). That is:

```dataview
table round( (date(today) - date(date-created, "yyyy-MM-dd HH:mm")).days ) as age
from "test folder"
limit 10
```