Dataview plugin snippet showcase

justdoitcc · September 9, 2022, 8:10am

Topic

Summary

How to filter by duration?

Test

Summary

dataview: v0.5.46

Input

Summary

dictionary files

Location: “100_Project/02_dataview/Q90_Duration/Q90_test_data”
filename : dic_20020301
file.mtime: “2002-03-03T19:30:50”

---
Date: 2002-03-01
---
#Project/P03

WorkoutDuration:: 90 minutes

filename : dic_20020401
file.mtime: “2002-04-03T19:30:50”

---
Date: 2002-04-01
---
#Project/P04

WorkoutDuration:: 120 minutes

filename : dic_20020501
file.mtime: “2002-05-03T19:30:50”

---
Date: 2002-05-01
---
#Project/P05

WorkoutDuration:: 3 minutes, 7 minutes

DQL10_filter_by_duration_and_TABLE

Summary

Main DQL

Code Name	Data type	Group By	Purposes	Remark
DQL10 _filter _by_duration _and_TABLE	`F_dur_diff`: a duration	no	1.To define a field variable `F_dur_diff` and `let F_dur_diff = date("2002-05-03T23:59:59") - file.mtime;` 2.To filter by `F_dur_diff` 3.To sort by file.mtime in ascending order 4.To display the result as a table

Notes

Summary

The same codes:

To filter the data by duration

Original Example10

```SQL
FLATTEN date("2002-05-03T23:59:59") - file.mtime AS F_dur_diff
WHERE F_dur_diff <= dur("3 months") AND F_dur_diff >= dur("0 months")
```

Another Example11

```SQL
FLATTEN (date("2002-05-03T23:59:59") - file.mtime).months AS F_dur_months
WHERE F_dur_months <= 3 AND F_dur_months >= 0
```

Another Example10B

```SQL

WHERE date("2002-05-03T23:59:59") - file.mtime <= dur("3 months") AND date("2002-05-03T23:59:59") - file.mtime >= dur("0 months")
```

Another Example11B

```SQL

WHERE (date("2002-05-03T23:59:59") - file.mtime).months <= 3 AND (date("2002-05-03T23:59:59") - file.mtime).months >= 0
```

Another Example10C

```SQL

WHERE date("2002-05-03T23:59:59") - file.mtime <= dur("3 months") 
WHERE date("2002-05-03T23:59:59") - file.mtime >= dur("0 months")
```

Another Example11C

```SQL

WHERE (date("2002-05-03T23:59:59") - file.mtime).months <= 3 
WHERE (date("2002-05-03T23:59:59") - file.mtime).months >= 0
```

DQL10_filter_by_duration_and_TABLE

Summary_code

title: DQL10_filter_by_duration_and_TABLE =>1.To define a field variable `F_dur_diff` and `let F_dur_diff = date("2002-05-03T23:59:59") - file.mtime;` 2.To filter by `F_dur_diff` 3.To sort by file.mtime in ascending order 4.To display the result as a table
collapse: close
icon: 
color: 
```dataview
TABLE WITHOUT ID
      file.link AS "File",
      dateformat(file.mtime,"yyyy-MM-dd HH:mm:ss") AS "mtime",
      F_dur_diff AS "F_dur_diff"
      
FROM "100_Project/02_dataview/Q90_Duration/Q90_test_data" AND !#daily-note

FLATTEN date("2002-05-03T23:59:59") - file.mtime AS F_dur_diff
WHERE F_dur_diff <= dur("3 months") AND F_dur_diff >= dur("0 months")

SORT file.mtime ASC
```

Screenshots(DQL10)

Reference

Summary

Dataview: Sources