Dataview - Calculation Sum of work hours (clock-in and clock-out) with overtime

Things I have tried

I have been searching the whole wide Internet, but couldn’t come up with a solutions that fits my idea.

What I have so far is the following:

table
		dateformat(file.day, "cccc") as "weekday",
		dateformat(file.day, "WW") as "calendarweek",
		clock-in as "clockin",
		clock-out as "clockout",
		date("2022-01-01T" + clock-out) - date("2022-01-01T" + clock-in) as "workhours"
from "Calendar/days"
sort file.name desc
table
		date("2022-01-01T" + clock-out) - date("2022-01-01T" + clock-in) as "work hours"
from "Calendar/days"
group by dateformat(file.day, "WW | yyyy") as "calendarweek"
sort desc

What I’m trying to do

Everyday I arrive at work and I write down my time in a daily note in frontmatter as “clock-in” and when I leave I also note the time as “clock-out”.
My company expects me to work exactly 38.5 hours a week. From Monday to Thursday I have a break for exactly 45 minutes.
Usually I have to come to work at 07:00 and have to leave at 16:00, except fridays I leave earlier at 12:30.

However I work overtime sometimes and I want to know how many hours per day/week/month/year I have worked overtime, so I can know when to ask for time compensation.

I have an extra Note, where I want to keep track of all this information.
This is how it looks:

I havent figured out, how to achieve this work hour tracking, so I am asking you.

Additional Information:

I also want to track my time, where I am on vacation and/or am sick.

So how do you think would be the best solution to achieve this?

Small fixes:

  • When you use a GROUP BY in your table, you need to change any field names in the displayed columns to be rows.field.
  • The result of date() - date() in dataview is a dur(), so you need to pull the hours field from that before you can get the total.
  • Then I think you want sum to total the hours for the week. So for example :
table
		sum((date("2022-01-01T" + rows.clock-out) - date("2022-01-01T" + rows.clock-in)).hours) as "work hours"
from "Calendar/days"
group by dateformat(file.day, "WW | yyyy") as "calendarweek"
sort desc

Good luck!

Hm I tried that, but I get the following result:

Ooops, I must have made a mistake then, sorry! How I would investigate the issue would be to display pieces of the calculation and see where there is an issue. For example, just show the “workhours” (should be a list for each week), then if that shows up, do the conversion to hours, then the sum.
A typo in my previous query (would have only affected the English version, not the one in your images): if your field name contains a - then write it as rows["clock-out"] rows["clock-in"]. if your fieldname has no punctuation, can use the rows.fieldName way of writing it in the groupby.

Also it looks like your sort desc does not work - I think you need sort calendarweek desc?

Hey thanks for your help and the quick answer, unfortunatly it doesnt resolve the issue.
Still no data the way I would like to see it^^

I guess, I have to invest some more of my, hopefully yours and the communities brain power into this = )

1 Like

Hi, so sorry that my answers are not helping! Just to confirm: is your dataview plugin up to date (Settings → Community Plugins → Check for Updates button)?

I do not have the same time-tracking as you in my daily notes but I did experiment with a numeric field I have tasks-done-today.

TABLE sum(rows["tasks-done-today"]) AS "sum"
FROM "testFolder"
GROUP BY dateformat(file.day, "WW") as "calendarWeek"
SORT calendarWeek DESC

This appears to be working correctly in my vault, including seeing the output table sorted in the correct order. I cannot tell how your example differs from this one that works - maybe you can? Or maybe @Craig or @justdoitcc can add some more dataview expertise?
Good luck!

Edit: typo in codeblock

Hi all,

Thanks for pinging me. This is an interesting problem! I think the issue is that we have a calculated field (workhours) that needs to be calculated per file before we can aggregate it up to the week level. Here’s the query I used to do the calculation:

```dataview
TABLE
	sum(map(rows, (item) => 
		date("2022-01-01T" + item.clock-out) - 
		date("2022-01-01T" + item.clock-in))) 
		as "workhours"
FROM "Scratch"
WHERE file.day
GROUP BY dateformat(file.day, "WW | yyyy") as "calendarweek"
SORT calendarweek desc
```

Before we can sum hours for the week, we must first use the map() function to iterate over each file and calculate the work hours for the day. That returns a list of durations, which we then use the sum() function to add together into a single field, which we name “workhours”.

