Dataview: Sum of durations from Full Calendar frontmatter

[andrewinthemountains]

Hello.

I am using the following snippet to create a table with a totalTime column consisting of rows that are showing total duration values from frontmatter variables startTime & endTime.

startTime and endTime variables are auto generated when a new note / calendar entry is created in the Obsidian Full Calendar plugin.

TABLE startTime, endTime, totalTime 
FROM "Work/Client/Calendar_Client/2023_08"
FLATTEN dateformat(file.day, "yyyy-MM-dd") as dt 
FLATTEN dur(date(dateformat(file.day, "yyyy-MM-dd") 
 + "T" + endTime) 
 - date(dateformat(file.day, "yyyy-MM-dd") + "T" + startTime)) 
  as totalTime

I would like to sum the totalTime column.

How do I sum the totalTime column?

Thanks for any help!

[holroy]

I would switch to dataviewjs, and add another row with the calculated sum. This has been addressed in various posts recently, and below is one of my earlier answers:

How to get Column totals in dataview

My main approach to such an issue is to switch to dataviewjs, and do the extra work on the table within the javascript code there. So here is one attempt on doing this: ## Original query ```dataview TABLE WITHOUT ID file.link as Account, Amount, Payment_rate WHERE file.folder = this.file.folder WHERE number(Amount) ``` ## With summation ```dataviewjs const amounts = await dv.query(` TABLE WITHOUT ID file.link as Account, Amount, Payment_rate WHERE file.folder = this.file.folder WHERE n…

[andrewinthemountains]

Thanks for the reply @holroy.

I haven’t worked with dataviewjs so it will take some time for me to construct what I need.

This snippet from a dataview example vault pulls in one duration column which is referencing a ‘training’ variable.

You can see that the ‘training’ variable has a value that is already a manual entered total time: training:: 1h 27m.

My challenge is that I need to output a table that calculates a duration from ‘startTime’ and ‘endTime’ variables in each note in a target folder and secondly adds all of those values together for a Grand Total of hours and minutes worked for a client. It is currently outside of my ability to do so.

I’m sure that the solution that gets crafted and added to this post is going to be very helpful for anyone using Obsidian Full Calendar.

[holroy]

If you take the time to read that other post, you’ll see that you don’t need to do much. Change the query, and which column to sum and you should have your solution.

[andrewinthemountains]

Hi @holroy. I have read closely through the post you’d linked and have successfully summed my totalTime column. Thanks!

Here is your snippet from the link you shared above, altered for my purposes.

const amounts = await dv.query(`
TABLE startTime, endTime, totalTime 
FROM "Work/Client/Calendar_Client/2023_08"
FLATTEN dateformat(file.day, "yyyy-MM-dd") as dt 
FLATTEN dur(date(dateformat(file.day, "yyyy-MM-dd") 
 + "T" + endTime) 
 - date(dateformat(file.day, "yyyy-MM-dd") + "T" + startTime)) 
  as totalTime
`)

if ( amounts.successful ) {
  const sum = amounts.value.values
    .map(a => a[3])
    .reduce((tmp, curr) => tmp + curr, 0)

  amounts.value.values.push(["<strong>Grand Total:</strong>", sum])
  dv.table(amounts.value.headers, amounts.value.values) 
  dv.paragraph("Total amount: " + sum)
} else
  dv.paragraph("~~~~\n" + amounts.error + "\n~~~~~")

The last piece for me would be to format the Grand Total output as hours and minutes instead of milliseconds. In the instance shown below of 289800000 milliseconds, either ‘80.5’ hours or ‘80 hours, 30 minutes’ could work as final format.

I’m looking into how to achieve the format conversion now. But I’m rather sure that you know right away how to do this. If you wouldn’t mind sharing how to do so, I’d very much appreciate it.

[anon63144152]

In local tests, this gives the time as hours:

- date(dateformat(file.day, "yyyy-MM-dd") + "T" + startTime)).hours  

hours can be changed to minutes.

Expect there might be a better way.

---
startTime: 08:30
endTime: 17:30
---

### total hours
```dataviewjs
const amounts = await dv.query(`TABLE startTime, endTime, totalTime 
FLATTEN dateformat(file.day, "yyyy-MM-dd") as dt 
FLATTEN dur(date(dateformat(file.day, "yyyy-MM-dd")  + "T" + endTime) 
 - date(dateformat(file.day, "yyyy-MM-dd") + "T" + startTime)).hours  
  as totalTime`)
if ( amounts.successful ) {  const sum = amounts.value.values
    .map(a => a[3])
    .reduce((tmp, curr) => tmp + curr, 0)
  amounts.value.values.push(["<strong>==Total Hours==:</strong>", sum])
  dv.table(amounts.value.headers, amounts.value.values)} else
  dv.paragraph("~~~~\n" + amounts.error + "\n~~~~~")
```

### total minutes
```dataviewjs
const amounts = await dv.query(`TABLE startTime, endTime, totalTime 
FLATTEN dateformat(file.day, "yyyy-MM-dd") as dt 
FLATTEN dur(date(dateformat(file.day, "yyyy-MM-dd")  + "T" + endTime) 
 - date(dateformat(file.day, "yyyy-MM-dd") + "T" + startTime)).minutes 
  as totalTime`)
if ( amounts.successful ) {  const sum = amounts.value.values
    .map(a => a[3])
    .reduce((tmp, curr) => tmp + curr, 0)
  amounts.value.values.push(["<strong>==Total Minutes==:</strong>", sum])
  dv.table(amounts.value.headers, amounts.value.values)} else
  dv.paragraph("~~~~\n" + amounts.error + "\n~~~~~")
```

[andrewinthemountains]

Total hours works great for my purposes.
Thanks for the help @anon63144152 & @holroy.

[anon63144152]

A pleasure.