Using Frontmatter and Dataview/Tracker to build a timesheet

I’m coming from org-roam and the only thing I’m really missing is tracking my work hours. My current idea is the following:
In my daily notes I would create something along the lines of…

---
tags: Daily
work:
  2022-11-01T00:07 -- 2022-11-01T00:27
  2022-11-01T00:47 -- 2022-11-01T00:58
---

Then build a dataview/tracker query the fetches these time ranges and sums up my hours.
As I’m not familiar with JS/yaml, I’m struggling to understand the datatypes that are allowed in this frontmatter thingy. How would date ranges look like?

What I’m trying to do

In org-roam I can clock in and clock out of every headline/task. Using the tags on these headlines I can then build a time sheet (my todos have the top level tag #work and then split into projects). Example:

 ** TODO Example Task                                                   :work:
:LOGBOOK:
CLOCK: [2022-11-01 Tue 00:12]--[2022-11-01 Tue 00:22] =>  0:10
:END:

I’m ok with not being able to clock in and out of individual tasks, but after a couple hours I still can’t figure out how to properly keep track of time.
The closest resources I’ve found:
Tracking Every Minute With Obsidian - lacks example
Org-mode like time tracking? - unanswered
Time tracking with dataview and sum of values - close, but I don’t want to manually put in the hours it took me, just calculate it from timestamps

Any hint towards keeping track of time and building a timesheet would be appreciated

2 Likes

Here’s what I do, maybe there’s something useful here.

I track my time in a section in my daily note, in this format. I use Templater to insert the timestamps for me:

image

## 🕔 Time tracking
- 07:30-10:56 Some stuff
- 10:56-11:23 Another task, did some things
- 12:34-13:30 Third project

Then I have a master time tracking note which sums them all up for me using Dataview, in hours-per-week/day:

const tracked = {}
dv.pages('"Daily/Notes"').file.lists
  .where(x => x.section.subpath === "🕔 Time tracking").array()
  .forEach(x => {
    // Find the start/end times for each bullet point
    const times = x.text.match(/^(\d{2}:\d{2})-(\d{2}:\d{2})/)
    if (times) {
      const start = moment(times[1], 'HH:mm')
      const end = moment(times[2], 'HH:mm')
      const minutes = moment.duration(end.diff(start)).asMinutes()
      const date = x.path.match(/(\d{4}-\d{2}-\d{2})/)[1]
      const week = moment(date).format('YYYY, [Week] WW')
      if (!tracked[week]) tracked[week] = {}
      if (tracked[week][date]) {
        tracked[week][date].minutes += minutes
      } else {
        tracked[week][date] = {
          path: x.path,
          minutes: minutes
        }
      }
    }
  })

const hours = minutes => (minutes / 60).toFixed(1)

const table = []
Object.keys(tracked).sort((a, b) => b.localeCompare(a))
  .forEach(weekDate => {
    // Push weekly value
    const week = tracked[weekDate]
    const weekTime = Object.values(week).reduce((prev, curr) => prev + curr.minutes, 0)
    table.push([weekDate, '**' + hours(weekTime) + '**'])

    // Push daily values
    Object.keys(week).sort((a, b) => b.localeCompare(a))
      .forEach(date => {
        const link = `– [[${week[date].path}#🕔 Time tracking|${moment(date).format('dddd D MMMM')}]]`
        table.push([link, '– ' + hours(week[date].minutes)])
      })
  })

dv.table(['Date', 'Hours'], table)

The summary table looks like this, with links back to each day:

10 Likes

To this type of yaml:

---
tags: Daily
work:
  - 2022-11-01T00:07 -- 2022-11-01T00:27
  - 2022-11-01T00:47 -- 2022-11-01T00:58
  - 2022-11-01T01:10 -- 2022-11-01T02:34
---

I suggest a mix of DQL and DVJS (an alternative way for js dumbs like me):


// query for values per file
const query = `TABLE WITHOUT ID key AS "Day", rows.Time AS "work time", sum(rows.Time) as "total by day"
FROM #Daily
WHERE work
FLATTEN work AS W
FLATTEN date(split(W, " -- ")[1]) - date(split(W, " -- ")[0]) AS Time
GROUP BY file.link`

// executing the query and return results
let DQL = await dv.tryQuery(query);

// divider line
const hrArray = Array(3).fill('<hr style="padding:0; margin:0 -10px; border-top: 1px solid var(--background-modifier-border)">');

// query for global total
const query2 = `TABLE WITHOUT ID "**TOTAL**", " ", "**" + sum(rows.TT) + "**"
FROM #Daily
WHERE work
FLATTEN work AS W
FLATTEN date(split(W, " -- ")[1]) - date(split(W, " -- ")[0]) AS Time
GROUP BY file.link
FLATTEN rows.Time as TT
GROUP BY true`

// executing the global query and return results
let DQLglobal = await dv.tryQuery(query2);

// add lines and global values for the first query
DQL.values.push(hrArray)
DQL.values.push(DQLglobal.values.flatMap(r => r))
DQL.values.push(hrArray)

// render the final table
dv.table(DQL.headers, DQL.values);

// hide count number in first column
this.container.querySelectorAll(".table-view-table tr:first-of-type th:first-of-type > span.small-text")[0].style.visibility = "hidden";

2 Likes

That looks super cool! Can’t believe this got such a neat solution in such a short time :smiley:
Since I’m quite the newbie, I gotta ask:

  1. how do you execute that JS block? (I found this: GitHub - twibiral/obsidian-execute-code: Obsidian Plugin to execute code in a note., but that means you do it manually?)
  2. Would you mind sharing that templater template? Additionally, does that mean you always have to navigate to the daily note before inserting the time? (would be neat to do it with a small overlay from anywhere)

I think it would be super valuable if you could write this up/make a quick video about it. I think I can make it work with the 2 questions above, but from what I’ve found googling, there isn’t really anything out there… So would be very helpful for beginners :+1:

With Dataview. Just wrap the code in

```dataviewjs
code goes here
````

Super complex :stuck_out_tongue_winking_eye:

<% moment().format('HH:mm') + '-' %>

I do it from the daily note because that’s where I work from. But it would be 100% doable to write a Templater script the modifies the current daily note from a popup prompt, rather than having to open the note.

1 Like

Additionally, does that mean you always have to navigate to the daily note before inserting the time? (would be neat to do it with a small overlay from anywhere)

To automate this from anywhere in Obsidian, look at QuickAdd plugin, specifically its Capture functionality. You can use it to set up macro commands that you can fire up by pressing CTRL+P → your command, and it allows you to insert any text anywhere into any note, among other things. This Youtube tutorial by the plugin’s author goes into much more detail. You can then combine it with Dataview or anything else, really.

It’s a bit of a rabbit hole, but you can make very quick and powerful commands with this.

1 Like

thats awesome - thank you for the hint!

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