Timestamps received from a sensor feed display differently in GeoEvent Sampler, ArcGIS REST Services queries, and ArcGIS Pro

[RJSunderman]

This post was developed by Esri staff with the purpose of modeling real customer questions and positioning them with answers in a way that complements our users’ search processes.

We use Verizon Connect to track vehicles in our van pool in Cheyenne Wyoming. The clocks on our vehicles are set to Mountain Standard Time (MST) which is -7 hours relative to UTC.

When I use the GeoEvent Sampler to look at data from my input I see the date and time in MST, which is what I want. The data also appears to be in MST when I look at output I’ve written to a CSV file. Here are a couple of screenshots of what I’m seeing:

When I look at feature records in the database the date and time look like they’ve been switched to an integer. When I open the feature record’s attribute table in ArcGIS Pro all of the values are offset 7 hours.

So, data coming into GeoEvent Server is MST but is somehow being changed when written to the database to switch it to UTC? Why is this happening and how do I fix it?

[RJSunderman · Accepted solution]

To answer your question we should first take a look at the raw data GeoEvent Server is receiving. The date/time values associated with sensor data are highly adaptable and can be represented many different ways. Your question mentions receiving data from Verizon Connect, so let’s look at some sample data from that sort of data feed.

The illustration above left shows a sample of raw JSON data typical of what Verizon Connect might send. Note the UpdateUTC data value is sent as a 13-digit epoch value in milliseconds. When a GeoEvent Server input receives data in this format and adapts it as a Date the epoch value is assumed to represent a Coordinated Universal Time (UTC) value, not a local time value such as Mountain Standard Time (MST). So to clarify a point in your question, data is not received as MST values. It is received as an epoch long integer value and adapted, first for processing as a Date data type, and later for display as a string.

The second illustration, above right, shows an online date/time utility I often use to convert epoch long integer values to human-readable string representations of a given date and time. Note that the strings constructed by the utility specify an offset from Greenwich Mean Time (GMT). GMT and UTC never change for Daylight Saving Time (DST) and are sometimes used interchangeably even though GMT is technically a time zone and UTC is a time standard.

A display string constructed from the epoch 1676597835000 can specify a time zone. For example, an application might construct a string to display a date and time as February 17th 1:37:15 AM (GMT) or display a local time February 16th 6:37:15 PM (MST). Note that when the epoch converter displays a local time value a time zone offset is included in the string.

Client applications generally determine how they want to construct and display string values representing a date and time. GeoEvent Server uses epoch long integer values for its Date values when processing data, and at times converts the Date into a human-readable string for display in the server’s local time zone.

To your original question, the UpdateUTC data value is not changed when written to the database. The Date value in a geodatabase feature record is always going to be an epoch long integer value consistent with the ArcGIS REST API. Verizon Connect sends the date/time of a vehicle position report as a UTC value, and that is how the value is being stored in the database.

Why a date/time value displayed by the GeoEvent Sampler appears different, for example, than the same value written out to a CSV file or a JSON file is a question of data adaptation and string construction. Rather than displaying the actual epoch long integer value of a Date (such as 1676597835000) GeoEvent Sampler constructs and displays a string representation of the Date using your server’s locale to determine an appropriate time zone. That is why you see the time zone MST included in the GeoEvent Sampler’s displayed string "Thu Feb 16 18:37:15 MST 2023".

When you configure a Write to a CSV File output you choose whether date/time values are written out as ISO 8601 formatted values or as strings in a custom format. The Write to a JSON File output, on the other hand, cannot be configured to write out a human-readable string. When reviewing the JSON output file in a text editor you will see the actual epoch long integer value of a Date.

I’ll include some examples in the comments below to illustrate how you might expect different client applications to construct display strings from epoch long integer values stored in a geodatabase feature record. But to answer the last part of your question, how do you fix what you are seeing, that depends entirely on what you are trying to do.

The epoch long integer underlying a Date cannot express a time zone or an offset from UTC the way a String value can. You could configure a Field Calculator or Field Mapper with an expression that either adds or subtracts a number of hours from a Date value, but I really do not recommend this. If you fudge a Date value to shift it from UTC into a local time zone you risk a client application downstream, possibly one outside the ArcGIS Enterprise, constructing a string from an epoch value it assumes is a UTC value and displaying a string which appears incorrect.

