Datviewjs date manipulation

Help
1

I’m seeking help troubleshooting a DV js query.

I’ve created a DV query to sum a field value. The field is contained in multiple pages.

For context:
There is page for each workout, contained in folder “Workouts/2022”.
There is an inline DV field “WorkoutDuration” on each page.

The code is adequate when I use the slice method to define a subset of the array of pages.

When I try to be a little more elegant and define a subset of the pages using dates (the last 7 days in the example), I get a syntax error I cannot solve (Evaluation Error: SyntaxError: missing ) after argument list at DataviewInlineApi.eval). I suspect it’s related to my very limited understanding of date manipulation in DV and JS.

The entire code is below, but I believe the error is in the short segment bracketed by the comments.


const pagesPre = dv.pages('"Workouts/2022"')
.sort(k => k.file.name, "desc");

// code causing the error is below

let n = 0;
for(let i = 0; i < pagesPre.length; i++) {
  if(dv.date(today) - pagesPre[i].file.day <= dur(7 days)) {
      let pages[n] = pagesPre[i];
      n += 1;
    }
}

//end of error-generating code


let sumDuration = 0;
   for(let i = 0; i < pages.length; i++) {
     if(pages[i].WorkoutDuration) {
       sumDuration += pages[i].WorkoutDuration;
     }
   }


//let sumHours = sumDuration / 60;
let sumHours = Math.floor(sumDuration/60);
let minutes = sumDuration % 60;

dv.paragraph("total workout time:  " + sumHours + " hours "  + minutes + " minutes "  );
2

I solved this problem by creating a subset of the pages using Luxon dates and a more intellingent filtering mechanism. Code is below.


let today = DateTime.local().toISODate(); 
let weekAgo = DateTime.local().minus({days: 
 7}).toISODate();
 
let pagesPre = dv.pages('"Workouts/2022"')
	.sort(k => k.file.name, "desc")
	.filter(t => t.file.day.toISODate() <= today)
	.filter(t => t.file.day.toISODate() >= weekAgo);
	
let sumDuration = 0;
   for(let i = 0; i < pagesPre.length; i++) {
     if(pages[i].WorkoutDuration) {
       sumDuration += pagesPre[i].WorkoutDuration;
     }
   }

//let sumHours = sumDuration / 60;
let sumHours = Math.floor(sumDuration/60);
let minutes = sumDuration % 60;

dv.paragraph("total workout time:  " + sumHours + " hours "  + minutes + " minutes "  );
3

Topic

Summary
  • How to filter the data by duration and sum a duration in multiple pages?

Test

Summary
  • dataview: v0.5.46

input

Summary

dictionary files:

  • Location: “100_Project/01_dataviewjs/01_by_example/Q20_SumDuration/Q20_test_data”
  • Suppose that today is “1987-03-08”
  • Suppose that the notes from the past seven days are to be taken into consideration.
  • Therefore, ONLY dic_19870302 ,dic_19870305 and dic_19870308 are to be taken into consideration.

03

  • filename : dic_19870302 
---
Date: 1987-03-02
---
#Project/P03

WorkoutDuration:: 90 minutes
  • filename : dic_19870305 
---
Date: 1987-03-05
---
#Project/P03

WorkoutDuration:: 120 minutes

04_list

  • filename : dic_19870308 
---
Date: 1987-03-08
---
#Project/P03

WorkoutDuration:: 3 minutes, 7 minutes

07_excluded

  • filename : dic_19870316 
---
Date: 1987-03-16
---
#Project/P03

WorkoutDuration:: 360 minutes

08_null

  • filename : dic_19870801 
---
Date: 1987-08-01
---
#Project/P08

WorkoutDuration::

09_undefined

  • filename : dic_19870901 
---
Date: 1987-09-01
---
#Project/P09

DQL10_filter_by_duration_and_sum_duration_and_LIST

Summary

Main DQL

Code Name Data type Group By Purposes Remark
DQL10
_filter_by_duration
_and
_sum_duration
_and_LIST		 WorkoutDuration:
a duration
or a list of durations		 yes 0.“today” = “1987-03-08”
1.To filter by WorkoutDuration
2.To filter by file.day
3.To define a field variable F_dur_diff and let F_dur_diff = date("1987-03-08") - file.day;
4.To filter by F_dur_diff
5.To sort by file.link in descending order
6.To group by true
7.To display the result as a non-list		 1.Suppose that today is “1987-03-08”

