Skip to main content

Notifications

Power Automate - Using Connectors
Unanswered

POSTGRES ODATA time comparison

(0) ShareShare
ReportReport
Posted on by 8

I am trying to get all records from a postgres database where the date_completed (timestamp without timezone) is between startoftoday and endoftoday. However I cannot get the ODATA query for less than or greater than to work. I keep getting this error. 

 

{
 "status": 400,
 "message": "We cannot apply operator < to types DateTime and DateTimeZone.\r\n inner exception: We cannot apply operator < to types DateTime and DateTimeZone.\r\nclientRequestId: 12f42943-0acf-4b4a-a482-3d72ca9dbf98",
 "source": "postgresql-eus.azconn-eus.p.azurewebsites.net"
}

The odata filter is equal to 

utcnow('yyyy-MM-ddTHH:mm:ssZ’) lt date_completed

 This is the odata filter after the expressions run

2018-09-06T08:07:16Z lt date_completed
  • degvalentine Profile Picture
    degvalentine 193 on at
    Re: POSTGRES ODATA time comparison

    Couldn't use Azure Functions to access my database because it's on-prem and I don't want to pay for a VPN Gateway.

  • degvalentine Profile Picture
    degvalentine 193 on at
    Re: POSTGRES ODATA time comparison

    @ialastairhunter  2 great suggestions - Thank you!

     

    The OData workaround was a bit painful as you suggested, but it works. Doing a "my_ts >= yyyy-mm-dd" filter ended up looking like this (for any that might find this thread later):

    year(my_ts) gt yyyy or (year(my_ts) eq yyyy and (month(my_ts) gt mm or (month(my_ts) eq mm and day(my_ts) ge dd)))

    I'll try the Azure Function next and post results (and probably code).

  • ialastairhunter Profile Picture
    ialastairhunter 10 on at
    Re: POSTGRES ODATA time comparison

    @degvalentine 

     

    There are a few aditional options

    1 - Create an Azure Function app that would take a HTTP Post with a sql query string and return JSON Object with the query results. Then use an HTTP Connector in flow to pass a SQL Query to the azure function app. 

    2 - Use an Azure Logic app instead of flow and you can call an azure function app, or execute some javascript right from the azure logic app. 

     

  • ialastairhunter Profile Picture
    ialastairhunter 10 on at
    Re: POSTGRES ODATA time comparison

    @degvalentineI was able to solve this however it was not an ideal query. You will need to use the MONTH, DAY, and YEAR Functions in combintation with the greate than and less than operators. 

     

    For example if you wanted to compare the date was equal to 04/04/2013

    month(DATE) eq 04 AND day(DATE) eq 04 AND year(DATE) eq 2013

     

  • degvalentine Profile Picture
    degvalentine 193 on at
    Re: POSTGRES ODATA time comparison

    I'm in the same boat. My research leads me to believe there is no solution. I hope I'm wrong and am still searching...

     

    • our PG timestamp without time zone columns are considered "DateTime" fields in OData
    • OData intentionally dropped support for DateTime in some prev update (many years ago)
    • OData community is/was livid (https://github.com/OData/WebApi/issues/136) but apparently there's no intent to restore it

    I believe our only hope is that Flow will add support for "Execute a query" to on-prem gateways. Here's a link to the community topic. Add your vote!

  • elksson Profile Picture
    elksson 8 on at
    Re: POSTGRES ODATA time comparison

    IMG_0977.pngIMG_0978.png

  • elksson Profile Picture
    elksson 8 on at
    Re: POSTGRES ODATA time comparison

    Using filter contains '2018-09-05' works but since the table has over a million rows the query takes 22 mins to execute if I don’t use the database engine to filter the results.

     

    I will try the filter as you suggested but im sure if it does work the same issue will exist as the table just has too many rows. 

  • v-yuazh-msft Profile Picture
    v-yuazh-msft on at
    Re: POSTGRES ODATA time comparison

    Hi @elksson,

     

    Could you please share a screenshot of the configuration of your flow?

     

    Please take a try to add a "Filter array" action in your flow replace of the ODATA query, the flow should as below:

    Capture.PNG

     

     

    Best regards,

    Alice

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

Microsoft Kickstarter Events…

Register for Microsoft Kickstarter Events…

Announcing Our 2025 Season 1 Super Users!

A new season of Super Users has arrived, and we are so grateful for the daily…

Announcing Forum Attachment Improvements!

We're excited to announce that attachments for replies in forums and improved…

Leaderboard

#1
WarrenBelz Profile Picture

WarrenBelz 145,508

#2
RandyHayes Profile Picture

RandyHayes 76,287

#3
Pstork1 Profile Picture

Pstork1 64,839

Leaderboard

Featured topics