Suppose, for example, you are using an application like SQL Server Management Studio (SSMS). When the application retrieves a Date whose value you have explicitly offset, it will likely assume the value it retrieved is a UTC value, use your server’s locale to determine an appropriate time zone, and apply the temporal offset a second time. The Date value you explicitly offset by a number of hours as part of your event record processing in GeoEvent Server now appears incorrect when viewed using SSMS.

For the simple reason that a database server can be in any time zone, and web client applications that access the data may not be in the same time zone as the server, the recommended best practice is to keep the ArcGIS REST API default and allow feature services to maintain Date values as epoch long integer values in the assumed UTC standard. If you want to calculate and store a local representation of a Date value, calculate the value as a String. You can use the toString( ) function in a Field Mapper, for example, to do this.

A String value is a literal string and won’t ever be manipulated to change its value. This might be ideal for displaying attribute values in a web map pop-up, but you cannot use attributes of type String to configure something like the time slider in ArcGIS Pro. Instead of computing a String value, or adding/subtracting a number of milliseconds from a Date value, the best approach working with ArcGIS Pro would be to configure its feature layer properties to apply a time zone offset to Date values it retrieves from a geodatabase. As you zoom, pan, and potentially change a map’s temporal extent to see more or fewer features, the date and time strings displayed by ArcGIS Pro should reflect local time rather than a UTC time.

[RJSunderman]

Data from my sensor feed reports timestamp values in seconds rather than milliseconds. When these timestamps are converted to string values they display as dates in 1970. How do I fix this?

Some data feeds report date/time values as 10-digit epoch values measured in seconds. GeoEvent Server uses 13-digit millisecond values consistent with the ArcGIS REST API, so you have to multiply by 1000 to scale the value from seconds to milliseconds before writing it out to a geodatabase

To prevent an epoch such as 1676597835 from displaying as "Tue Jan 20 02:43:17 MST 1970":

• Allow your input to adapt the 10-digit epoch value as either a Date or a Long integer value
  
  
• Use a Field Calculator or Field Mapper to scale the ReportedDT attribute value from seconds to milliseconds
  
  
• Use a toDate( ) function to explicitly cast the arithmetic result from an implicit Long to a Date
  
  

Example:  toDate(ReportedDT * 1000)

[RJSunderman]

Data from my sensor feed reports the date and time of each data record in separate fields. How do I combine these into a single Date attribute?

Use a Field Calculator or Field Mapper to combine the date and time values into a string which GeoEvent Server can interpret as a Date. Your goal is to construct a single ISO 8601 formatted string that contains both the date and time values which you can then cast from String to Date.

For this example assume that ReportedDate and ReportedTime hold the values "02/16/2023" and "18:37:15" respectively. Assume that the date and time are reported as local values, so a dynamic UTC offset should be applied to avoid errors when clocks are adjusted for Daylight Savings twice each year.

The string you are calculating for this example should be:  2023-02-16T18:37:15-00:00

• Allow your input to adapt the ReportedDate and ReportedTime as separate String value
  
  
• Use substring( ) functions to slice values from these strings and append them to a new String
  
  
• Use a toDate( ) function to cast the constructed String value to a Date
  
  
• Use the currentOffsetUTC( ) function to offset the constructed Date from a local to a UTC standard value
  
  
• Write the result into a new attribute field whose data type is Date
  
  

Example:

The expression in the example presented above has been formatted within a code block for readability.
The expression should be entered as a single line of text into a Field Calculator or Field Mapper processor.

You can copy/paste the text shown  below:

toDate( substring( ReportedDate, 6, 10 ) + '-' + substring( ReportedDate, 0, 2 ) + '-' + substring( ReportedDate, 3, 5 ) + 'T' + substring( ReportedTime, 0, 2 ) + ':' + substring( ReportedTime, 3, 5 ) + ':' + substring( ReportedTime, 6, 8 ) + '-00:00' ) - currentOffsetUTC()

Each substring( ) function in the example slices a few characters out of the expression’s target field. For example, substring(ReportedDate, 6, 10) slices the four characters '2023' from the reported date '02/16/2023'.

The several + in the expression append substrings to one another. The '-' in the expression are literal dash characters. Your goal is to construct the string:  2023-02-16T18:37:15-00:00

