Dataview/DataviewJS: Issue with date input

[d.barleben]

Hey y’all, I have a question on calculating time duration in hours.
I intend to build a little ‘office hour tracker’ and want to have a table that lists not only the start time clockIn and the end time clockOut but also the duration I worked in hours (e.g. “8.5h” or “8h 30min”). The clockIn and clockOut values are given in HH:mm format.

That’s what I have so far, written in DQL:

TABLE
    dateformat(file.day, "cccc") AS "Weekday",
    clockIn AS "Clock In",
    clockOut AS "Clock Out",
    (clockOut - clockIn).hours AS "Work hours"
FROM "DAILY NOTES" WHERE type = "daily review"
SORT file.name DESC

When the clockIn and clockOut values are given in ISO format (YYYY-MM-DDTHH:mm) the table works. Otherwise the time format seems to be unrecognized (mnvwvnm helped me already to find that out in the discord dataview thread).
The issue now is that I can’t change the time format because I use the tracker plugin as well and tracker can’t process the ISO times (aargh).

Is there now maybe a way to prepend the HH:mm time with the corresponding date of the daily note to get the time in ISO format before creating the table as shown above using DataviewJS?
Or maybe a totally different way to achieve what I want?

Thanks a lot for your help! I appreciate it a lot!

[mnvwvnm]

To “construct” the ISO format required by Dataview (YYYY-MM-DDTHH:mm) you already have HH:mm). The rest we can create it with a false 2022-02-01T date >>> date("2020-02-01T" + clockOut)

TABLE
    dateformat(file.day, "cccc") AS "Weekday",
    clockIn AS "Clock In",
    clockOut AS "Clock Out",
    date("2020-02-01T" + clockOut) - date("2020-02-01T" + clockIn) AS "Work hours"
FROM "DAILY NOTES" WHERE type = "daily review"
SORT file.name DESC
``