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 methods and GetDSBySQL.

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

Returned Dataset Column Naming Conventions

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 configured default time zone of the MES system (the def_tz_id column of the Db_Status database table). The system default time zone can be set using the post-install Configurator on the Production tab of the MES DB/MW Communication component. For more information, see the MES Installation Guide or help.
• column_name: Holds the event's date and time in the local time of the client application that is calling the event. When returned in response to a call, the middleware converts the UTC time to the client local time 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

Datetimes Provided as Input Parameters

When datetimes are provided as input parameters, the UTC and local times are determined as follows:

• If both UTC and local datetime values are provided in the same method call, the UTC value takes precedence over the local time value. (The UTC value is used to calculate the local time, and the supplied local time is overwritten with the calculated value.)
• If a UTC value is provided in the call:
  • And the transaction is tied to an entity that is associated with a time zone via its site assignment (i.e., the entity's time zone corresponds to the site's region ID), then the local datetime value is determined from the UTC value based on the entity's time zone.
  • And the transaction is tied to an entity that is not associated with a time zone, then the local datetime value is determined from the UTC based on the default time zone of the MES system (from the def_tz_id column in the Db_Status table).
  • And the transaction is not tied to an entity, then the local datetime value is determined from the UTC based on the default time zone of the MES system.
• If only a local datetime value is supplied in the call and the transaction is tied to an entity that is associated with a time zone, then the UTC value is determined from the local time of the entity. Otherwise, the UTC is determined based on the default time zone of the MES system.

When Using DirectAccess Methods or the GetDSBySQL Method

When calling custom stored procedures with the DirectAccess methods or querying custom tables with the GetDSBySQL method, 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 time and will convert it to the client’s local time before returning it to the caller.

Item and Inventory Expiry datetimes

The column names for item and inventory expiry datetimes in returned datasets follow the naming convention described above.

However, item and inventory expiry datetimes as input parameters use the following conventions:

• If UTC is appended to the input parameter name (e.g., expiryDateUtc), then the system assumes that the date and time being provided is in UTC.
• If UTC is not appended to the input parameter name (e.g., newExpiryDate), then the system assumes that the date and time being provided is in the local time of the applicable storage entity.

In addition, lot and sublot expiry datetimes are fixed date and times. That is, they don't refer to local or UTC times. They are also not currently used by the system.

Datetime Columns with No UTC or Local Time Distinction

Some database tables have datetime columns that do not distinguish between UTC and local time (e.g., ver_date in the Bom_Ver table and expiry in the Cert_User_Link table). For these, the datetime values are considered to be in UTC.

last_edit_at Field in UTC

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