Help understanding a query

173
8
01-23-2020 07:24 AM
MarieCline_Delgado
New Contributor III

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

0 Kudos
8 Replies
JoshuaBixby
MVP Esteemed Contributor

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?

0 Kudos
MarieCline_Delgado
New Contributor III

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.

0 Kudos
JoshuaBixby
MVP Esteemed Contributor

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?

0 Kudos
MarieCline_Delgado
New Contributor III

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.

0 Kudos
MarieCline_Delgado
New Contributor III

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

Looks like it's a range parameter.

JoshuaBixby
MVP Esteemed Contributor

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

0 Kudos
MarieCline_Delgado
New Contributor III

Same here!  Thanks for helping me explore to understand.  

0 Kudos
MichaelVolz
Esteemed Contributor

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.