Display aggregate of total time as HH:MM

[guardmetrics]

I have a table which includes a timestamp and a type field (among others) I've created triggers and a view that combines this table into itself and displays a clock-in time, clock-out time, and a total time difference between them in HH:MM format.  The formula in the calculated field is below:

CONVERT(varchar(5),  DATEADD(minute, DATEDIFF(minute, [@field:Patrol_Activity_Database_Date], [@field:Patrol_Activity_Database_1_Date]), 0), 114)

My issue is that I need to have a total/aggregate for this field, but it gives an invalid error if I just do sum, and I've been unable to figure out how to get it to add the time and display it in the HH:MM format.

Any help would be great!

[Hastur]

@guardmetrics

The calculated field should look like this in:

CAST(((
  DATEDIFF(minute, [@field:Patrol_Activity_Database_Date], [@field:Patrol_Activity_Database_1_Date]) 
	- 
  (DATEDIFF(minute, [@field:Patrol_Activity_Database_Date], [@field:Patrol_Activity_Database_1_Date]) % 60)
)
/
60) AS nvarchar)
+
':'
+
CAST((DATEDIFF(minute, [@field:Patrol_Activity_Database_Date], [@field:Patrol_Activity_Database_1_Date]) % 60) AS nvarchar)

Also you may find other examples here - 

 

[guardmetrics]

@Hastur This is working great.  The only thing is that it is showing time like this " 8:0" instead of "8:00". Any idea on how to get it to show 2 digits to the right of the colon?

[Hastur]

@guardmetrics

You may use this formula:

CAST(((
  DATEDIFF(minute, [@field:Patrol_Activity_Database_Date], [@field:Patrol_Activity_Database_1_Date]) 
	- 
  (DATEDIFF(minute, [@field:Patrol_Activity_Database_Date], [@field:Patrol_Activity_Database_1_Date]) % 60)
)
/
60) AS nvarchar)
+
':'
+
CASE WHEN LEN(CAST((DATEDIFF(minute, [@field:Patrol_Activity_Database_Date], [@field:Patrol_Activity_Database_1_Date]) % 60) AS nvarchar)) = 1
  THEN '0' + CAST((DATEDIFF(minute, [@field:Patrol_Activity_Database_Date], [@field:Patrol_Activity_Database_1_Date]) % 60) AS nvarchar)
  ELSE CAST((DATEDIFF(minute, [@field:Patrol_Activity_Database_Date], [@field:Patrol_Activity_Database_1_Date]) % 60) AS nvarchar)
END

 

[guardmetrics]

@HasturThanks so much!  How do you make this work in the aggregate?

[Kurumi]

Hi! Caspio now offers integration with OpenAI, so you can use extensions to leverage AI to update your data based on a prompt. Here's a sample use case to get the total worked hours based on Time In and Time Out fields.

Request:
Calculate the total working hours for the following time entries:

Time In: [@field:Clock_In]
Time Out: [@field:Clock_out]

The format should be HH:MM

Insert 'File a leave' if there are no values in:

Time In: [@field:Clock_In]
Time Out: [@field:Clock_out]

Return only the final result.

Result:

Change the 'Clock_In' and 'Clock_out' fields to your right field names.

To learn more about Extensions, you can check it here: https://howto.caspio.com/integration/extensions/