Converting ISO8601 date and time to day of the week with dataview

What I’m trying to do

I have a bunch of files with the tag #music that all have a key called ‘added’ and a value formatted like 2024-04-20T16:33:04Z (ISO8601)

What I want to do is create a dataview table that lists all days of the week and the amount of files whose day of the week value corresponds to that of the day in the table.

so it’d look like this:

| days | amount  |
| mon  |  6           |
| tue    | 12          |
| thur   | 8            |

where there are 6 files with the tag #music where the dates in ‘added’ correspond to monday, 12 for tuesday etc.

I’ve read up on the dataview documentation and found some stuff that I thought would be useable, but whenever I actually tried to implement it in my table I wouldn’t get any results.

Things I have tried:

using dateformat to convert the ISO8601 date to day of the week with luxontoken EEEE which should convert the time to the full day of the week according to this table. I couldn’t get this to work, as the table just returned nothing (no error either, so I probably made a logic error).

I was assuming I’d have to convert the ISO8601 date to day of the week, then flatten that and have it as the amount. I’d think the query would look something like this?

TABLE day, amount
FROM #music
\\code for converting date
FLATTEN day as amount 
SORT amount desc

But I’m quite new to dataview so this might be completely wrong :face_with_spiral_eyes:

Do you want to count up all mondays with a given tag, or just a given weeks tag for that particular monday? (And likewise for the rest of the week)

All mondays with a given tag. So if I do FROM #tag then the query should return how many files containing #tag have a value in ‘added’ that is a monday, how many have tuesday etc.

So something like

TABLE amount
FROM #music
FLATTEN dateformat(added, "EEEE") as weekday
GROUP BY weekday
FLATTEN length(rows) as amount
SORT amount desc

could possibly do the job. Untested, but hopefully it’s good. :slight_smile:

Updated: Corrected where to get the date from…

Thank you so much!

For future reference if anyone needs it, I had to change to the yaml property name. should work fine if the date is in the file name though (I think).

Sorry, my bad. Didn’t think, just copied an earlier approach. Will update my response.

This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.