topic Label in SQL calculate time Now + 12 hours in ArcGIS Pro Questions

Label in SQL calculate time Now + 12 hours

NicolasSoenens — Mon, 05 Jun 2023 08:41:10 GMT

Hi,

I need a label in SQL-expression with variable Time:

Label must be visible if:

start time > time now
Start time < time now + 12 hours

I have a fault with this:

start > CurrentTime() AND start < DateAdd(CurrentTimestamp(), 12, 'hours')

Re: Label in SQL calculate time Now + 12 hours

DuncanHornby — Tue, 06 Jun 2023 15:34:56 GMT

With my test data written to in-memory:

I can use the following SQL query to limit the data:

myDates > timestamp '2023-06-06 16:00:00' And myDates < timestamp '2023-06-07 04:00:00'

What expression you settle on is as I understand dictated by the underlying database you are storing your data within.

Re: Label in SQL calculate time Now + 12 hours

NicolasSoenens — Wed, 07 Jun 2023 07:53:54 GMT

Thanks for your answer but I need an expression where the 'time' is variable according to current time, like TIMSTAMP().
Goal:
I'm making a layer for a fire brigade dispatch that contains specific traffic obstacles that are not equal to those of the citizen (Wase is therefore not usable).
My objects are variable according to start and, end and obstacle type.

I have 2 layers:
- Operational layer of current traffic disruption according to the 3 variables.
This is finished and works. 😉 At any time, a dispatcher can enter traffic disruption, including disruption that will occur in the future. Only the current nuisance is visible on the operational layer.

- A layer with the traffic disruption that "will" start between current time and 12 hours later.
It is for that last layer that I ask this question. At any time of the day, TIMESTAMP() must be taken into account and not a pre-entered time.

So I'm looking for how to write the following for my labels:
TIMESTAMP() + 12 hours

Re: Label in SQL calculate time Now + 12 hours

DuncanHornby — Wed, 07 Jun 2023 09:07:01 GMT

So what is your underlying data store? A File GeoDatabase, Oracle, or even a shapefile? I've always understood the SQL used by say a File GeoDatabase is some variant of the full capability of the SQL language and what you are asking may not be even possible.

You could try and explore using Arcade as away of bringing in the logic you require to disable/enable labelling. That has a great range of date manipulating functions. It seems to me the SQL query component is for filtering out data but you could probably return "nothing" as a label which would be effectively the same outcome?

Re: Label in SQL calculate time Now + 12 hours

NicolasSoenens — Wed, 07 Jun 2023 10:15:59 GMT

I'm currently starting from a hosted feature layer but the aim would be to get it on DB. (currently our server has a bug but Esri is working on it).

I fear that my question cannot be resolved at the moment.
Labeling is only in SQL and not in Arcade.
An additional problem with Arcade is that it does not take UTM and time zones into account.

https://community.esri.com/t5/arcgis-pro-questions/arcade-on-calculate-field-does-not-respect-the/m-p/1057384#M40868

Once my data is on DB it will be easier to write a script from there to perform the correct calculations.

I was still hoping to be dumber than the rest and get a solution from a GIS genius. :-).

Thank you for thinking along.

Re: Label in SQL calculate time Now + 12 hours

NicolasSoenens — Wed, 07 Jun 2023 10:32:49 GMT

I just recieved an answer from ESRI: It's a Bug in ArGIS Pro:

I will try the solution they propose:

As for the workaround you applied to 'now()', it seems that this is a bug in ArcGIS Pro. In the bug I found some information that might be interesting to possibly work around the problem:

1) A first workaround (in this case for calculating fields in an attribute table) that is suggested is the following:
Use Calculate Field with Python 3 as Expression Type and with Date functions, such as datetime.datetime.now() to populate the local current time:

Right-click the Date Field > Calculate Field.
Expression: Python 3.
Click the Filter icon next to Helpers > Date. Choose any compatible Helpers (Example: datetime.datetime.now())
Click Apply.