Performing Calculations on Timestamps

dsixta

Scenario: I need to calculate how much working hours time has passed between a creation date and a completion date.

Parameter Exception: Item can be submitted after hours, but obviously that time wouldn't be counted until 7am the next day.

Example timestamp: 2019-12-10T16:24:48Z

Working hours: 7am to 8pm

Working days: M-F

Example:

CreateDate: 2022-12-06T21:24:38Z

CompleteDate: 2022-12-09T13:10:52Z

TurnAroundTime: 33 hours: 10 Minutes: 52 seconds OR 33:10:52

Note: This is very simple to do in Excel, but every way I have tried to do it in Domo has failed.

GrantSmith

This one was a bit tricky and fun and I spent way too much time on this. I ended up doing this in a Magic ETL2.0 formula tile and building off of the different components. I'll break it all down below and at the bottom will be the ETL code you can copy and paste into the new Magic ETL which should have it all for you.

Notes:

• You'll need to update your fields in the Modified Start Date and Modified End Date formulas.
• This doesn't take into account holidays.
• Assumes M-F work week.
• This hasn't been fully vetted with different edge cases but the ones I have tested with seemed to come out correctly. Be sure to test this our yourself.

First step I just put in my hour constants for the start and end hours, in your case they're set to 7 and 20 (24 hour clock)

Modified Start Date (step 1):

GREATEST(`Start Time`,
  DATE(`Start Time`) + INTERVAL `Day Start Hour` HOUR,
  DATE(`Start Time` + INTERVAL (24 - `Day End Hour`) HOUR) + INTERVAL `Day Start Hour` HOUR)

Get the greatest timestamp of the start time being either

1. The actual timestamp
2. The business start of the day (converting timestamp to date makes the time midnight / 0:00, adding the day start hours give you the business start of the day)
3. If the start time is after the end of the day and goes into the next day start it on the next day at the business start time

Modified Start Date (step 2):

`Modified Start Time` + INTERVAL
	CASE WHEN DAYOFWEEK(`Modified Start Time`) = 1 THEN 1
  	WHEN DAYOFWEEK(`Modified Start Time`) = 7 THEN 2
  ELSE 0
  END DAY

If we've been pushed into a Sunday Add another day, Saturday add two days to the start date.

Modified End Date (step 1):

LEAST(`End Time`,
  DATE(`End Time`) + INTERVAL `Day End Hour` HOUR,
  DATE(`End Time` - INTERVAL `Day Start Hour` HOUR) + INTERVAL `Day End Hour` HOUR)

Get the earliest end time being either:

1. The actual timestamp
2. The end of the business day for the End Time (if it's at 11pm it'll bump it back to 10pm)
3. If the end time is before the start of the next day bump it back a day to the end of the day

Modified End Date (step 2):

`Modified End Time` - (
  		INTERVAL (CASE WHEN DAYOFWEEK(`End Time`) = 7 THEN 1
  		 	WHEN DAYOFWEEK(`End Time`) = 1 THEN 2
	 		 		ELSE 0
  				END) DAY)

If we've been pushed into a Sunday subtract another day, Saturday subtract two days.

First Day (business seconds elapsed in the first day)

  (UNIX_TIMESTAMP(LEAST(`Modified End Time`, DATE(`Modified Start Time`) + INTERVAL `Day End Hour` HOUR)) - (UNIX_TIMESTAMP(`Modified Start Time`))) -- First Day

Get the unix timestamp for the earliest time. If the End time is in the same day use that, otherwise use the end of the day for the start time.

Last Day (business seconds elapsed in the last day)

(UNIX_TIMESTAMP(`Modified End Time`) - UNIX_TIMESTAMP(GREATEST(`Modified Start Time`, DATE(`Modified End Time`) + INTERVAL `Day Start Hour` HOUR))) -- Last Day

Similar to the first day beast mode this will get the latest time between the actual timestamp and the start of the day of the end date and subtract that from the end timestamp

Middle Days

(GREATEST((DATEDIFF(`Modified End Time`, `Modified Start Time`) - 1), 0) * (`Day End Hour` - `Day Start Hour`)) * 60 * 60 -- Middle days in working seconds

