Definition expression makes labels disappear

[Anonymous User]

I have a map, taken offline from a webmap in ArcGIS Online. There is a layer in it with labels enabled. The label is fairly simple, just labelling off a single field. The labels display fine on the map.

Until, that is, I apply a very specific definitionExpression to the layer.

I can apply various different definition expressions and the labels continue to display fine. But if I try this particular expression the labels disappear:

"(CAST(strftime('%s', proposedRevisitDate)  AS  integer) >= 1501545600"

In this example, 'strftime' should take the value found in the 'proposedRevisitDate' field and return it as seconds since 1970, which should then be CAST as an integer, and then compared to the number on the right.

The SQL expression does appear to resolve ok, as the map layer is successfully filtered to the expected features. The only weird thing is that suddenly the labels disappear.

If I remove the definition expression, the labels reappear.

I have checked that layer.labelsEnabled is always true which it is.
I have checked that the label definition list model has a label definition, which it does.
In other words, nothing seems to change regarding the actual labels. I'm at a loss to explain why setting a definition expression should affect the display of the label.

Any ideas?

Cheers,
-Paul

[LucasDanzinger]

Hey Paul - 

It's unclear where, but somewhere in our parser we are not handling this expression properly. We will need to look into it, but it does indeed look like a bug in our API.

Thanks,

Lucas

[LucasDanzinger]

Looks like the issue is specifically with "sftrftime" - It is not SQL92 standard. Our platform has various SQL parsers, some which can and cannot handle this (sqlite, for example, does support this command). We will need to shore up the differences somehow. 

SQL reference for query expressions used in ArcGIS—ArcGIS Pro | Documentation 

[Anonymous User · Accepted solution]

Thanks Luke.

Knowing that 'strftime' was the issue, I went back and relooked at creating the query string.

Essentially I was manipulating the field data before comparing it. So instead I left that as is and converted the comparison date to the same format. The field data is stored as a Julian Date, so I googled a function to convert a javascript date, injected that into the query and now I have it functional.

The convert to Julian function I took from here:
Calculating julian date with javascript - Stack Overflow 

function toJulianDate(date) {  var floor = Math.floor;  var y = date.getUTCFullYear();  var m = date.getUTCMonth() + 1;  var d = date.getUTCDate() + (date % 8.64e7) / 8.64e7;     if (m < 3) {    y -= 1;    m += 12;  }     var a = floor(y/100);  var b = date < new Date(Date.UTC(1582,9,15))? 0 : 2 - a + floor(a/4);  var c = floor(y < 0? 365.25 * y - 0.75 : 365.25 * y);  var e = floor(30.6001 * (m + 1));     return b + c + d + e + 1720994.5;}

and my SQL query is now:

`proposedRevisitDate >= ${toJulianDate(plannedRevisitDateFrom)}`

cheers,

-Paul