topic Re: Converting between Date Formats in ArcGIS GeoEvent Server Questions

Converting between Date Formats

SimonJackson — Fri, 10 Dec 2021 22:52:01 GMT

I have an incoming feed which has a LastSampleTime.

This comes into GEP and by default creates a string field in the GeoEvent Defintion.

1. Incoming Date as a String Field:
"25/06/2014 12:45:00"

If I alter the LastSampleTime in the GeoEvent Definition to be a date:

2. Incoming Date as a Date Field:
'06/25/2014 12:00:00 AM'
- It makes all the times to be 12:00:00 AM, which is incorrect as the times vary on the incoming stream.

I have a feature service hosted on ArcGIS Online, and I have the rest of the feed readings updating the relevant fields on this service.
As it is ArcGIS Online, I have no control over the underlying DBMS in regards to any settings around date formats.

For the date field, if I pass the date as a date (#2 above), then this gets written to the feature fine. However, unless I can find a way to ensure the time is correct, this is not going to be an option.

If I try and pass the date as a string, then it won't work. This makes sense, as if I try and do an ApplyEdits using the string directly against the REST endpoint, then it fails:

Fails:

This is using the same date (string) that I am getting off the feed:

{
    "attributes" : {
      "OBJECTID" : 1,
      "LastSampleTime" : '25/06/2014 12:45:00'
    }
}

Error: 1000, Description: Conversion failed when converting date and/or time from character string.

What does work, is if it convert the date into a different format:

Successful:

{
    "attributes" : {
      "OBJECTID" : 1,
      "LastSampleTime" : '2014-06-25 12:45:00'
    }
}

= Edits applied ok!

I was pondering having a play with a regular expression to try and convert the original date into a date string that works, but sounds like the wrong way to do it. I dont think there are any function expressions that convert dates into different formats.

Please, some advice on how to go about getting the correct date & timestamp from the incoming feed into GEP would really help me out.

Re: Converting between Date Formats

RJSunderman — Sun, 12 Dec 2021 15:59:34 GMT

Hello Simon -

Generally speaking the out-of-the-box processors and adapters make a good faith attempt to convert data not presented in the ISO 8601 format, which looks like a date/time value, into a Date type object ... when the GeoEvent Definition specifies that a particular event field should be interpreted as a Date.

Because the yahoo feed you referenced is passing its LastSampleTime as a quoted value, GeoEvent Processor will assume that the event field should be treated as a String ... so your initial assumption to modify the generated event definition to handle the LastSampleTime field as a Date type was correct.

But there's one more step you have to take. Since the date/time is being supplied in an "international" format with the days value coming before the month value. In the inbound connector's configuration ... I was using a 'Poll an external website for JSON' input ... you need to specify a date/time mask.

Expand the connector's Advanced properties and enter the following for the 'Expected Date Format': dd/MM/yyyy hh:mm:ss.

This tells GEP to expect that any field of type Date should be parsed as days, months, years, hours, minutes, seconds with literal '/' between the date values and literal ':' between the time values -- rather than the American custom of months, days, years, hours, minutes, seconds.

Now, because the provided date/time string does not include any time zone information, the GEP will assume that the received date/time is in the same time zone as the server processing the data. On my machine here in California the feed's provided value "30/06/2014 11:45:00" was parsed as June 30th 2014 at 11:45:00 AM, which is equivalent to Mon, 30 Jun 2014 18:45:00 GMT. When I push the GeoEvents into a GeoEvent cache and request a JSON representation of the event data, this is what I get:

{
    "Site": "210135-Brickmans Bdg",
    "LastSampleTime": 1404153900000,
    "RiverLevel": "1.123",
    "FlowRate": "5.3",
    "RainfallIntensity": " ",
    "WaterTemp": " ",
    "Conductivity": " "
}

The long integer, of course, is the GMT value of the date/time represented as milliseconds since the epoch ... consistent with the Esri Feature Services REST API.

As you know, the out-of-the-box text adapter can create a text representation of this value, but it will automatically shift the value to your server's current time zone (e.g. 11:45:00 AM). If you need to, you can use a Field Calculator to add/subtract a number of milliseconds to shift the GeoEvent's UTC value from Zulu/GMT to Lima/Localtime, then use a Field Mapper to create a String representation of the value: "Mon Jun 30 11:45:00 PDT 2014". From there, if you really need report dd/mm/yyyy hh:mm:ss ... you'll have to use string functions to manipulate the string representation of the date/time.

Hope this information helps -
RJ

Re: Converting between Date Formats

SimonJackson — Mon, 30 Jun 2014 12:40:54 GMT

RJ - really appreciate you taking the time to provide such detailed answers, really helps me (and I imagine others who may stumble upon this thread) get a better understanding on how to configure GeoEvent services.

Not had time to apply the above yet, but it makes perfect sense, was unaware there was expected date format settings for the Poll URL for JSON input connector.

Thanks again.

Re: Converting between Date Formats

SindreEngh — Wed, 15 Oct 2014 09:39:11 GMT

Hey.

I have a similar problems with GEP and dates.

I get the date and time in to different fields, and uses a Field Calculator to combine it in a string field, but when I try to update my feature class (using a field mapper between the calculated new date string and my FC date filed) the date field is not updated. My format is yyyy-mm-dd hh:mm:ss, I've also tried dd/mm/yyyy hh:mm:ss, but non is working.

And aslo, when I'm using GeoNet it seems like all replies from Esri Staff disappears?!? This is really frustrating, and means that 90% of the solutions of threads are gone for me, I can't se any replies from "RJ" in any threads.

Best regards

Sindre Engh

Geodata Norway

Re: Converting between Date Formats

ThomasPaschke — Mon, 20 Oct 2014 22:40:18 GMT

Hi Sindre,

we are aware of the issue that all posts of RJ got lost while migrating from the old forum to GeoNet and working on a fix to recover his posts.

Regarding your problem: Please try to use the ISO 8601 format (http://www.w3.org/TR/NOTE-datetime), which is for your case YYYY-MM-DDThh:mm:ss.

We make a good afford to convert different date/time formats received by the input text adapter into the Epoch format (long) in order to use them for further GeoEvent processing. However, if you want to convert a string into a date field after it past the input text adapter (e.g. using a processor like the field mapper) only the very strict time formats of ISO 8601 are supported.

I hope this helps, let me know if you need any further assistance.

Best,

Thomas

Re: Converting between Date Formats

SindreEngh — Tue, 21 Oct 2014 11:41:33 GMT

Hi Thomas.

Thanks for the detailed reply!

I found out that the format YYYY-MM-DDThh:mm:ssZ did work, so adding a 'T' between the date and time and a 'Z' at the end of the time did the trick. My Field Calculatoer ended up looking like this:

'20' + substring(date,4,6)+ '-' + substring(date,2,4) + '-' + substring(date,0,2) + 'T' + substring(UTC_time,0,2) + ':' + substring(UTC_time,2,4) + ':' + substring(UTC_time,4,6)+'Z'

Date = "DDMMYY"

UTC_time = ""hhmmss"

Sindre

Re: Converting between Date Formats

Ctal_GISsquad — Wed, 24 Feb 2021 18:34:30 GMT

Hi @RJSunderman / @ThomasPaschke I have been encountering a slightly different issue. A couple of fields in my CSV file only have 'date' values in them, no time i.e. in MM/DD/YYYY format. However, The geoevent processor brings them in as 'MM/DD/YYYY 4 PM' format in the feature service. I guess the default 4 PM gets added because of the UTC conversion. How can I remove the aforementioned 4 PM from my values?

Re: Converting between Date Formats

RJSunderman — Sat, 27 Feb 2021 00:50:02 GMT

If data you are receiving contains only a date value (e.g. 12/31/2021 ) without a time, this is not a pattern GeoEvent Server recognizes without you specifying a an Expected Date Format the inbound adapter can use to figure out how to parse a String as a Date. You would have to specify a value like MM/dd/yyyy when configuring your inbound connector. The connector will apply this pattern to all event record attributes whose data type is Date in the GeoEvent Definition used by the inbound connector.

When I send the String value "12/31/2021" to my GeoEvent Server with the Expected Date Format configuration described above, the Date value my inbound adapter constructs for me from the received string is 1640937600000. This is an epoch value used by Java to represent date/time values. GeoEvent Server uses millisecond epoch values, which is why the value has 13 digits rather than only 10.

If I ask GeoEvent Sever to cast its Date to a String I get a representation of the date which looks like "Fri Dec 31 00:00:00 PST 2021". Notice that the string has both a "date" and a "time" and includes the Time Zone for the expressed date/time value. In this case, the Date is expressed in the Pacific time zone. This is because an Expected Date Format pattern was specified -- which is required to handle the inbound string which does not match one of the few built-in expected patterns for a date/time value. The time zone handling is important to note because, in this case, the date/time is not in UTC units. GeoEvent Server assumes that the non-standard date/time must be a date/time local to my solution, so it uses the locale of my server (whose clock is configured to use the Pacific Time Zone).

Focusing on your question, if you are receiving a string value which is somehow being adapted to produce the epoch date value 1640908800000 (which could also be represented as "Thursday, December 30, 2021 4:00:00 PM GMT-08:00" or "Thu Dec 30 16:00:00 PST 2021") and you need to truncate the value to be simply "Thursday, December 30" ... you have a couple of options.

I strongly recommend you make sure you understand how the received data is actually being adapted, and check to verify how client applications are representing the value in web map pop-ups or web forms. A client application will likely represent a Java Epoch date/time value it receives, when querying a feature service for feature records for example, in whatever time zone the client web application is running. The value 1640908800000 already represents the date/time "Friday, December 31, 2021 12:00:00 AM" when a UTC value is assumed and web clients are likely going to try and represent an assumed UTC date/time in whatever time zone the web application is running.

If you were to add or subtract some number of milliseconds from the epoch to drop the "time" portion and keep only the whole "date" value, your effort is likely going to have unintended consequences client-side.

You could use a RegEx pattern match on a value toString(myDate) to isolate the whole hours portion of the "time" and then multiply this by 3,600,000 (which is 60 min x 60 sec x 1000 ms) and then subtract that from your Date using a Field Calculator. The eventual expression would be something like:

myDate - (16 * 3600 * 1000)

This assumes you are able to extract the value "16" from a string "Thu Dec 30 16:00:00 PST 2021" to know that you wanted to subtract 16 hours worth of milliseconds from the myDate attribute value.

You also might want to look at some of the supported expressions for the Field Calculator processor. The function currentOffsetUTC() specifically computes the millisecond difference between your GeoEvent Server's locale and UTC. Since my server is configured to use the Pacific Time Zone, which is currently -08:00 hours behind UTC, the currentOffsetUTC() function returns a value -28800000, which is (8 hours x 60 minutes x 60 seconds x 1000 milliseconds). You might scale the computed value by some constant when performing date/time adjustment arithmetic, or more likely, shift an epoch Date from an assumed local time zone so that the value represents a UTC value.

The advantage of using currentOffsetUTC() is that the function automatically recognizes changes in daylight savings, so you don't have to rely memory to update GeoEvent Services twice a year when a fixed constant value you might have hard-coded in an expression no longer reflects the observance of daylight savings time.