This time I’m walking through the remaining Date and Time functions. These include the functions that convert between and work with timezones: convertFromUtc, convertToUtc, convertTimeZone, utcNow. But first, we’re going to take a look at ticks.
Just what the heck are ticks, anyway? Ticks are an integer value that represents the total number of 100-nanosecond intervals that have elapsed since January 1, 0001 at 12 am midnight, Universal time. These numbers can obviously get quite big. For example, the ticks value of September 9, 2022, at 10:00 AM UTC time is 637983144000000000.
The advantage of ticks is that since they are an integer values, they can often be much easier to work with than DateTime values. For example, if you want to find out the difference between two dates, adding or subtracting two integers is easier than adding or subtracting two date stamps.
That said, the use of ticks in Power Automate is rather limited for two very big reasons. Currently, there isn’t a built-in method for converting ticks back into dates. You can get around this with a manual conversion like so:
addSeconds('0001-01-01', div(TICKSVALUE, 10000000), 'yyyy-MM-dd')
But that’s a pain for something that should be built in.
The second problem is that ticks are more often used with timespans (i.e. 10 days or 1 hour) instead of actual dates themselves. And Power Automate has no built-in support for timespans.
So, generally, the usefulness of ticks in Power Automate is limited to just those occasions where some external connector requires a date value to be passed as a ticks value instead of a DateTime formatted value.
This function gets the current DateTime value for this exact moment. No, I mean now. No, now, now. Not then, now. Oh, we missed it. When? Sorry, we missed it. When? Just now.
utcNow() // returns now in UTC timezone, in ISO 8601 format
convertFromUtc & convertToUtc
These functions convert a DateTime between any other timezone to or from the equivalent UTC timezone value. This is valuable as many systems store DateTime values as UTC values (when they’re doing things right, anyways). But you will often want to present values to end users in their current timezone.
The formats are:
convertFromUtc('<TIMESTAMP>', '<TIMEZONE>', '<FORMAT>') convertToUtc('<TIMESTAMP>', '<TIMEZONE>', '<FORMAT>')
For convertFromUtc, the TIMEZONE parameter is the name of the timezone that the value should be converted into. For convertToUtc, the TIMEZONE parameter represents the name of the timezone that you are converting from.
The values of TIMEZONE should be taken from the standard list of Windows Default Time Zones. You’ll want to use the “Timezone” column value from the chart on that page. For example, the west coast United States timezone known generally as Pacific should be passed in as “Pacific Standard Time”.
The FORMAT parameter, as with other date and time functions, represents the format of the output and is optional. The TIMESTAMP parameter should always be passed in as the ISO 8601 default format.
convertFromUtc('2022-09-09T10:00:00Z', 'Eastern Standard Time') // returns '2022-09-09T06:00:00.0000000' convertToUtc('2022-09-09T10:00:00', 'Eastern Standard Time') // returns '2022-09-09T14:00:00.0000000Z'
This function works in the same manner as convertFromUtc and convertToUtc. The only difference is that you are passing in both the source and destination timezones to convert between.
convertTimeZone('<TIMESTAMP'>, '<SOURCETIMEZONE>', '<DESTINATIONTIMEZONE'>, '<FORMAT>')
convertTimeZone('2022-09-09T10:00:00', 'Eastern Standard Time', 'Pacific Standard Time') // returns '2022-09-09T07:00:00.0000000'
Wrapping It Up
But what about “daylight savings” time or other equivalent local government seemingly random and senseless adjustments to the regularly scheduled timezone, you say? I don’t see any of that in the Windows standard timezone list. Well, um… uh…. *hand wave* *hand wave* WHAT IS THAT OVER THERE?!?!?!?!?!?!?!
Did you forget your question, yet? No? Well, just always do everything in UTC and you’ll be fine.
That wraps up the date and time functions in Power Automate. Next time we’ll go over the URI parsing functions and working with web addresses.
Husband, father, gamer, developer, manager, writer, creative, blogger, model railroader, Buckeyes fan