如何在不知道日期格式的情况下将日期字符串转换为时间戳

Question

我在尝试编写一个查询语句将值插入到一个没有时区数据的timestamp类型字段中。该值来自CSV文件。

我正在使用的版本是PostgreSQL 8.1.21。

客户端通过上传CSV文件完成，其中包含一个日期列。日期有时以'28-Sep-13'和'28/09/2013'格式出现。

我尝试使用以下代码将字符串转换为时间戳：str_date::timestamp。

如果str_date类似于'28-Sep-13'，则这样可以正常工作，但如果传入的日期格式为'28/09/2013'，则会出现以下错误：

ERROR: date/time field value out of range: "28/09/2013"  
HINT:  Perhaps you need a different "datestyle" setting

基本上，客户在上传的CSV文件中不断更改日期格式。
有没有一种方法可以根据实际格式将日期字符串转换为时间戳？

- Shiver

---

首先：考虑升级。PostgreSQL 8.1于2005年发布，已于一年前到达生命周期。它非常过时。 - Erwin Brandstetter

你们用我的生日作为例子，太棒了；-) - milovanderlinden

Answer 1

你需要将你的日期样式设置为“ISO, DMY”。默认情况下，它设置为“ISO, MDY”，这会导致你的示例失败:

> show datestyle;

 DateStyle 
-----------
 ISO, MDY
(1 row)

> select '28-Sep-13'::date;
    date    
------------
 2013-09-28
(1 row)

> select '28/09/2013'::date;
ERROR:  date/time field value out of range: "28/09/2013"
LINE 1: select '28/09/2013'::date;
               ^
HINT:  Perhaps you need a different "datestyle" setting.

> set datestyle = 'ISO, DMY';
SET

> select '28-Sep-13'::date;
    date    
------------
 2013-09-28
(1 row)

> select '28/09/2013'::date;
    date    
------------
 2013-09-28
(1 row)

(这些示例是在 PostgreSQL 9.1 中完成的，但 DateStyle 设置和相关行为已经存在很久了，所以应该可以正常工作)

- Matthew Wood

---

感谢Matthew的帮助。我在查询中添加了“set datestyle ='ISO，DMY'”，问题已解决。 - Shiver

很高兴听到问题已经解决。如果您能够标记答案为已接受，我们将不胜感激！ - Matthew Wood

Answer 2

您可以通过以下步骤避免这个问题：

1. Create an empty temporary table with the same structure as target table:

   CREATE TEMP TABLE tmp AS SELECT * FROM real_tbl LIMIT 0;
   

2. Change the type of the problematic column to text:

   ALTER TABLE tmp ALTER COLUMN str_date TYPE text;
   

3. Import data to the temp table. Should work fine now:

   COPY tmp FROM '/path/to/my/file.txt';
   

4. INSERT into target table depending on depending on the actual content of the column:

   INSERT INTO real_tbl (col1, col2, col3, date_col)
   SELECT col1, col2, col3
        , CASE WHEN str_date ~~ '%/%'
             THEN to_date(str_date, 'DD/MM/YYYY')
          WHEN str_date ~~ '%-%'
             THEN to_date(str_date, 'DD-Mon-YYYY')
           -- more cases?
          ELSE ???
          END AS date_col
   FROM   tmp;
   
   -- DROP TABLE tmp;  -- optional; dropped at end of session automatically
   

- Erwin Brandstetter

Answer 3

我同意Erwin的观点，但是我会尝试创建数据库函数（使用PL/pgSQL、PL/Python或其他语言），可以将各种日期字符串转换为date类型。在Erwin的回答中，你可以看到WHEN ... THEN语句，你可以使用它。这样的函数将更容易进行测试和维护。

- Michał Niklas