Representation of ESQL datetime data types
When your application sends a message to an integration node, the way in which the message data is interpreted depends on the content of the message itself and the configuration of the message flow. If your application sends a message to be interpreted either by the generic XML parser, or the MRM parser, that is tailored by an XML physical format, the application can include date or time data that is represented by any of the XML Schema primitive datetime data types.
The XML Schema data type of each piece of data is converted to an ESQL data type, and the element that is created in the logical message tree is of the converted type. If the datetime data in an input message does not match the rules of the chosen schema data type, the values that the parser writes to the logical message tree are modified even if the message is in the MRM domain and you have configured the message flow to validate the input message. (Validation is not available for generic XML messages.)
This has the following effect on the subfields of the input datetime data:
- If any of the subfields of the input message are missing, a default value is written to the logical message tree. This default is substituted from the full timestamp that refers to the beginning of the current epoch: 1970-01-01 00:00:00.
- If the input message contains information for subfields that are not present in the schema, the additional data is discarded. If this occurs, no exception is raised, even if a message in the MRM domain is validated.
- After the data is parsed, it is cast to one of three ESQL datetime
data types. These are DATE, TIME, and TIMESTAMP.
- If a datetime value contains only date subfields, it is cast to an ESQL DATE.
- If a datetime value contains only time subfields, it is cast to an ESQL TIME.
- If a datetime value contains both date and time subfields, it is cast to an ESQL TIMESTAMP.
The following examples illustrate these points.
| Input data XML Schema data type | Schema rules | Input value in the bit stream | Value written to the logical tree (ESQL data type in brackets) |
|---|---|---|---|
| xsd:dateTime | CCYY-MM-DDThh:mm:ss | 2002-12-31T23:59:59 | 2002-12-31 23:59:59 (TIMESTAMP) |
| --24 | 1970-01-24 (DATE) | ||
| 23:59:59 | 23:59:59 (TIME) | ||
| xsd:date | CCYY-MM-DD | 2002-12-31 | 2002-12-31 (DATE) |
| 2002-12-31T23:59:59 | 2002-12-31 (DATE) | ||
| -06-24 | 1970-06-24 (DATE) | ||
| xsd:time | hh:mm:ss | 14:15:16 | 14:15:16 (TIME) |
| xsd:gDay | ---DD | ---24 | 1970-01-24 (DATE) |
| xsd:gMonth | --MM | --12 | 1970-12-01 (DATE) |
| xsd:gMonthDay | --MM-DD | --12-31 | 1970-12-31 (DATE) |
| xsd:gYear | CCYY | 2002 | 2002-01-01 (DATE) |
| xsd:gYearMonth | CCYY-MM | 2002-12 | 2002-12-01 (DATE) |
Validation with missing subfields
When you consider which schema datetime data type to use, consider that, if the message is in the MRM domain, and you configure the message flow to validate messages, missing subfields can cause validation exceptions.
The schema data types Gday, gMonth, gMonthDay, gYear, and gYearMonth are used to record particular recurring periods of time. There is potential confusion when validation is turned on, because the recurring periods of time that are used in these schema data types are stored by ESQL as specific points in time.
For example, when the 24th of the month, which is a gDay (a monthly day) type, is written to the logical tree, the missing month and year subfields are supplied from the epoch (January 1970) to provide the specific date 1970-01-24. If you code ESQL to manipulate this date, for example by adding an interval of 10 days, and then generate an output message that is validated, an exception is raised. This is because the result of the calculation is 1970-02-03 which is invalid because the month subfield of the date no longer matches the epoch date.