Convert a Date to Unix Format in a Flow

I had to learn about Rest APIs at work. Quickly. Oh and Azure as well, never having properly used either before.

Without going into unnecessary detail, I needed to extract data through a Rest API in order to build up an SQL table and to be efficient my requests were going to need to use a unix style date.

By this point I'd built an Azure logic app to listen for webhooks and a custom Power Automate connector for this Rest API so I had commited to doing this as a flow. I spent a while looking on the web and finding threads saying it couldn't be done, plus one that had a complicated function I couldn't understand, so I didn't approve of. Eventually I came up with my own solution, so in the spirit of this site here it is.

What is a unix date?

Put simply, a unix date is an integer that represents the number of seconds that have passed since 01 January 1970.

On my trawl through the internet I found someone asking the question "How do I convert to Excel date?" and the explanation for that struck a chord with me as something I could adapt. It contained a function called ticks() and in Azure this has the description of:

ticks(timestamp: string) - Returns the number of ticks (100 nanoseconds interval) since 1 January 0001 00:00:00 UT of a string timestamp

I've no idea who decided this would make a brilliant and intuitive clock, but let's go with it since, being away back at the beginning of time, it's the base date we need.

We'll stick with calling this period of 100 nanoseconds a "tick" for brevity. If we count how many ticks it has been from the big bang up to today's date and subtract how many ticks it was from the big bang until 01/01/1970, then what's left is the number of ticks between 01/01/1970 and today's date. That would look like:

sub( ticks(utcNow()),ticks('1970-01-01') )

This calculation tells us the number of ticks that have passed since 01 January 1970. So how many seconds is that?

A nano denotes a factor of 10⁻⁹ or nine zeroes after the decimal point, but a tick is 100 nanoseconds (0.0000000001 * 100) or 7 zeros, so to calculate how many seconds instead of ticks that is, we do:

div( sub(ticks(utcNow()),ticks('1970-01-01')) ,10000000)

And that's it except to customise it to your needs. I wanted to control how many days in the past to go, so I initialised an integer variable called DaysAgo. Then, because the development was 'agile' (bordering on 'seat of your pants') and I thought one day I might want to use a date instead, I initialised a standalone string variable called StartDate and gave it the interim function of:

formatDateTime(subtractFromTime(utcNow(),variables('DaysAgo'),'day'),'yyyy-MM-dd')

The formatDateTime part knocks off the timestamp from the date, so you can omit that if needed and subtract hours or whatever.

This let me change my unix date function to:

div(sub(ticks(variables('StartDate')),ticks('1970-01-01')),10000000)

You could obviously get fancy and merge the variables into one big calculation.

Testing this with 2 DaysAgo gave me 1614729600, which when plugged into https://unixtime-converter.com/ is 'Wed Mar 03 2021 00:00:00 GMT+0000 (Greenwich Mean Time)'