Converting UTC Date Time to UNIX Time with Power Automate

Recently Ben Vollmer asked if it was possible to convert a UTC datetime value to Unix time in Power Automate. There’s no function available out of the box to do this, but it seemed pretty achievable to me and I’ve outlined the solution I came up with below.

According to Wikipedia, Unix time is a system for describing a point in time; it is the number of seconds that have elapsed since the Unix epoch, minus leap seconds. the Unix epoch is 00:00:00 UTC on 1 January 1970. Leap seconds are ignored with a leap second having the same Unix time as the second before it, and every day is treated as if it contains exactly 86400 seconds.

Since we know this, we just need to calculate how many seconds have passed between the Unix epoch and the datetime we want to convert.

The Solution

The solution is a single Compose action with the following expression:

div(sub(ticks(triggerBody()['text']),ticks('1970-01-01Z00:00:00')),10000000)

As this expression contains a few different functions, I’ve broken it out into separate actions below to explain how it works:

1. Convert Unix Epoch to Ticks representation – as there is no datediff function in Power Automate we need to use the ticks value for our dates to get the time difference between them. The ticks function gives us the 100 nanosecond interval for a specified datetime value.

2. Convert Input timestamps to Ticks representation – just like in the previous step we are converting the value we want to convert to Unix time to it’s ticks representation. In my Flow I’m using a manually triggered flow with a string input field, but you could use the utcNow() function to convert the current datetime or you could use convertToUtc() for a value from an existing data source.

3. Subtract Unix Epoch from Input Timestamp – now that we’ve converted the Unix Epoch and the Input Timestamp to their ticks representations, we subtract the Unix Epoch value from the Input Timestamp value and this will give us the amount of 100- nanosecond intervales that have elapsed between the dates.

4. Divide subtraction result by 1000000 to get Unix Time – As Unix Time is the amount of seconds that have elapsed since the Epoch we divide the 100-nanosecond intervals result by 1000000 to get the Unix Time value.

Conclusion

To make the Flow more efficient and to only use a single action we combine the 4 steps above into a single action to carry out our conversion

Unix time is useful for computer system to track and sort dated information in dynamic and distributed applications both online and client side as it is a point in time that does not change no matter where you are located on the globe.

If you’d like to download a copy of this solution for yourself then click here.

Original Post https://ryanmaclean365.com/2020/08/03/converting-utc-date-time-to-unix-time-with-power-automate/