Invalid timestamp when loading data into Amazon Redshift

Learn how to resolve an invalid timestamp error when loading data into AWS Redshift.

Written by Adam Pavlacka

Last published at: May 31st, 2022

Problem

When you use a spark-redshift write operation to save timestamp data to Amazon Redshift, the following error can occur if that timestamp data includes timezone information.

Error (code 1206) while loading data into Redshift: "Invalid timestamp format or value [YYYY-MM-DD HH24:MI:SSOF]"

Cause

The Redshift table is using the Timestamp data type that doesn’t store timezone information.

Solution

Include the option .options("extracopyoptions", "TIMEFORMAT 'auto'") as shown in the following Scala code:

%scala

df.write
.format("com.databricks.spark.redshift")
.options(...)
.options("extracopyoptions", "TIMEFORMAT 'auto'")
.mode("append")
.save()

If you specify auto as the argument for the DATEFORMAT or TIMEFORMAT parameter, Amazon Redshift automatically recognizes and converts the date format or time format in your source data.