Power Automate: Working with DateTime to Dataverse

As you know, working with DateTime is never an easy task. Especially if you also combine your Power Automate flow with Dataverse (which by default support showing DateTime based on the System User > Time Zone setting). So, today I will show a little bit of knowledge that I got while creating the Flow that I’ll show you.

I created a flow where I will call an API to get data that contains the DateTime (but in the Unix time format):

As you can see in the above picture, the “regularMarketTime” contains a strange integer value that is identified as Unix epoch time. To validate the value, I use the below code in .NET Fiddler:

using System;
					
public class Program
{
	public static void Main()
	{
		var dateTime = DateTimeOffset.FromUnixTimeSeconds(1683326453);
		Console.Write(dateTime +" " +dateTime.DateTime.AddHours(8));
	}
}

Which showing below result:

If we add the time given to AddHours(8) (convert to MYT/SGT) time, we can get 5/6/2023 6:40:53 AM which will be the correct value if we open the data in Dataverse. So here is how I convert the Unix time:

I get the above formula (which I also modified a bit) from this thread:

addSeconds('1970-1-1', Div(items('Apply_to_each')?['meta']?['regularMarketTime'],1),'yyyy-MM-dd hh:mm:ss tt')

Because in my scenario, I needed the full-time (including AM and PM), which is why we need to use the above formula.

As I inspect, the API will give UTC time. If we tried directly to insert that value into Dataverse:

Resulting in the correct value:

Summary

When working with Power Automate and we are using Dataverse Connector, the concept still the same with the plugin (we also need to work using UTC time and the system will automatically handle the rest). That is why in Power Automate we have functions like convertToUtc or Convert time zone action to make the task easier to do.

FYI, if you want to convert the DateTime automatically from xx Time Zone to xx from Dataverse, you can try to check Debajit’s blog which gives you a bit idea of how to do it.

Author: temmyraharjo

Microsoft Dynamics 365 Technical Consultant, KL Power Platform User Community Leader, Student Forever, Test Driven Development, and Human Code enthusiast. View all posts by temmyraharjo