I sort by week descending in this example, since that seems to be what you intended in your query.

(The FROM and WHERE clauses are different than yours, so as to just select the files from my particular vault.)

Finally, here’s a working example from my vault:

I hope this helps! Please let us know if it works for you.

3 Likes

@Craig thanks! When I was testing things for my first post in this thread, I got an error about sum not handling Durations, but yours seems to be fine with that. Any idea why we got different results?

@scholarInTraining The bug is found!


Topic

Summary

1.How to debug a code in DQL or Inline DQL?
2.How to calculate the difference between two DateTimes as a Duration?
3.How to sum up the duration of a column(F_workhours), grouping by another column(F_calendarweek)?


1. Input

Summary

1.1. dictionary files

  • location: “100_Project/02_dataview/Q09_Sum_groupBy/Q09_test_data”

1.1.1. N06

  • filename : dic_20220601_clock
---
Date: 2022-06-01
clock-in: 06:30
clock-out: 13:25
---

  • filename : dic_20220602_clock
---
Date: 2022-06-02
clock-in: 06:00
clock-out: 14:10
---

  • filename : dic_20220603_clock (wrong input)
---
Date: 2022-06-03
clock-in: 06:00ice
clock-out: 14:10coffee
---

1.1.2. L07

  • filename : dic_20220704_clock
---
Date: 2022-07-04
clock-in: 07:20
clock-out: 14:00
---

  • filename : dic_20220705_clock
---
Date: 2022-07-05
clock-in: 07:20
clock-out: 14:00
---

  • filename : dic_20220709_weekend
---
Date: 2022-07-09
clock-in: 
clock-out: 
---


2. DQL10_debgug_Small_fixes_of_scholarInTraining

Summary

2.1. Main DQL

Code Name Data type Group By Purposes Remark
DQL10_debgug_Small_fixes
_of_scholarInTraining
a date format string, or a list yes 1.To debgug: date("2022-01-01T" + rows.clock-out) step by step
2.To sum hours for the week of groupBy data
A bug ocurrs.

2.2. Inline DQL

a_Result11==map([10, 20, 30], (e) => e + 6)
//=>16, 26, 36

a_Result12==map(["A", "B", "C"], (e) => e + "P")
//=>AP, BP, CP

2.2.1. OK

s_an_ISO_string=="2022-01-01T" + "13:25"//=>2022-01-01T13:25
a_an_ISO_string==map(["13:25","14:10"], (e) => "2022-01-01T"+ e)
//=>2022-01-01T13:25, 2022-01-01T14:10

2.2.2. the bug: NG

string + array = string , but not an ISO date string

s_Not_an_ISO_string=="2022-01-01T" + ["13:25","14:10"]//=>2022-01-01T13:25, 14:10

2.3. Notes

  • 2022-07-26 add WHERE row["clock-in"] AND row["clock-out"]

2.4. Code DQL10_debgug_Small_fixes_of_scholarInTraining

Summary_code
title: DQL10_debgug_Small_fixes_of_scholarInTraining =>1.To debgug: `date("2022-01-01T" + rows.clock-out)` step by step 2.To sum hours for the week of groupBy data
collapse: close
icon: 
color: 

```dataview
TABLE WITHOUT ID
      calendarweek AS "Group",
	  sum((date("2022-01-01T" + rows.clock-out) - date("2022-01-01T" + rows.clock-in)).hours) AS "work hours",

      rows.clock-out as "S1_rows.clock-out",
        "2022-01-01T" + rows.clock-out AS "S2_ISO_string",
      date("2022-01-01T" + rows.clock-out) AS "S3_date_S2",
      typeof(date("2022-01-01T" + rows.clock-out)) AS "S4_type_S3"        
FROM "100_Project/02_dataview/Q09_Sum_groupBy/Q09_test_data"
WHERE file.day
WHERE row["clock-in"] AND row["clock-out"]
GROUP BY dateformat(file.day, "WW | yyyy") AS "calendarweek"
SORT DESC
```

2.3.1. Screenshots(DQL10)


3. DQL20_FLATTEN_calendarweek

Summary

3.1. Main DQL

Code Name Data type Group By Purposes Remark
DQL20_FLATTEN_calendarweek a date format string, or a list yes 1.To sum hours for the calendarweek
2.To use FLATTEN to transform lists into non-lists
3.To use FLATTEN to create an alias for a new field

