Converting between Date Formats

9962
5
06-24-2014 09:21 PM
Highlighted
Occasional Contributor III
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.
Reply
0 Kudos
5 Replies
Highlighted
Esri Regular Contributor
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
Highlighted
Occasional Contributor III
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.
Reply
0 Kudos
Highlighted
New Contributor III

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

Reply
0 Kudos
Highlighted
New Contributor III

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

Reply
0 Kudos
Highlighted
New Contributor III

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