Answered

Struggling with Filter Query

(0) Share

Report

Posted on by JeffC

I am struggling to get a filter query created for power automate to only retrieve a sharepoint items created within the last 60 minutes.

I am using the query below createdon addMinutes(convertFromUtc(utcNow(), 'Eastern Standard Time'),-60)

Any help that someone can provide would be greatly appreciated.

Categories:

Building flows

I have the same question (0)

All responses (9)

Answers (1)

Sort by

Ventit 422 on at

Like (0)

Report
Copy link

Link copied!

Try
Created ge 'addMinutes(addHours(utcNow(),10),-60)'
Add Hours brings it to my time zone EST Brisbane

Was this reply helpful? Yes No
Verified answer

Pstork1 69,485 Most Valuable Professional on at

Like (1)

Report
Copy link

Link copied!
The problem is that Power Automate will retrieve the Created Time as UTC so there is no need to adjust for the Time Zone. The following works for me in a filter array query.

@greaterOrEquals(item()?['Created'], addMinutes(utcNow(), -60))

and this works in the filter query of the Get Items

Created ge '@{addMinutes(utcNow(),-60)}'

Make sure to add the AddMinutes using the Expressions tab.

Was this reply helpful? Yes No
JeffC on at

Like (0)

Report
Copy link

Link copied!

This worked perfectly. Thank you!!!

Was this reply helpful? Yes No
Ventit 422 on at

Like (0)

Report
Copy link

Link copied!

Hi Pstork1, Are you saying that Created in Sharepoint stores at utc and is not based on your regional settings? This completely contradicts my understanding and puts some of my flows at risk. I assume new records being stored on current regional setting in SharePoint which makes the need to adjust utc to suit

Was this reply helpful? Yes No
Pstork1 69,485 Most Valuable Professional on at

Like (1)

Report
Copy link

Link copied!

The SharePoint backend always stored Dates and Times using UTC. The question is how are the dates being retrieved. Power Apps will normally convert the dates to the local user's regional time zone, or at least the time zone for the server in the cloud. SharePoint does the same thing. But Power Automate actions don't normally do that. They work with the stored dates as they are in UTC. Some actions will convert, but most do not.

So what I'm saying is that when a document/item is created SharePoint will take your local time, convert it to UTC and then store that in the database. So when something like a flow does a Get Items it doesn't do the conversion back automatically. It just gives you the UTC time that represents the time that was in your local time zone. So when doing a filter query you don't need to adjust for the local time zone. You just work in UTC.

Was this reply helpful? Yes No
Ventit 422 on at

Like (0)

Report
Copy link

Link copied!

Thanks for that. Appreciate the explanation. FYI. it has been my experience in extracting data I need to add my region 10 hours to the UTC, but that could be that I usually create a separate Date only field for a date created and need to use formatDateTime(addHours(utcNow(),10),'yyyy-MM-dd') before I get my proper number of records returned. I have never rationalised that field in flow against the creation date done by the system.

Was this reply helpful? Yes No
Ventit 422 on at

Like (0)

Report
Copy link

Link copied!

OK, I did a test and you are spot on. the image below is an extraction of one record showing the creation date and the Date field which was based on the creation date but with SharePoint Region applied which in my case is 10hors different

Was this reply helpful? Yes No
Pstork1 69,485 Most Valuable Professional on at

Like (0)

Report
Copy link

Link copied!

Date only fields will tend to cause difficulties because they store the time element as midnight. So often to get the right Date ONly value you need to shift time zone.

Was this reply helpful? Yes No
Pstork1 69,485 Most Valuable Professional on at

Like (0)

Report
Copy link

Link copied!

Another FYI. If you look at the raw date in the first compose you can see it ends in a 'Z'. That is the ISO 8601 indicator that this Date/Time is in UTC time zone.

Was this reply helpful? Yes No