2.Suppose that the notes from the past seven days are to be taken into consideration.

3.The sum expression in the DQL10 is based on the Conclusion in the following topic.
- Solutions: by Justdoitcc

Notes

Summary

to modify: “today” = “1987-03-08”

Original Example10: use date(“1987-03-08” )
```SQL
FLATTEN date("1987-03-08") - file.day AS F_dur_diff
```
Another Example11: use date(“today”)
```SQL
FLATTEN date("today") - file.day AS F_dur_diff
```

The same codes:

  • To filter the data by duration
Original Example10
```SQL
FLATTEN date("today") - file.day AS F_dur_diff
WHERE F_dur_diff <= dur("7 days") AND F_dur_diff >= dur("0 days")
```
Another Example11
```SQL
FLATTEN (date("today") - file.day).days AS F_dur_days
WHERE F_dur_days <= 7 AND F_dur_days >= 0
```
Another Example10B
```SQL

WHERE date("today") - file.day <= dur("7 days") AND date("today") - file.day >= dur("0 days")
```
Another Example11B
```SQL

WHERE (date("today") - file.day).days <= 7 AND (date("today") - file.day).days >= 0
```
Another Example10C
```SQL

WHERE date("today") - file.day <= dur("7 days") 
WHERE date("today") - file.day >= dur("0 days")
```
Another Example11C
```SQL

WHERE (date("today") - file.day).days <= 7 
WHERE (date("today") - file.day).days >= 0
```

Code DQL10_filter_by_duration_and_sum_duration_and_LIST

Summary_code 
title: DQL10_filter_by_duration_and_sum_duration_and_LIST =>0."today" = "1987-03-08" 1.To filter by `WorkoutDuration` 2.To filter by `file.day` 3.To define a field variable `F_dur_diff` and `let F_dur_diff = date("1987-03-08") - file.day;` 4.To filter by `F_dur_diff` 5.To sort by file.link in descending order 6.To group by true 7.To display the result as a non-list
collapse: close
icon: 
color: 
```dataview
LIST WITHOUT ID
      "total workout time:  " + sum(map(rows.WorkoutDuration, (e) => sum(default(e, 0))))

FROM "100_Project/01_dataviewjs/01_by_example/Q20_SumDuration/Q20_test_data"
WHERE file.day != null
WHERE WorkoutDuration != null

FLATTEN date("1987-03-08") - file.day AS F_dur_diff
WHERE F_dur_diff <= dur("7 days") AND F_dur_diff >= dur("0 days")

SORT file.link DESC
GROUP BY true

```

Screenshots(DQL10): “today” = “1987-03-08”

20221017_Q20_DQL10_output_a01
20221017_Q20_DQL10_output_a011170×260 31.7 KB

DVJS10_debug_the_first_original_code_of_kjz

Summary

Main DVJS

Code Name Data type Group By Purposes Remark
DVJS10
_debug
_the_first
_original_code
_of_kjz		 WorkoutDuration:
a duration
or a list of durations		 no To debug the first original code of kjz Suppose that today is “1987-03-08”

Notes

Summary

to modify(M11): “today” = “1987-03-08”

Original Example10: use dv.date(“1987-03-08” )
```JS
// M11. define pagesPre: gather relevant pages
// #####################################################################
//const pagesPre = dv.pages('"Workouts/2022"')
let pagesPre = dv
    .pages(
        '"100_Project/01_dataviewjs/01_by_example/Q20_SumDuration/Q20_test_data"'
    )
    //Added by Justdoitcc 2022-10-16 10:30
    .where((page) => page.file.day) 
    .where((page) => page.WorkoutDuration)
    .where((page) => dv.date("1987-03-08") - page.file.day <= dv.duration("7 days"))
    .where((page) => dv.date("1987-03-08") - page.file.day >= dv.duration("0 days"))    
    
    .sort((k) => k.file.name, "desc");
```
Another Example11: use dv.date(“today”)
```JS
// M11. define pagesPre: gather relevant pages
// #####################################################################
//const pagesPre = dv.pages('"Workouts/2022"')
let pagesPre = dv
    .pages(
        '"100_Project/01_dataviewjs/01_by_example/Q20_SumDuration/Q20_test_data"'
    )
    //Added by Justdoitcc 2022-10-16 10:30
    .where((page) => page.file.day) 
    .where((page) => page.WorkoutDuration)
    .where((page) => dv.date("today") - page.file.day <= dv.duration("7 days"))
    .where((page) => dv.date("today") - page.file.day >= dv.duration("0 days"))    
    
    .sort((k) => k.file.name, "desc");
```

