Inline JavaScript query duration summation help

[rjgresham]

What I’m trying to do

I capture the time I’ve spent running each day in my Daily Note properties, in a field called “timeinterval” in hh:mm:ss format (although the property field is a text field). I also capture the miles run. I have an inline javascript query that I use to calculate the MTD and YTD miles run, but I would like to do something similar to capture the MTD and YTD time I’ve spent running. I am not javascript savvy, and have not been able to figure out how to do this.

Here is a screenshot of my properties:

And here is screenshot of the stats I’m surfacing in my daily note:

Lastly, here is the inline query I use to calculate the miles run per month:

$= dv.pages('"00-Administrative/Daily/2024/2024-03"').distance.values.reduce((a, b) => a + b, 0)

and per year:

$= dv.pages('"00-Administrative/Daily/2024"').distance.values.reduce((a, b) => a + b, 0)

Things I have tried

I have tried a number of different queries, including regular dataview, dataview js, and inline js, and have not had success. My preference is to use an inline js query because that will provide the succinct output I have for my other data points. If I need to reformat my timeinterval field to accommodate the query, I can do that. Any assistance or suggestions are much appreciated.

[holroy]

You mean some simple javascript such as:

`$= const msec = dv.pages('"00-Administrative/Daily/2024"').timeinterval.values.reduce((a, b) => { const t = b.split(":"); const add=moment.duration({hours: t[0], minutes: t[1], seconds: t[2]}); return a+add }, moment.duration(0, "s")) ; moment.utc(msec).format("HH:mm:ss")`

HeHe… Sorry couldn’t help myself, but there is not anything simple about this

Contrary to the other calculations we need to do this in two steps, I think. Where the first step is to use reduce on the timeinterval to calculate the duration in milliseconds, and then we use a little trick to display that duration in the more common “HH:mm:ss” format.

In order to calculate the duration we need to convert the timeinterval to a duration, and that is done through splitting the parts on the colon, :, and using moment.duration() to create a new duration. After this conversion, it’s kind of a standard reduce() operation.

Hopefully this works at your end too!

Here is the same script in a slightly more verbose form:

const msec = dv.pages('"00-Administrative/Daily/2024"')
  .timeinterval.values
  .reduce((a, b) => {
    const t = b.split(":");
    const add=moment.duration({
      hours: t[0], minutes: t[1], seconds: t[2]});
    return a+add 
  }, moment.duration(0, "s")) ;
moment.utc(msec).format("HH:mm:ss")`

[rjgresham]

Oh wow, you are incredible; this worked perfectly! Thank you so much!!

I can’t tell you how much time I’ve spent on this, and how many variations of this I have attempted. I really appreciate your help!

[holroy]

Hmm… When I posted it, it struck me that using this trick to create a better display might not work if it goes above 24 hours… Now I tested it, and it just goes around… That’s not good…

I was hoping we didn’t need to start manipulating the duration, but we might need to as you’re already up to 17h, and I reckon you don’t want to go back to 0 in a few days.

[rjgresham]

Ohh, that’s a good point. Hmm, what recommendations do you have for manipulating the duration?

[holroy]

Here is three alternatives:

• The original failing after 24 hours: moment.utc(msec).format("HH:mm:ss")
• Increasing the hours: (moment.duration(msec).asHours()|0||"00") + moment.utc(msec.asMilliseconds).format(":mm:ss")
• Using durationformat(): dv.func.durationformat(dv.func.dur(msec/1000 + "s"), "d'd' h'h' m'm'")

Which to choose depends on what time ranges you expect. The last one shows days, but not seconds, so that could be nice if you expect to be in that range. The second just keeps adding up the hours, which could be nice if you don’t expect to go too far up in hours. It’s not looking nice to do 1234:45:23.

The last option is by far the most flexible variant though, as you can then play with which parts you want to use, and can add leading zeros and what note if you choose to do so.

[rjgresham]

@holroy

dv.func.durationformat(dv.func.dur(msec/1000 + “s”), “d’d’ h’h’ m’m’”)

This is awesome; thank you again for your help. Can I buy you a coffee, or contribute somehow?

I am going with the last option you provided. I wanted to pull the number for last year and include a screenshot but sadly, I changed the format of my notes at the end of the year so the query doesn’t work for prior years. It does work beautifully for 2024 though, and I’m thrilled about this.

Thank you!!