Query for total time spent studying across all the subjects on a particular day

Help
1

What I want to do

Each subject is a field in my daily note that denotes how much time I spent studying it on a given day.

What I want to query for is the total time I spent studying across all my subjects on a particular day

image
image864×623 35.4 KB 

TABLE Computer_Science, English, Business_Studies
FROM "Permanent/Diary"

Above is the query and it’s output.

It’s to be noted that on 6th I studied English for 25 min and on 9th I studied Business_Studies for 3hours

What I have tried

TABLE English + Business_Studies + Computer_Science 
FROM "Permanent/Diary"
Group by date

TABLE sum(English + Business_Studies + Computer_Science) 
FROM "Permanent/Diary"
Group by date

TABLE sum(row.English + Business_Studies + Computer_Science) 
FROM "Permanent/Diary"
Group by date

Non of them have worked so far. All it returns is a dash in the column next to the date.

But interestingly

TABLE English + Business_Studies
FROM "Permanent/Diary"

Returns

image
image764×582 24.2 KB

Even tho I studied Business_Studies on 9th the whole file doesn’t show up, same on 6th on which I studied English

2

NB! Please try to refrain from asking the same question two places at the same time. It’s confusing, and kind of messy.

Why the mysterious results

You got an error in the markup for Computer_science on 2023-01-05, with some “Mmin” stuff, which cause some issues.

In addition there is something funky going on when dataview adds a duration to nothing, which sometimes even causes files to disappear, as you’ve discovered. I suspect those missing lines/files in the result to actually be bugs/features of Dataview.

Regarding missing lines/files in the results:

  • No value added to no value, returns the file and no value
  • No value added to a duration, hides the file completely
  • Duration added to a duration, returns the file and summed duration

How to fix

One of the issues is most likely that you’re trying to add a value to nothing (for subjects you didn’t study that day). In these cases the value is undefined, and we need to tackle that somehow. My first idea was to use default(Computer_Science, 0) as com, which with nothing in Computer_Science would return 0.

However, that failed because it was trying to add a duration and a number, so after some tinkering, I found that I needed to do default(Computer_Science, dur("0 m")) as com to get a duration of zero minutes which I could use in the summation.

Some do some calculation/alteration like this in the header, but I prefer to use FLATTEN to do this, so given the previous information the query now looks like:

```dataview
TABLE
  com as "Computer Science", 
  eng as English,
  biz as Business_Studies, 
  total as "Study time"
FROM "Permanent/Diary" 
FLATTEN default(Computer_Science, dur("0 m")) as com
FLATTEN default(English, dur("0 m")) as eng
FLATTEN default(Business_Studies, dur("0 m")) as biz
FLATTEN (com + eng + biz) as total
SORT file.name
```

Which resulted in this output:

image
image962×224 27.7 KB

1 Like
3

I shall keep that in mind from next time. My apologies.

The query did work, but when I added another field, namely “Economics”, it broke. But I was able to fix it. Just getting used to the new data command flatten.

The resource linked below was really useful since the dataview docs on github don’t have many examples and the language can get too technical.

Linking it here in case a novice like me comes across this post.

Thanks for your help holroy, by the way, which theme are you using?

4

It’s the dark Minimal theme, with “macos” and “True black” options.

5

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