MES and datetimes

The MES middleware has special logic for managing date times depending on the column naming convention. This logic will also be applied to any custom stored procedures called by the DirectAccess and MiddlewareAccess methods.

The MES middleware processes datasets to convert datetimes to client local time.

Dataset columns that are datetimes have the following naming convention:

• column_name_utc: Holds the event's date and time in UTC.
• column_name_local: Holds the event's date and time typically in the local time of the entity at which the event will occur or has occurred. If not in the local time of the entity, the datetime is in the local time of the MES middleware that is handling the call.
• column_name: Holds the event's date and time in the local time of the client application that is calling the event if a valid time zone ID is supplied in the header or URL. Otherwise, the datetime defaults to UTC with "Z" in it. When returned in response to a call that provides the time zone ID, the middleware converts the UTC time to the supplied time zone and returns that time in this column. This column is never stored in the MES database. It is always calculated as needed from the UTC time.

MES core stored procedures typically return all three datetime columns for an event. For example, when calling sp_SA_Sample, it returns the following columns with the sample's requested time:

• requested_time_utc, as stored in the database
• requested_time_local, which is local to the entity at which the sample is being taken, as stored in the database
• requested_time, which is the requested_time_utc value converted to the local time of the calling client application

When datetimes are provided as input parameters, they are assumed to be in UTC.

When calling custom stored procedures with the DirectAccess or MiddlewareAccess methods, datetime columns should follow the naming convention of appending either _utc or _local so that the MES middleware does not automatically convert the values. If a custom stored procedure or table provides a datetime field without _utc or _local appended, then the MES middleware will treat the value as a UTC date and will convert it to the client’s local time before returning it to the caller.

last_edit_at Field

The last_edit_at field, whether used as an input parameter or when returned in a dataset, is always in UTC. 

datetime Input Parameters Listed as Strings

Input parameters that are stored as datetimes in the database are listed as strings in an endpoint's Parameters table in this help. This is because the date and time are entered as a string when included in the endpoint's URL.

When entering a datetime input parameter, make sure that it is in the ISO 8601datetime format, with time specified in 24-hour format.

UTC: yyyy-mm-ddThh:mm:ss:sssZ (e.g., 2020-08-24T19:12:58Z, milliseconds not included)

Local time: yyyy-mm-ddThh:mm:ss:sss±hh:mm (e.g., 2020-08-24T19:12:58+05:00, milliseconds not included)