Help understanding a query

[MarieCline_Delgado]

Looking for a little help understanding what the Where clause of this SQL query is doing. This is used to define a query layer in an ArcPro map.  DBMS is postgresql

SELECT

   a.id, a.valid_date, a.id, a.name, a.net, a.elevation, a.mph, a.local_time, a.shape
FROM

   obs.gusts AS a
WHERE

   ::r:DateTime

I understand that the last line is a cast function, but I do not understand what the "r" represents and how it's filtering the data.

Thanks in advance

[JoshuaBixby]

Most of the SQL statement is straightforward, but there are a couple of things about the WHERE clause that are odd to me.  How are you extracting this query?  Are you copying and pasting it or typing it from what you seen on a screen?

[MarieCline_Delgado]

The query is a property of an existing Query Layer in a map within ArcPro.  I copied/pasted from the properties into the message above.

[JoshuaBixby]

I am stumped, then.  You are correct that double colons are historical Postgres syntax for type casting, and type casting can be chained using the old syntax:

"1"::real::integer‍‍

Postgres isn't my primary DBMS, but 2 things are throwing me:  1) type casting requires an expression to the left of the double colon, which appears to not exist; and 2) single quotes are usually used for slicing arrays but that doesn't make sense given the syntax.

This may be some clever use of Postgres operators, but clever is the death of readability and maintainability.

Does the query layer give the same results if you remove the WHERE clause?

[MarieCline_Delgado]

I share all of these thoughts and feelings with you!  

I did try removing the WHERE clause and it DOES change the results.   

I would expect that it is filtering a time field, but I haven't been able to find a pattern yet.

[MarieCline_Delgado]

I finally found something. 
Define parameters in a query layer—Query layers | ArcGIS Desktop 

Looks like it's a range parameter.

[JoshuaBixby]

Nice catch.  I was so focused on the Postgres part that I forgot about the possibility of it being an ArcGIS issue.

[MarieCline_Delgado]

Same here!  Thanks for helping me explore to understand.  

[MichaelVolz]

The range parameters are a powerful feature of a query layer as per this thread which goes to the same link as you:

https://community.esri.com/thread/205904-dynamic-date-value-in-filter-widget-using-arcgis-online 

This might be of use to you if you want to use range parameters in a web application.