3.2. Notes

  • 2022-07-26 add WHERE row["clock-in"] AND row["clock-out"]
  • 2022-08-02 add WHERE F_clock_in AND F_clock_out

3.3. Code DQL20_FLATTEN_calendarweek

Summary_code
title: DQL20_FLATTEN_calendarweek => 1.To sum hours for the calendarweek 2.To use `FLATTEN` to transform lists into non-lists 3.To use `FLATTEN` to create an alias for a new field
collapse: close
icon: 
color: 
```dataview
TABLE WITHOUT ID 
      file.link AS "File",
      F_weekday AS "weekday",
      F_calendarweek AS "calendarweek",
      clock-in AS "clockin",
      clock-out AS "clockout",     
      F_workhours  AS "workhours"
FROM "100_Project/02_dataview/Q09_Sum_groupBy/Q09_test_data"
WHERE file.day
WHERE row["clock-in"] AND row["clock-out"]
SORT file.name desc
FLATTEN file.name AS F_name
FLATTEN dateformat(file.day, "cccc") AS F_weekday
FLATTEN dateformat(file.day, "WW | yyyy") AS F_calendarweek
FLATTEN date("2022-01-01T" + row["clock-in"]) AS F_clock_in
FLATTEN date("2022-01-01T" + row["clock-out"]) AS F_clock_out
FLATTEN F_clock_out - F_clock_in AS F_workhours
WHERE F_clock_in AND F_clock_out
```

3.2.1. Screenshots(DQL20)


4. DQL30_FLATTEN_groupBY_calendarweek

Summary

4.1. Main DQL

Code Name Data type Group By Purposes Remark
DQL30_FLATTEN_groupBY
_calendarweek
a date format string, or a list yes 1.To sum hours for the week
2.To use FLATTEN to transform lists into non-lists
3.To use FLATTEN to create an alias for a new field
4.Not to use map to dael with a list
1.To use map such as Craig : To calculate field (workhours) by using map to iterate over each element of lists is a basic method.

4.2. Notes

  • 2022-07-26 add WHERE row["clock-in"] AND row["clock-out"]
  • 2022-08-02 add WHERE F_clock_in AND F_clock_out

4.3. Code DQL30_FLATTEN_groupBY_calendarweek

Summary_code
title: DQL30_FLATTEN_groupBY_calendarweek =>1.To sum hours for the week of groupBy data 2.To use `FLATTEN` to transform lists into non-lists 3.To use `FLATTEN` to create an alias for a new field 4.Not to use `map` to dael with a list
collapse: close
icon: 
color: 
```dataview
TABLE WITHOUT ID 
      GF_calendarweek AS "Group",
      sum(rows.F_workhours) AS "workhours"
FROM "100_Project/02_dataview/Q09_Sum_groupBy/Q09_test_data"
WHERE file.day
WHERE row["clock-in"] AND row["clock-out"]
FLATTEN file.name AS F_name
FLATTEN dateformat(file.day, "WW | yyyy") AS F_calendarweek
FLATTEN date("2022-01-01T" + row["clock-in"]) AS F_clock_in
FLATTEN date("2022-01-01T" + row["clock-out"]) AS F_clock_out
FLATTEN F_clock_out - F_clock_in AS F_workhours
WHERE F_clock_in AND F_clock_out
GROUP BY F_calendarweek AS GF_calendarweek
SORT GF_calendarweek DESC

```

4.2.1. Screenshots(DQL30)


2 Likes

Thank you very much @justdoitcc !! That was very educational for me. :slight_smile:

1 Like

Hmm, no, not off the top of my head. If you’d like to investigate further, you could post your data files and your query, and I could try to troubleshoot the error.

1 Like

That’s clever! I like the way you used FLATTEN to create calculated fields.

2 Likes

wow thank you all, that is brilliant!
I got the list right now the way I wanted it!

really great!!!

1 Like

You could post your data files as a zip file, and I could try to troubleshoot the error.

1 Like

I found the reason. It’s weekends. If you don’t work on weekends, there is no clock-in and clock-out. :smiley:

1 Like

Thank you! It is taken into consideration.

1 Like

Yes, with filtering added it’s fine

1 Like

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