What I’m trying to do
I track time in Obsidian using blocks that look like this:
- [project:: AGF] (taskStart:: 2024-07-23T10:00:36+01:00) (taskFinish:: 2024-07-23T11:09:51+01:00)
- [project:: Writing] (taskStart:: 2024-07-23T15:47:58+01:00) (taskFinish:: 2024-07-23T16:05:55+01:00)
- [project:: AGF] (taskStart:: 2024-07-23T16:06:09+01:00) (taskFinish:: 2024-07-23T16:12:49+01:00)
I have a dataview query that calculates the duration spent on each timeblock
TABLE WITHOUT ID item.project AS Task, dateformat(item.taskStart, "HH:mm") AS Start, dateformat(item.taskFinish, "HH:mm") AS Finish, duration AS Duration
WHERE file = this.file
WHERE project
FLATTEN file.lists AS item
FLATTEN dur(item.taskFinish - item.taskStart) AS duration
This works perfectly and produces:
| Task | Start | Finish | Duration |
|---|---|---|---|
| AGF | 10:00 | 11:09 | 1 hour, 9 minutes, 15 seconds |
| Writing | 15:47 | 16:05 | 17 minutes, 57 seconds |
| AGF | 16:06 | 16:12 | 6 minutes, 40 seconds |
What I would like to do is produce a summary table that shows the total time per project.
| Task | Total |
|---|---|
| Writing | 1.23 hours |
| AGF | 1.56 hours |
Things I have tried
I searched for time tracking, calculated sum, duration sum and variations. The closest I got was from this.
TABLE WITHOUT ID
item.project AS Task,
round(sum(rows.duration), 2) AS Total
WHERE file = this.file
WHERE project
FLATTEN file.lists AS item
FLATTEN round((item.taskFinish - item.taskStart).hours, 2) AS duration
GROUP BY item.project
But while this does give a correct sum by project, this results in the project column showing no result:
| Task | Total |
|---|---|
| - | 1.26 |
| - | 1.28 |
Any help much appreciated! I am happy to use a dataview or dataviewjs query but the level of my skills means I can only adapt other js queries not write them from scratch.