To convert the [WTHR_LAST_UPDATED] into a text output duration as DD:HH:MM:SS you can use the expression below:
(numberFormat("00",(floor((([UTC_TS]-[WTHR_LAST_UPDATED])/(86400*1000)))))):(numberFormat("00",(floor((([UTC_TS]-[WTHR_LAST_UPDATED])/(1000*60*60))%24)))):(numberFormat("00",(floor((([UTC_TS]-[WTHR_LAST_UPDATED])/(1000*60))%60)))):(numberFormat("00",(floor((([UTC_TS]-[WTHR_LAST_UPDATED])/(1000))%60))))
Explanation:
The code calculates and formats a time duration in days, hours, minutes, and seconds. Here’s a breakdown of each part:
Time Calculation:
[UTC_TS] and [WTHR_LAST_UPDATED] are likely placeholders for timestamps representing two points in time.
[UTC_TS] - [WTHR_LAST_UPDATED] calculates the difference between these timestamps in milliseconds.
/ (86400 * 1000) converts the milliseconds to days, floor() rounds it down to the nearest integer, and numberFormat(“00”, …) formats it as a two-digit number with leading zeros (e.g., “01”).
/ (1000 * 60 * 60) converts the milliseconds to hours, % 24 gets the remainder after dividing by 24 to ensure it’s within the 24-hour range, floor() rounds it down, and numberFormat(“00”, …) formats it as two digits.
/ (1000 * 60) converts the milliseconds to minutes, % 60 gets the remainder after dividing by 60 to ensure it’s within the 60-minute range, floor() rounds it down, and numberFormat(“00”, …) formats it as two digits.
/ (1000) converts the milliseconds to seconds, % 60 gets the remainder after dividing by 60 to ensure it’s within the 60-second range, floor() rounds it down, and numberFormat(“00”, …) formats it as two digits.
Formatting:
numberFormat(“00”, …) is used in each part to format the calculated values as two-digit numbers with leading zeros. This ensures consistent formatting, even if the values are single digits.
Result:
The final output will be a string in the format “DD:HH:MM:SS”, where DD is the days, HH is the hours, MM is the minutes, and SS is the seconds. The values will be formatted with leading zeros as described above.
Example:
If the time difference is 3 days, 1 hour, 23 minutes, and 45 seconds, the output would be “03:01:23:45”.
