How to Insert TIMESTAMP Column into Redshift

Question

I created a table in Redshift:

create table myTable (
       dateTime TIMESTAMP NOT NULL,
       ...
);

However, when I try to insert a record that contains a dateTime of, I get an error from stl_load_errors.

20080215 04:05:06.789

Since I took this timestamp from the docs, I would've expected it to have worked.

The error logs from Redshift show:

Invalid timestamp format or value [YYYY-MM-DD HH24:MI:SS]

However, I'd like to include 3 extra seconds, example: 2015-02-01 15:49:35.123.

How do I need to modify my timestamp field to insert it with the extra precision on seconds?

Answer 1

TL;DR - When importing into Redshift from an S3 file force the imported data to have the default time format of 'YYYY-MM-DD HH:MI:SS'that Redshift expects in order to get a precision past seconds, otherwise it will be truncated.

I ran into this same issue while trying to upload to pull in from S3. My original JSON has a timestamp like this. { "updated_at" : "2014-12-08T21:14:49.351638" }. However when I went to pull it into Redshift I needed to set the format, which included the T before the time.

COPY schema.temp_table FROM 's3://s3-bucket/file-name'
    WITH CREDENTIALS 'aws_access_key_id=access-key;aws_secret_access_key=secret-key'
    format as json 'auto'
    timeformat 'YYYY-MM-DDTHH:MI:SS';

This imported everything, however the time was always truncated to seconds, so I would end up with 2014-12-08 21:14:49 in Redshift.

The documentation looks like this should import with precision out to 6 places, but this was not the case.

I decided to try out the default format 'YYYY-MM-DD HH:MI:SS' for importing to Redshift so I had to change my Postgres database to export the JSON for date fields in the correct format to_char(updated_at, 'YYYY-MM-DD HH24:MI:SS.SSSSS') as updated_at.

After making this change the new JSON exported as { "updated_at" : "2014-12-08 21:14:49.351638" } and I set the timeformat for the import into Redshift as the default format as json 'auto' timeformat 'YYYY-MM-DD HH:MI:SS';

By making this change to use the default timeformat Redshift now imported the timestamps with the correct precision!

Answer 2

timeformat 'auto' and dateformat 'auto' worked well on my format, 2017-11-02T21:04:03.108Z. Documentation at http://docs.aws.amazon.com/redshift/latest/dg/automatic-recognition.html