Date & Timestamp Data Types in Datamarts

Datamart Migrations and Loads support the Exasol data types of TIMESTAMP and DATE. In order to use these Exasol data types via Datamarts, the format metadata on the datamart must be set to date-time The Exasol data type of TIMESTAMP will be used if the dateFormat field is specified in addition to the date-time metadata field.

For example:

{
    "tables": [
        {
            "id": "Datamart-Dates-Test",
            "properties": {
                "exasolDate": {
                    "type": "string",
                    "format": "date-time",
                },
                "exasolTimestamp": {
                    "type": "string",
                    "format": "date-time",
                    "dateFormat": "YYYY-MM-DD HH24:MI:SS.FF3"
                },
            },
            "propertiesOrder": [
                "exasolDate",
                "exasolTimestamp",
            ]
        }
    ],
    "views": []
}

Format Models

From this example, the exasolDate column within the datamart schema will be of type DATE in Exasol, while the exasolTimestamp column will be of type TIMESTAMP within Exasol. An important note to and general rule to follow, is that the value for the dateFormat field should exactly match format at the data at rest which is loaded into Exasol. The value of dateFormat should follow the Exasol Format Models Documentation

For example, if I have a dataset I wish to load to the above example datamart, which has data at rest that looks like: 2022-03-08 23.56.59, the dateFormat should match this data. Which would be: "dateFormat" : "YYYY-MM-DD HH24.MI.SS"

Note

It should be noted that our current version of Exasol does NOT allow timestamps with respect to UTC such as: 2019-11-05T00:00:00.000Z. This format may be supported in the future.

Converting legacy datamarts

To use Date and Timestamp Exasol data types on legacy datamarts, the schema should be changed to follow the above format. And then subsequently migrated with the Drop All or Drop Others selection box ticked. This will re-create the table in Exasol with the appropriate data types specified for each column in the table.