postgresql 如何在不知道日期格式的情况下将日期字符串转换为时间戳
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/8130594/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to convert date strings to timestamp without knowing the date format
提问by Shiver
I am trying to write a query to insert a value into a timestamp with no timezone datatype field. The value is coming from CSV file.
我正在尝试编写查询以将值插入timestamp with no timezone data类型字段。该值来自 CSV 文件。
The version I am working with is PostgreSQL 8.1.21.
我正在使用的版本是PostgreSQL 8.1.21。
The CSV file upload is done by the client and it has a date column. The date sometimes comes as '28-Sep-13'and sometimes as '28/09/2013'formats.
CSV 文件上传由客户端完成，它有一个日期列。日期有时'28-Sep-13'以'28/09/2013'格式出现，有时以格式出现。
I tried to use the following to cast the string into timestamp:
str_date::timestamp.
我尝试使用以下内容将字符串转换为时间戳：
str_date::timestamp.
This works fine if str_dateis something like '28-Sep-13'but it won't work if the incoming date has the format '28/09/2013', when this error occurs:
如果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
ERROR: date/time field value out of range: "28/09/2013" HINT: Perhaps you need a different "datestyle" setting
Basically the client keeps changing the date format in the uploaded CSV file.
Is there a way to convert the date strings into timestamp depending on its actual format?
基本上，客户端会不断更改上传的 CSV 文件中的日期格式。
有没有办法根据其实际格式将日期字符串转换为时间戳？
回答by Matthew Wood
You need to set your datestyle to "ISO, DMY". It is set to "ISO, MDY" by default, and would cause your example to fail:
您需要将日期样式设置为“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)
(examples done in PostgreSQL 9.1, but the DateStyle setting and associated behavior are ancient, so should work fine)
（示例在 PostgreSQL 9.1 中完成，但 DateStyle 设置和相关行为是古老的，所以应该可以正常工作）
回答by Erwin Brandstetter
You can circumvent the problem with these steps:
您可以通过以下步骤规避该问题：
Create an empty temporary tablewith the same structure as target table:
CREATE TEMP TABLE tmp AS SELECT * FROM real_tbl LIMIT 0;Change the typeof the problematic column to text:
ALTER TABLE tmp ALTER COLUMN str_date TYPE text;Importdata to the temp table. Should work fine now:
COPY tmp FROM '/path/to/my/file.txt';INSERTinto target tabledepending 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
创建一个与目标表结构相同的空临时表：
CREATE TEMP TABLE tmp AS SELECT * FROM real_tbl LIMIT 0;将有问题的列的类型更改为text：
ALTER TABLE tmp ALTER COLUMN str_date TYPE text;将数据导入临时表。现在应该可以正常工作：
COPY tmp FROM '/path/to/my/file.txt';INSERT进入目标表取决于列的实际内容：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
回答by Micha? Niklas
I agree with Erwin, but I would try create database function (PL/pgSQL, PL/Python or other language) that can convert various date strings into date. In Erwins answer you can see WHEN ... THENand you can use it. Such function will be easier to test and maintain.
我同意 Erwin，但我会尝试创建数据库函数（PL/pgSQL、PL/Python 或其他语言），可以将各种日期字符串转换为date. 在 Erwins 的答案中，您可以看到WHEN ... THEN并且可以使用它。这样的功能将更容易测试和维护。
