Iterate over nested Elements/Sum up Day Planner clock fields by task

Help
1

What I’m trying to do

Hi community,

I use de Day Planner Plugin for task management and for Time Tracking those tasks.
This generates something like this:

Day planner

  • #task 10:00 - 11:00 Once [scheduled:: 2025-02-18]
    [clock::2025-02-17T09:00:30–2025-02-18T10:30:30]
  • #task 12:45 - 13:45 Twice [start:: 2025-02-11] [scheduled:: 2025-02-11]
    [clock::2025-01-03T01:00:00–2025-01-03T02:00:00]
    [clock::2025-01-03T03:00:00–2025-01-03T03:30:15]
  • #task 14:00 - 15:00 Not yet [scheduled::2025-02-11]

I try to sum up all occurrences I worked on a specific task, hoping for a table like this:

Task Duration

once 1h 30 min
twice 1h 30 min 15s

Things I have tried

TABLE WITHOUT ID
 Task.text AS Task,
 sum(map(Task.clock, (c) => ((date(split(c, "--")[1]) - date(split(c, "--")[0]))))) as Duration
FROM #task
  FLATTEN file.tasks AS Task
  FLATTEN task.clock AS Cloc
WHERE file=this.file 
 AND Task.clock
 GROUP By Task
2

This Query unfortunately just collects tasks with more than one clock field:

image
image1011×140 3.94 KB

Thanks and kind regards
Zolto

3

Tried serveral other ways but it seams that, whatever I do: a task with a single clock field will never show up. Do I need to somehow ‘cast’ it to an array or something the like?

4

OK, I finally figured it out although I don’t really understand why the above didn’t work.

TABLE WITHOUT ID
 Task.text AS Task,
 sum(rows.Hours) AS Sum
 
FROM #task
 FLATTEN file.tasks AS Task
 FLATTEN Task.clock AS Clock
 FLATTEN (date(split(Clock, "--")[1]) - date(split(Clock, "--")[0])) AS Hours

WHERE  file=this.file AND Task.clock
 GROUP BY Task

image
image1481×81 6.17 KB

(Changed the Times a little so I is easier destinguishable )