I am having issues loading a json file (date fields) in my S3 bucket into Redshift via the copy command. The three columns that are problematic look like this...
{
...
"date":20201209,
"dateChecked": "2020-12-09T24:00:00Z",
"lastModified": "2020-12-09T24:00:00Z",
...
}
The DDL looks like...
create table table1 (
...,
date date,
dateChecked timestamptz,
lastModified timestamptz,
...
);
and the copy command is ...
COPY {schema}.{table}
FROM 's3://{s3_bucket}/{s3_prefix}'
with credentials
'aws_access_key_id={access_key};aws_secret_access_key={secret_key}'
DATEFORMAT 'YYYYMMDD'
TIMEFORMAT 'YYYY-MM-DDTHH24:MM:SSZ'
JSON 'auto ignorecase';
The way I understood it was that the DATEFORMAT would parse the date column and the TIMEFORMAT would handle the timestamptz columns. I am only getting an error on the timestamptz columns, specifically "Invalid timestamp format or value [YYYY-MM-DDTHH24:MI:SSZ]".
Now I have also tried defining the dateChecked and lastModified columns as dates and using the DATEFORMAT 'YYYY-MM-DD' in the copy statement without the TIMEFORMAT. This works for those two columns but then the "date" column is null.
I've also tried every combination of ACCEPTANYDATE, DATEFORMAT 'auto', TIMEFORMAT 'auto' commands, which returns null for all three columns.
My question is how do I load data with different date/time formats?
Any assistance would be greatly appreciated. Thank you.