code DVJS10_debug_the_first_original_code_of_kjz

Summary_code 
title: DVJS10_debug_the_first_original_code_of_kjz =>0."today" = "1987-03-08" 
collapse: close
icon: 
color: 
```dataviewjs
// M11. define pagesPre: gather relevant pages
// #####################################################################
//const pagesPre = dv.pages('"Workouts/2022"')
let pagesPre = dv
    .pages(
        '"100_Project/01_dataviewjs/01_by_example/Q20_SumDuration/Q20_test_data"'
    )
    //Added by Justdoitcc 2022-10-16 10:30
    .where((page) => page.file.day) 
    .where((page) => page.WorkoutDuration)
    .where((page) => dv.date("1987-03-08") - page.file.day <= dv.duration("7 days"))
    .where((page) => dv.date("1987-03-08") - page.file.day >= dv.duration("0 days"))
    
    .sort((k) => k.file.name, "desc");


// M11.DEB10 debug pagesPre: 
// #####################################################################
dv.span("##### M11.DEB10 debug pagesPre:");
dv.span(pagesPre.file.name);
dv.span(pagesPre.file.day);
dv.span(pagesPre.WorkoutDuration);


// M21.define pages:
// #####################################################################
//Remarked by Justdoitcc 2022-10-16 10:35
//Added by Justdoitcc 2022-10-15 13:30
//let pages = [];


// M23.update pages: 
// #####################################################################
//Remarked by Justdoitcc 2022-10-16 10:35
// let n = 0;
// for (let i = 0; i < pagesPre.length; i++) {
//     // M21.IF10 filter pagesPre:
//     // #####################################################################
//     //Remarked by Justdoitcc 2022-10-15 13:22
//     //if(dv.date(today) - pagesPre[i].file.day <= dur(7 days)) {
//     if (
//         dv.date("today") - pagesPre[i].file.day <= dv.duration("7 days") &&
//         // Added by Justdoitcc 2022-10-15 13:10
//         dv.date("today") - pagesPre[i].file.day >= dv.duration("0 days")
//     ) {
//         //Remarked by Justdoitcc 2022-10-15 13:30
//         //let pages[n] = pagesPre[i];
//         pages[n] = pagesPre[i];
//         n += 1;
//     }
// }


// M31.define sumDuration: To sum WorkoutDuration (milliseconds)
// #####################################################################
//Added by Justdoitcc 2022-10-16 10:40
let pages = pagesPre;

let sumDuration = 0;
for (let i = 0; i < pages.length; i++) {
    //Remarked by Justdoitcc 2022-10-16 10:40
    // if (pages[i].WorkoutDuration) {
    //     sumDuration += pages[i].WorkoutDuration;
    // }
    sumDuration += pages[i].WorkoutDuration;
}


//dv.span(sumDuration);
//dv.span(dv.func.typeof(sumDuration));


// M33.define sumHours and minutes:
// #####################################################################
//let sumHours = sumDuration / 60;

//Remarked by Justdoitcc 2022-10-15 13:40
//let sumHours = Math.floor(sumDuration/60);
//let minutes = sumDuration % 60;

let sumHours = Math.floor(sumDuration / 60 / 60 / 1000);
let minutes = (sumDuration / 60 / 1000) % 60;


// M41.Output sumDuration:
// #####################################################################
dv.span("##### M41.Output sumDuration:");
dv.paragraph(
    "total workout time:  " + sumHours + " hours " + minutes + " minutes "
);

Screenshots(DVJS10): “today” = “1987-03-08”

20221016_Q20_DVJS10_output_a01
20221016_Q20_DVJS10_output_a011170×590 37.2 KB

DVJS20_filter_by_duration_and_sum_duration_and_SPAN

Summary

Main DVJS

Code Name Data type Group By Purposes Remark
DVJS20
_filter_by_duration
_and_sum_duration
_and_SPAN		 WorkoutDuration:
a duration
or a list of durations		 no To filter the data by duration and sum a duration in multiple pages 1.Suppose that today is “1987-03-08”
2.Suppose that the notes from the past seven days are to be taken into consideration.
3.The sum expression in the DVJS20 is based on the Conclusion in the following topic.
- Solutions: by Justdoitcc

Notes

Summary

to modify(M11): “today” = “1987-03-08”

Original Example10: use dv.date(“1987-03-08” )
```JS
// M11. define pages: gather relevant pages
// #####################################################################
let pages = dv
    .pages(
        '"100_Project/01_dataviewjs/01_by_example/Q20_SumDuration/Q20_test_data"'
    )
    .where((page) => page.file.day) 
    .where((page) => page.WorkoutDuration)
    .where((page) => dv.date("1987-03-08") - page.file.day <= dv.duration("7 days"))
    .where((page) => dv.date("1987-03-08") - page.file.day >= dv.duration("0 days")) 
    .sort((page) => page.file.name, "desc");