The currentOffsetUTC( ) function uses your machine’s locale to determine a millisecond offset from local time to UTC taking current Daylight Savings adjustments into account. The example said to assume the date and time are reported as local values so we subtract the current UTC offset to shift the constructed Date to a UTC standard consistent with the string’s time zone designation (the '-00:00' we appended to the constructed ISO 8601 String). The UTC offset for regions in North America is negative as these time zones are several hours behind Greenwich Mean Time (GMT). Subtracting a negative value from the date/time effectively adds a number of hours to the value pushing it to a UTC standard time.

Appending a time zone designation '-00:00' is a better approach than hard-coding an offset such as '-07:00' into the constructed string. Whatever constant you choose as the offset for a local time zone is likely only accurate during certain months of the year. Adjusting the date/time by adding the dynamic UTC offset takes your server's current Daylight Savings observation into account for you.

Reference:  https://www.w3.org/TR/NOTE-datetime

[RJSunderman]

Data from my sensor feed reports timestamp values which look like the ISO 8601 format, but Date values converted to a string display with an offset hours ahead of the local time reported in the feed. Why is this happening?

Sometimes the date/time strings sent from a data provider appear to follow ISO 8601 formatting rules, but they are missing a time zone designator or the literal ‘T’ between the date and time values. To answer your question we should first confirm that the raw data being received actually follows the ISO 8601 standard.

For this example assume:
   a)  The sensor feed's specification indicates that ReportedDT is sent as a local time value
   b)  The ReportedDT string value "2023-02-10T11:45:00" is being received

The date/time value being sent by the data feed does not include a time zone designation. You can apply a dynamic UTC offset based on your server’s observed time zone as part of a Field Calculator or Field Mapper expression to fix this.

• Allow your input to adapt the ReportedDT string value as a Date
  
  
• Use currentOffsetUTC( ) to offset the adapted Date from its local value to a UTC standard value
  
  
• Cast  the result of the calculation to a Date (the arithmetic calculation returns a Long, not a Date)

Example:  toDate(ReportedDT - currentOffsetUTC())

The feed's value really ought to include a time zone designator to indicate in which time zone the date/time is being reported. It would be the feed’s responsibility to automatically adjust the time zone designation in data it sends when clocks change for Daylight Savings twice each year. If no offset were being applied the time zone designation should be '-00:00' to indicate the date/time is a UTC value.

The data provider’s specification, in this example, supposedly says that the date and time values are consistent with a local time zone. Applications which adapt an ISO 8601 formatted string which is missing a time zone designator are free to assume a time zone. GeoEvent Server assumes the date/time is a UTC value when adapting an ISO 8601 formatted string. The current UTC offset is therefore subtracted from the reported date/time to shift the value from what the feed specification says is a local time to the UTC standard assumed by GeoEvent Server. The value returned by the currentOffsetUTC( ) function is negative for areas in North America. Subtracting a negative value effectively adds a number of hours to the Date shifting it forward to a UTC standard time.

You might think to fix the ReportedDT value by adapting it as a String and then appending a time zone designator yourself to the string. If you do, twice each year when clocks are adjusted for Daylight Savings, the time zone designator you append to the received string would require update. So it is better to use the dynamic UTC offset provided by the currentOffsetUTC( ) function in this case.

Remember, Date values in a geodatabase are maintained as epoch long integer values which by convention are assumed to be UTC values. As a best practice you want to write UTC values to your geodatabase and allow client applications to determine how they want to construct a string representation of an assumed UTC value they retrieve as part of a feature record

Reference:  https://www.w3.org/TR/NOTE-datetime

[RJSunderman]

Does GeoEvent Server have a preferred string format for date and time values?  When is a value for the Expected Date Format parameter required and when can I leave it unspecified?

GeoEvent Server is able to adapt a variety of different string formats without relying on a custom string formatting pattern specified using the Expected Date Format parameter. The preferred format for date/time values is the ISO 8601 format with a time zone designator, but either of the following two string formats are preferable to other options:

• "2023-02-21T14:36:45-08:00"
• "Tue Feb 21 14:36:45 PST 2023"

The first formatted string is an example of the ISO 8601 standard.
The second is an example of the format Java uses when converting a Date to a String.

