Hi folks, when transforming the timestamp data, th...
# troubleshooting
s
Hi folks, when transforming the timestamp data, there are some data that doesn't make sense for example the date became `+200000-07-09 000000:00`is there a way to make the data null only for the broken date time? I want it to be
2025-05-02
only. Below is my schema and table configuration Schema:
Copy code
"enableColumnBasedNullHandling": true,
"dateTimeFieldSpecs": [
{
      "name": "datepromised",
      "dataType": "TIMESTAMP",
      "format": "TIMESTAMP",
      "granularity": "1:DAYS"
}
]
Table:
Copy code
"ingestionConfig": {
    "transformConfigs": [
      {
        "columnName": "datepromised",
        // "transformFunction": "FromDateTime(SUBSTR(JSONPATHSTRING(_airbyte_data, '$.datepromised'), 0, 10), 'yyyy-MM-dd')"
        "transformFunction": "FromDateTime(SUBSTR(REPLACE(JSONPATHSTRING(_airbyte_data, '$.datepromised'), '+', ''), 0, 10), 'yyyy-MM-dd')"
      }
    ]
}
x
maybe convert it to a number and see if this number is with in a reasonable timestamp epoch range
e.g. something like
CASE WHEN FromDateTime(SUBSTR(JSONPATHSTRING(_airbyte_data, '$.datepromised'), 0, 10), 'yyyy-MM-dd') BETWEEN 0 AND xxxxxxx THEN  FromDateTime(SUBSTR(JSONPATHSTRING(_airbyte_data, '$.datepromised'), 0, 10), 'yyyy-MM-dd') ELSE null
the syntax might not be correct, the logic should follow
s
Thank you for your info, this is my final syntax, because pinot doesn't support BETWEEN`"transformFunction": "CASE WHEN REGEXP_LIKE(substr(jsonPathString(_airbyte_data, '$.datepromised'), 0, 10), '^\\\\d{4}-\\\\d{2}-\\\\d{2}$') THEN fromDateTime(substr(jsonPathString(_airbyte_data, '$.datepromised'), 0, 10), 'yyyy-MM-dd') ELSE null END"`