Gets the number of days between the two dates and multiplies that by the number of hours in the working day to get the number of working seconds for the days in-between.

Weekends:

FLOOR(DATEDIFF(`Modified End Time`, `Modified Start Time`) / 7) * 2 * (`Day End Hour` - `Day End Hour`) * 60 * 60

Determine the number of days between the two dates and divide by 7 taking the FLOOR to return the number of whole weeks in between the two dates. We then multiply it by 2 (Saturday and Sunday) and convert it into the number of working seconds (working hours in a day * 60 minutes in an hour * 60 seconds in a minute)

Working Time:

SEC_TO_TIME(
  `First Day`
+
`Last Day`
  + `Middle Days`
  - `Weekend Days`
  )

Add the seconds from the first day, last day, middle days and remove the amount of working time on the weekends to get the total number of seconds. Pass that into SEC_TO_TIME to give us a readable HH:MM:SS formatted duration.

ETL Code:

Copy and paste the following to insert the formula tile into your New Magic ETL:

{"contentType":"domo/dataflow-actions","data":[{"name":"Add Formula","id":"dc1bc960-c2c4-40a5-b6d2-680b3a92a987","type":"ExpressionEvaluator","gui":{"x":408,"y":132,"color":null,"colorSource":null},"dependsOn":["c009fb72-5395-460a-8c3e-b37032557743"],"removeByDefault":false,"notes":[],"expressions":[{"expression":"7","fieldName":"Day Start Hour","settings":null},{"expression":"20","fieldName":"Day End Hour","settings":null},{"expression":"GREATEST(`Start Time`,\n DATE(`Start Time`) + INTERVAL `Day Start Hour` HOUR,\n DATE(`Start Time` + INTERVAL (24 - `Day End Hour`) HOUR) + INTERVAL `Day Start Hour` HOUR)","fieldName":"Modified Start Time","settings":null},{"fieldName":"Modified Start Time","expression":"`Modified Start Time` + INTERVAL\n\tCASE WHEN DAYOFWEEK(`Modified Start Time`) = 1 THEN 1\n \tWHEN DAYOFWEEK(`Modified Start Time`) = 7 THEN 2\n ELSE 0\n END DAY","settings":null},{"expression":"LEAST(`End Time`,\n DATE(`End Time`) + INTERVAL `Day End Hour` HOUR,\n DATE(`End Time` - INTERVAL `Day Start Hour` HOUR) + INTERVAL `Day End Hour` HOUR)","fieldName":"Modified End Time","settings":null},{"fieldName":"Modified End Time","expression":"`Modified End Time` - (\n \t\tINTERVAL (CASE WHEN DAYOFWEEK(`End Time`) = 7 THEN 1\n \t\t \tWHEN DAYOFWEEK(`End Time`) = 1 THEN 2\n\t \t\t \t\tELSE 0\n \t\t\t\tEND) DAY)","settings":null},{"expression":" (UNIX_TIMESTAMP(LEAST(`Modified End Time`, DATE(`Modified Start Time`) + INTERVAL `Day End Hour` HOUR)) - (UNIX_TIMESTAMP(`Modified Start Time`))) -- First Day","fieldName":"First Day","settings":null},{"expression":"(UNIX_TIMESTAMP(`Modified End Time`) - UNIX_TIMESTAMP(GREATEST(`Modified Start Time`, DATE(`Modified End Time`) + INTERVAL `Day Start Hour` HOUR))) -- Last Day\n","fieldName":"Last Day","settings":null},{"expression":"(GREATEST((DATEDIFF(`Modified End Time`, `Modified Start Time`) - 1), 0) * (`Day End Hour` - `Day Start Hour`)) * 60 * 60 -- Middle days in working seconds","fieldName":"Middle Days","settings":null},{"fieldName":"Weekend Days","expression":"FLOOR(DATEDIFF(`Modified End Time`, `Modified Start Time`) / 7) * 2 * (`Day End Hour` - `Day End Hour`) * 60 * 60","settings":null},{"expression":"SEC_TO_TIME(\n `First Day`\n+\n`Last Day`\n + `Middle Days`\n - `Weekend Days`\n )","fieldName":"Working Time","settings":null}]}]}