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

Transform date meta data for calculations¶

Basic¶

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

Variants¶

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