Datviewjs date manipulation

Help
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