如何在不知道日期格式的情况下将日期字符串转换为时间戳 [英] How to convert date strings to timestamp without knowing the date format

问题描述

我正在尝试编写一个查询，以将值插入没有时区数据的时间戳类型字段。该值来自CSV文件。

I am trying to write a query to insert a value into a timestamp with no timezone data type field. The value is coming from CSV file.

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

CSV文件上传是由客户端完成的，并且具有日期列。日期有时以 '28 -Sep-13'出现，有时以 '28 / 09/2013'格式出现。

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.

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

I tried to use the following to cast the string into timestamp: str_date::timestamp.

如果 str_date 类似于 '28 -Sep-13'，但是如果传入日期的格式为 '28 / 09/2013'，则不会发生此错误：

This works fine if str_date is something like '28-Sep-13' but it won't work if the incoming date has the format '28/09/2013', when this error occurs:

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

基本上，客户保留更改上传的CSV文件中的日期格式。

是否可以根据其实际格式将日期字符串转换为时间戳？

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?

推荐答案

您需要将日期样式设置为 ISO，DMY。默认情况下将其设置为 ISO，MDY，这会导致您的示例失败：

You need to set your datestyle to "ISO, DMY". It is set to "ISO, MDY" by default, and would cause your example to fail:

> 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设置和相关行为是古老的，因此应该可以正常工作）

(examples done in PostgreSQL 9.1, but the DateStyle setting and associated behavior are ancient, so should work fine)

这篇关于如何在不知道日期格式的情况下将日期字符串转换为时间戳的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！