20

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?

Kevin Meredith
  • 41,036
  • 63
  • 209
  • 384
  • 1
    I could upload the following JSON key-value into Redshift via the `COPY` command: `"putDateTime":"2009-11-17 10:45:32.021394"`. However, Redshift stored it as `2009-11-17 10:45:32` in the `putDateTime` column. Note that I created the column as a `TIMESTAMP` type. – Kevin Meredith Feb 03 '15 at 14:55
  • please consider accepting answers that helped you. – Mitch Wheat Jun 06 '23 at 03:20

3 Answers3

32

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!

Faiz
  • 5,331
  • 10
  • 45
  • 57
stsmurf
  • 430
  • 4
  • 8
17

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

jet
  • 698
  • 6
  • 12
6

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

Refer this for more details

Sandesh Deshmane
  • 2,247
  • 1
  • 22
  • 25