```
Another Example11: use dv.date(“today”)
```JS
// M11. define pages: gather relevant pages
// #####################################################################
let pages = dv
    .pages(
        '"100_Project/01_dataviewjs/01_by_example/Q20_SumDuration/Q20_test_data"'
    )
    .where((page) => page.file.day) 
    .where((page) => page.WorkoutDuration)
    .where((page) => dv.date("today") - page.file.day <= dv.duration("7 days"))
    .where((page) => dv.date("today") - page.file.day >= dv.duration("0 days"))    
    .sort((page) => page.file.name, "desc");
```

code DVJS20_filter_by_duration_and_sum_duration_and_SPAN

Summary_code 
title: DVJS20_filter_by_duration_and_sum_duration_and_SPAN =>0."today" = "1987-03-08" 1.To filter the data by duration and sum a duration in multiple pages
collapse: close
icon: 
color: 
```dataviewjs
// M11. define pages: gather relevant pages
// #####################################################################
let pages = dv
    .pages(
        '"100_Project/01_dataviewjs/01_by_example/Q20_SumDuration/Q20_test_data"'
    )
    .where((page) => page.file.day) 
    .where((page) => page.WorkoutDuration)
    .where((page) => dv.date("1987-03-08") - page.file.day <= dv.duration("7 days"))
    .where((page) => dv.date("1987-03-08") - page.file.day >= dv.duration("0 days")) 
    .sort((page) => page.file.name, "desc");


// M21.define sumDuration: To sum WorkoutDuration (milliseconds)
// the data type of sumDuration: number
// #####################################################################
let sumDuration = dv.func.default(dv.func.sum(pages.WorkoutDuration), 0);


// M31.define dur_sumDuration:
// the data type of dur_sumDuration: duration
// #####################################################################
let dur_sumDuration = luxon.Duration.fromMillis(sumDuration);


// M41.Output dur_sumDuration:
// #####################################################################
dv.span("total workout time:  ");
dv.span(dur_sumDuration);

Screenshots(DVJS20): “today” = “1987-03-08”

20221016_Q20_DVJS20_output_a01
20221016_Q20_DVJS20_output_a011170×170 17.1 KB

4
  • Nice work!
  • Your first code was fixed on “2022-10-15”. And it was refactored on “2022-10-16”. You can look at the comments in the DVJS10.
  • The bug in the step M33 was also fixed in the DVJS10. But you haven’t fixed it in your second code.
  • The following content is the step M33 in the DVJS10.
Summary 
```JS
// M33.define sumHours and minutes:
// #####################################################################
//let sumHours = sumDuration / 60;

//Remarked by Justdoitcc 2022-10-15 13:40
//let sumHours = Math.floor(sumDuration/60);
//let minutes = sumDuration % 60;

let sumHours = Math.floor(sumDuration / 60 / 60 / 1000);
let minutes = (sumDuration / 60 / 1000) % 60;
```
1 Like
5

Thank you!

These examples are “information dense” and very helpful!

I am still studying them to understand all of the details, particularly the solution using DQL rather than JS. Despite not knowing JS, the DQL seems less intuitive to me than learning the JS code to complete desired queries. Part of my limitation is not recognizing data types and incorrectly mixing different data types, which your solution illustrates.

1 Like
6

This topic was automatically closed 7 days after the last reply. New replies are no longer allowed.