Skip to content

Transform textual date informations in your meta data to proper dataview dates to do calculations with them

Transform date meta data for calculations


ISO format

In order to be recognizable by dataviews date function, that again enables us to calculate with dates, the date needs to be in ISO format, in most cases something like 2022-10-08T19:03:22. If your date is stored in another format, you need to transform it to fit ISO.

halfISO:: 2022-09-20 10:30

TABLE halfISO, date(replace(halfISO, " ", "T"))
WHERE file = this.file


Calculate with dates after transformation

halfISO-start:: 2022-09-20 10:30
halfISO-end:: 2022-10-01 15:00

TABLE start, end, end - start AS duration
WHERE file = this.file
FLATTEN date(replace(halfISO-start, " ", "T")) AS start
FLATTEN date(replace(halfISO-end, " ", "T")) AS end

Using other formats

Keep it as ISO as possible

If your date format diverges a lot from the ISO format, transforming it becomes cumbersome and errorprone. For example, the following examples do not work if you have no time or do not use two-digits in the date. To keep yourself from trouble, whenever possible try to keep the ISO format as much as possible.

germanformat:: 22.09.2022 11:15

TABLE date(regexreplace(germanformat, "([0-9]+).([0-9]+).([0-9]+) (.+)", "$3-$2-$1T$4")) AS "date"
WHERE germanformat

americanformat:: 09/25/2022 09:09 AM

TABLE date(regexreplace(americanformat, "([0-9]+)/([0-9]+)/([0-9]+) ([0-9:]+)(.+)", "$3-$1-$2T$4")) AS "date"
WHERE germanformat