CAST <value> as TIMESTAMP should accept more timestamp formats. In particular it should accept valid ISO 8601 timestamps.
For example all of the following ISO 8601 timestamps should work:
select cast('2023-06-30T02:26:49' as timestamp);
select cast('2023-06-30T02:26:49.123' as timestamp);
select cast('2023-06-30T02:26:49.123456' as timestamp);
select cast('2023-06-30T02:26:49.123Z' as timestamp);
select cast('2023-06-30T02:26:49.123UTC' as timestamp);
select cast('2023-06-30T02:26:49−07:00' as timestamp);
select cast('2023-06-30T02:26:49.123−07:00' as timestamp);
select cast('2023-06-30T02:26:49.123456−07:00' as timestamp);
I guess there is a question over whether it should fail for microseconds or truncate to millis. It would also be nice if Dremio had additional types to support microsecond and nanosecond timestamps as these are supported in Apache Arrow.
I’m aware of the TO_TIMESTAMP function that can be used instead. This is a reasonable workaround, but it would be more convenient if CAST AS TIMESTAMP worked ‘out of the box’ on more of the common formats.
One problem I have is with microsecond format timestamps. There is no microsecond format specifier here which makes parsing them a massive pain with REGEXP and CASE statements etc…