How to insert TIMESTAMP column in Redshift

Question

I created a table in Redshift:

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

However, when I try to insert a record containing dateTime , I get an error message from stl_load_errors .

20080215 04: 05: 06.789

Since I took this label from docs , I would expect it to work.

Error logs from the Redshift show:

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

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

How can I change the timestamp field to insert it with extra precision in seconds?

Answer 1

TL DR - When importing into Redshift from an S3 file, the imported data has a default time format of 'YYYY-MM-DD HH:MI:SS' , which Redshift expects to get accuracy in seconds, otherwise it will be truncated.

I ran into the same problem trying to download it from S3. My original JSON has such a timestamp. { "updated_at" : "2014-12-08T21:14:49.351638" } . However, when I went to pull it in Redshift, I needed to set a format that included T before 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, but the time was always truncated to a few seconds, so in Redshift I would get 2014-12-08 21:14:49 .

The documentation is as follows: import with an accuracy of 6 places, but this was not so.

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

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

Having made this change to use the default default, Redshift now imports timestamps with the correct precision!

Answer 2

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

Answer 3

In your copy command add this timeformat 'YYYY-MM-DD HH: MI: SS';

See details