Notice that both of the examples above designate the date and time values are in the Pacific Time Zone. GeoEvent Server is able to adapt either string to produce a Date without relying on a specific Expected Date Format pattern. The underlying epoch used for event record processing and the long integer value written to a geodatabase as a feature record’s Date will be the same (1677019005000) regardless of the server locale or clock setting when adapting either formatted string.

GeoEvent Server inputs can adapt other string formats as Date values when a GeoEvent Definition specifies the event record attribute should be handled as a Date rather than a String. Each of the following formatted string values will also adapt successfully, without requiring an Expected Date Format pattern, but the underlying epoch will depend on the locale and clock setting of the server.

• "02/21/23 02:36:45 PM"
• "02/21/23 14:36:45"
• "02/21/2023 02:36:45 PM"
• "02/21/2023 14:36:45"

None of the four date/time strings above specify a time zone, so GeoEvent Server has to assume one when adapting the formatted strings and calculating a Date. The string values will be adapted as local date/time values. The epoch long integer value assigned to the constructed Date will be a UTC date/time offset from the server’s local time by the appropriate number of hours. This means that different servers in different time zones will compute different epoch values.

Consider the example below:

The epoch calculated by a server observing ‘Pacific Time’ will be +03:00 hours relative to the epoch calculated by a server that observes ‘Eastern Time’ even though both server machines presumably received and adapted the same formatted string from the same data feed. If the date/time string sent by the feed included a time zone designator GeoEvent Server would not presume to use the server’s observed time zone and the epoch computed by each server would be the same.

Timestamp values with only “hours” and “minutes”

GeoEvent Server inputs are able to adapt string timestamps from data feeds which include only “hours” and “minutes” in the formatted string value.

• "02/21/23 14:36"
• "02/21/2023 14:36"

The server’s calculated epoch will, of course, be 45 seconds earlier than the ‘Pacific Time’ epoch shown in the previous example (1677018960000 rather than 1677019005000) since the reported time does not include ‘seconds’.

Specifying an Expected Date Format pattern string

You only need to configure an input with an Expected Date Format pattern string if strings being received and adapted as Date values deviate from the examples above. An example frequently encountered includes date/time values on the European continent which specify the day before the month (e.g. 21/02/2023).

In this case you would need to specify a date format pattern dd/MM/yyyy hh:mm:ss for GeoEvent Server to use when parsing the date string. The pattern specified uses the standard Java conventions for date/time strings. The Java SimpleDateFormat Tester is on online utility you can use to explore Java’s conventions.

Example: Receiving a UTC time which does not include a time zone designator

Suppose you want an input to adapt the formatted date/time string "February 28, 2023 21:36:45" reported by a sensor feed. The feed’s specification indicates that timestamps on each record are UTC values, but looking at the data you recognize that the formatted string uses the proper name for the month rather than a numeric value, includes a comma, and does not include a time zone designator. You therefore configure your GeoEvent Server input with an Expected Date Format pattern  MMM dd, yyyy HH:mm:ss to instruct GeoEvent Server how the custom string format should be interpreted.

The feed's specification indicates that date/time values are reported as UTC values, so you should also use the currentOffsetUTC( ) function to add your server's current UTC offset to the adapted date/time.

Example:  toDate(ReportedDT + currentOffsetUTC()

The epoch you want to compute and assign to your geodatabase feature records is:  1677620205000.

In an earlier example I indicated that GeoEvent Server assumes a date/time expressed in the ISO 8601 format is a UTC value when adapting the string. In this case GeoEvent Server assumes the opposite. The custom date/time format "February 28, 2023 21:36:45" follows no particular standard, so GeoEvent Server assumes that it represents a local value.

The current UTC offset is added to the reported date/time in this case because the feed's specification said each record's timestamp is a UTC value and GeoEvent Server assumed the opposite. It checked the server's locale and applied an assumed offset based on the time zone observed by the server when adapting the formatted string as a Date. Time zones in North America are several hours behind Greenwich Mean Time (GMT) so GeoEvent Server would add some number of hours to push an assumed local value to a UTC standard value.

The value returned by the currentOffsetUTC( ) function in negative (in North America) so adding the value to the date/time effectively rolls back the offset applied when the data value was adapted.