Redshift ISO 8601 datetime with nanoseconds conversion error

This behavior is not a bug in Amazon Redshift, but rather a limitation of the TIMESTAMPTZ data type's precision.

TIMESTAMPTZ columns in Redshift store values with up to a maximum of six digits of precision for fractional seconds (microseconds). When you try to insert a timestamp with nanosecond precision (9 digits after the decimal point), Redshift attempts to convert and store it within its supported precision.

In your first example, '2025-01-08T09:59:19.750218312Z' is successfully inserted because it can be rounded to fit within the six-digit precision without causing an overflow.

However, in the second example, '2025-01-08T09:58:20.999999886Z' causes an error because rounding this value to microsecond precision would result in '2025-01-08T09:58:21.000000Z', which is essentially the next second. This overflow condition triggers the "date/time field value out of range" error.

To work around this limitation, you could either:

1. Truncate or round your timestamps to microsecond precision before inserting them into Redshift.
2. Use a different data type or storage method if you absolutely need nanosecond precision.

It's important to note that when working with high-precision timestamps in Redshift, you should be aware of these limitations and handle potential rounding or truncation appropriately in your data processing pipeline.
Sources
Datetime types - Amazon Redshift
Data types - Amazon Redshift