如何将TIMESTAMP列插入Redshift

I created a table in Redshift:

我在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.

但是，当我尝试插入包含dateTime的记录时，我从stl_load_errors收到错误。

20080215 04:05:06.789

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:

Redshift的错误日志显示：

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

时间戳格式或值无效[YYYY-MM-DD HH24：MI：SS]

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

但是，我想包括3秒，例如：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?

如何修改我的时间戳字段以在秒上以额外的精度插入它？

3 个解决方案

#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.

TL; DR - 从S3文件导入Redshift时强制导入的数据具有默认时间格式'YYYY-MM-DD HH：MI：SS'that Redshift期望获得精确度超过秒，否则它将被截断。

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.

我试图上传从S3进入时遇到了同样的问题。我原来的JSON有这样的时间戳。 {“updated_at”：“2014-12-08T21：14：49.351638”}。然而，当我把它拉到Redshift时，我需要设置格式，其中包括T之前的时间。

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';

COPY schema.temp_table FROM's3：// s3-bucket / file-name'WH 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.

这导入了一切，但是时间总是被截断到秒，所以我最终会在Redshift中以2014-12-08 21:14:49结束。

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

文档看起来应该精确导入到6个位置，但事实并非如此。

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.

我决定尝试使用默认格式'YYYY-MM-DD HH：MI：SS'导入Redshift，所以我不得不更改我的Postgres数据库，以正确的格式导出日期字段的JSON to_char（updated_at，'YYYY- MM-DD HH24：MI：SS.SSSSS'）作为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';

在进行此更改之后，新的JSON导出为{“updated_at”：“2014-12-08 21：14：49.351638”}并且我将导入Redshift的时间格式设置为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!

通过使此更改使用默认时间格式Redshift现在以正确的精度导入时间戳！

#2

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

在你的复制命令中，请添加此时间格式'YYYY-MM-DD HH：MI：SS';

Refer this for more details

有关详细信息，请参阅此

#3

timeformat 'auto' and dataformat '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

timeformat'auto'和dataformat'auto'在我的格式上工作得很好，2017-11-02T21：04：03.108Z。有关http://docs.aws.amazon.com/redshift/latest/dg/automatic-recognition.html的文档

#1