将 CSV 时间戳转换为 postgresql 时间戳格式会给出错误:日期/时间字段值超出范围

Question

我正在尝试将带有时间戳(2020/01/07 07:20:12.718000)的csv文件导入postgresql。据我从其他帖子中收集到的信息，我无法将时间戳直接导入为Postgresql Timestamp格式。我必须将csv时间戳导入为文本，然后转换为postgresql格式。

到目前为止，我能够使用 copy 命令导入所有时间戳。然后，我尝试使用以下代码来转换时间戳格式：

Attempt1
select (to_timestamp(mytable."Timestamp",'HH24:MI:SS.MS')) as newtimestamp from mytable;
Attempt2
select (to_timestamp(mytable."Timestamp",'HH24:MI:SS.FM.US')) as newtimestamp from mytable;

两人都给出了Error:date/time field value out of range我认为问题可能是因为 6 位数字的毫秒精度。要处理 6 位毫秒的精度，我的尝试 2 应该足够了。

现在我不知道该怎么办。 我正在使用postgresql 12。

show datestyle;
DateStyle 
-----------
ISO, MDY

请引导我朝着正确的方向前进。另外，如果有更好的方法将时间戳从csv导入postgresql，请告诉我。 谢谢

Answer 1

显然，您可以将其硬转换为时间戳：

$ psql -a -f try.sql
SELECT
'2020/01/07 07:20:12.718000'::TIMESTAMP AS the_ts
,extract(MONTH FROM '2020/01/07 07:20:12.718000'::TIMESTAMP) AS the_month
;
the_ts          | the_month 
-------------------------+-----------
2020-01-07 07:20:12.718 |         1
(1 row)

还是您只需要时间部分？ 然后，您需要使用TO_TIMESTAMP()从字符串中创建一个时间戳，然后使用TO_CHAR()重新格式化生成的时间戳以仅包含时间部分：

WITH
input(string) AS (
SELECT '2020/01/07 07:20:12.718000'
)
SELECT
TO_CHAR(
TO_TIMESTAMP(string,'YYYY/MM/DD HH24:MI:SS.FF')
, 'HH24:MI:SS.MS'
)
FROM input;
to_char    
--------------
07:20:12.000

Answer 2

是的，您实际上不能从非标准格式的时间戳进行直接复制。 这是我所做的：

• 向表中添加文本列以接收文本时间戳：

  ALTER TABLE dest_table ADD COLUMN timestamp_text TEXT;
  

• 执行复制，将时间戳复制到文本列中(我使用了 psql 的copy命令(：

  COPY dest_table(stuff,other_stuff,timestamp_text) FROM STDIN WITH (FORMAT CSV,HEADER TRUE,DELIMITER '|'
  

• 从文本列更新时间戳列：

  UPDATE dest_table SET the_timestamp=to_timestamp(timestamp_text,'YYYY-MM-DD HH24:MI:SS,MS');
  

  (根据需要替换时间戳格式(

• 删除文本时间戳列：

  ALTER TABLE dest_table DROP COLUMN timestamp_text;
  

当然，您可能希望将所有这些放入脚本中。