What I’m trying to do
I have a set of notes logging activities with YAML data detailing begin and end times, and I would like to be able to summarize the notes by summing the durations of each activity grouped by other fields in the notes, specifically by the name and the activity itself.
The data is shown by the following query:
TABLE WITHOUT ID
substring(split(file.name,"–")[1],0,15) AS Name
,begin
,end
,activity
,date(end,"yy-MM-dd-HHmm")-date(begin,"yy-MM-dd-HHmm") as Duration
FROM "Logs" AND #type/log/activity
WHERE startswith(begin,"25-02-02")
which will produce the following table:
Name11 begin end activity Duration
Poly Coating 25-02-02-1451 25-02-02-1507
Woodworking(Act)
16 minutes
Dishes 25-02-02-1606 25-02-02-1630
Chores(Act)
24 minutes
Poly coating 25-02-02-1828 25-02-02-1839
Woodworking(Act)
11 minutes
Sunday Supper 25-02-02-1220 25-02-02-1430
Appointment(Act)
2 hours, 10 minutes
Reading Feeds 25-02-02-0645 25-02-02-0706
Reading(Act)
Media(Act)
21 minutes
Archiving movie 25-02-02-0715 25-02-02-0719
Media(Act)
4 minutes
Reading Feeds 25-02-02-1118 25-02-02-1200
Reading(Act)
Media(Act)
42 minutes
Reading Feeds 25-02-02-1508 25-02-02-1601
Reading(Act)
Media(Act)
53 minutes
YouTube 25-02-02-1648 25-02-02-1730
Media(Act)
42 minutes
Reading Feeds 25-02-02-1812 25-02-02-1826
Reading(Act)
Media(Act)
14 minutes
YouTube 25-02-02-1847 25-02-02-1937
Media(Act)
50 minutes
Things I have tried
I have tried to use FLATTEN activity, producing the following somewhat better formatted table:
Name15 | begin | end | activity | Duration |
---|---|---|---|---|
Poly Coating | 25-02-02-1451 | 25-02-02-1507 | Woodworking(Act) | 16 minutes |
Dishes | 25-02-02-1606 | 25-02-02-1630 | Chores(Act) | 24 minutes |
Poly coating | 25-02-02-1828 | 25-02-02-1839 | Woodworking(Act) | 11 minutes |
Sunday Supper | 25-02-02-1220 | 25-02-02-1430 | Appointment(Act) | 2 hours, 10 minutes |
Reading Feeds | 25-02-02-0645 | 25-02-02-0706 | Reading(Act) | 21 minutes |
Reading Feeds | 25-02-02-0645 | 25-02-02-0706 | Media(Act) | 21 minutes |
Archiving movie | 25-02-02-0715 | 25-02-02-0719 | Media(Act) | 4 minutes |
Reading Feeds | 25-02-02-1118 | 25-02-02-1200 | Reading(Act) | 42 minutes |
Reading Feeds | 25-02-02-1118 | 25-02-02-1200 | Media(Act) | 42 minutes |
Reading Feeds | 25-02-02-1508 | 25-02-02-1601 | Reading(Act) | 53 minutes |
Reading Feeds | 25-02-02-1508 | 25-02-02-1601 | Media(Act) | 53 minutes |
YouTube | 25-02-02-1648 | 25-02-02-1730 | Media(Act) | 42 minutes |
Reading Feeds | 25-02-02-1812 | 25-02-02-1826 | Reading(Act) | 14 minutes |
Reading Feeds | 25-02-02-1812 | 25-02-02-1826 | Media(Act) | 14 minutes |
YouTube | 25-02-02-1847 | 25-02-02-1937 | Media(Act) | 50 minutes |
which has successfully produced a table with one row per activity, but each way I try to GROUP BY either Name or activity I get nulls in all the other columns. And I cannot see how to sum the Duration column at all.
I’m obviously very confused and I have a feeling I’m missing something very basic. Please help.