Solved: Cannot filter retrieve PostGreSQL by date what structure to use?

Tried all sorts of variations on a simple date filter to retrieve items from PostGreSQL, all shown below, with a record that is present with a date of 2022-04-04 00:00:00.

It shouldn't be this hard, should it? Any help warmly welcomed, cheers, Richard U.K

"x-ms-connection-gateway-object-id":"eeee4ad3-8ca9-4b85-a9d7-f107965a5895","Timing-Allow-Origin":"*","x-ms-apihub-cached-response":"false","x-ms-apihub-obo":"true",
"Cache-Control":"no-store, no-cache","Date":"Tue, 21 Jun 2022 08:40:00 GMT","Content-Type":"application/json; charset=utf-8; odata.metadata=minimal",
"Expires":"-1","Content-Length":"345"},
"body":{"@odata.context":"https://europe-002.azure-apim.net/apim/postgresql/999999944dea4310afcd70bcc864c8c8/$metadata#datasets('default')/tables('clear.cx_imt_casualty_incident%20')/items","value":

How to filter on Incident date, sample data as returned from search on "Sunk" which works (last field)

"incident_date":"2022-04-04T00:00:00Z","incident_time":"10:10","incident_time_zone":"UTC-05:00","call_received_date":"2022-04-04T00:00:00Z","call_received_time":"12:12","call_received_time_zone":"Central Daylight Time","notified_by_name":"Chris","notified_by_company":"xx","notifier_role":"Change Lead","notifier_phone_number":"7736025930","notifier_email_address":"","notification_medium_name":"Email","shipping_region":"1","contact_duty_personnel_email":"Christopher.Mann2@xx.com","drill_indicator":true,"incident_status":"Closed","ballast_indicator":false,"xx_personnel_indicator":false,"notified_organizations":"TEST USCG\nTEST PORT AUTHORITY\nTEST P&I CLUB","notify_others":"shippingextcomms@xx.com","created_by_name":"Mann, Khris","created_date":"2022-04-04T11:57:34.046Z","last_updated_by_name":"Mann, Khris","last_updated_date":"2022-04-04T11:57:34.046Z","contact_personnel_mobile":"+187266452501","casualty_type":"Sunk"}]}}

incident_date eq '2022-04-04T00:00:00Z'

incident_date eq 2022-04-04T00:00:00Z

incident_date eq '2022/04/04 00:00:00'

incident_date gt '2022/04/04 00:00:00'

Http request failed: the content was not a valid JSON. Error while parsing JSON: 'Unexpected character encountered while parsing value: R. Path '', line 0, position 0.'

incident_date ge '2022/04/04 00:00:00'

Http request failed: the content was not a valid JSON. Error while parsing JSON: 'Unexpected character encountered while parsing value: R. Path '', line 0, position 0.'

"{
""host"": {
""apiId"": ""subscriptions/xxxxxxxxxxxxxxxxxxxxxxxxxxxx/providers/Microsoft.Web/locations/westeurope/runtimes/europe-002/apis/postgresql"",
""connectionReferenceName"": ""shared_postgresql_1"",
""operationId"": ""GetItems""
},
""parameters"": {
""table"": ""clear.cx_imt_casualty_incident "",
""$filter"": ""incident_date ge '2022/04/04 00:00:00'"",
""$select"": ""last_updated_date""
}
}"

incident_date ge '2020'

Http request failed: the content was not a valid JSON. Error while parsing JSON: 'Unexpected character encountered while parsing value: R. Path '', line 0, position 0.'
incident_date ge '2020-01-01 12:00:00'
Http request failed: the content was not a valid JSON. Error while parsing JSON: 'Unexpected character encountered while parsing value: R. Path '', line 0, position 0.'

incident_date eq '2022-04-04 00:00:00'

incident_date eq '04-04-2022 00:00:00'

incident_date eq '04-04-2022'

incident_date eq '2022-04-04'

Community site session details

Cannot filter retrieve PostGreSQL by date what structure to use?

Helpful resources

News and Announcements

Quick Links

Announcing our 2025 Season 2 Super Users!

Paul Stork – Community Spotlight

Congratulations to the June Top 10 Community Leaders!

Leaderboard > Power Automate

Featured topics

Product updates