在 MS SQL Server 中,有 Cast、Parse 和 Convert 等函数,但我在 PostgreSQL 中找不到类似的函数。
这是字符串格式的表格
如何将其转换为 yyyy-mm-dd 样式的日期时间格式?
Postgres 确实有 CAST
:
https://www.postgresql.org/docs/13/sql-expressions.html#SQL-SYNTAX-TYPE-CASTS
或 Postgres 快捷方式:
select '2-Jan-20'::date;
还有数据类型转换函数:
https://www.postgresql.org/docs/current/functions-formatting.html
对于此对话,to_date()
和 to_timestamp()
。
至于您的示例,最简单的方法是:
select '2-Jan-20'::date;
date
------------
2020-01-02
select 'Sunday,August 1,2021'::date;
date
------------
2021-08-01
select 'January 7 2023'::date;
date
------------
2023-01-07
例外是:
select '25/1/2028 4.25PM'::date;
ERROR: invalid input syntax for type date: "25/1/2028 4.25PM"
LINE 1: select '25/1/2028 4.25PM'::date;
由于时间段的原因,这失败了。即使你改变了:
select '25/1/2028 4:25PM'::date;
ERROR: date/time field value out of range: "25/1/2028 4:25PM"
LINE 1: select '25/1/2028 4:25PM'::date;
^
HINT: Perhaps you need a different "datestyle" setting.
--The date style has month/day/ordering
show datestyle;
DateStyle
-----------
ISO,MDY
--Changing it to day/month/ordering solves that issue.
set datestyle = iso,dmy;
SET
select '25/1/2028 4:25PM'::date;
date
------------
2028-01-25
要记住的重要一点是,如果要将值从字符串更改为日期/时间戳,它们不会被格式化存储。您将在查询中看到的格式将取决于您的 DateStyle 设置。
,不幸的是,Postgres 没有容错 to_date()
函数。你可以写一个:
create or replace function to_date_noerror (s text,fmt text)
returns date as
$$
begin
return to_date(s,fmt);
exception
when others then return null;
end;
$$ language plpgsql;
然后你可以试试格式:
select coalesce(to_date_noerror(col,'DD/MM/YYYY'),to_date_noerror(col,'DD MMM YY'),'MMM DD YYYY),. . .
)
您可以使用正则表达式进行合理的近似:
select (case when col ~ '^[0-9]{2}/[0-9]{2}/[0-9]{4}$'
then to_date(col,'DD/MM/YYYY')
when col ~ '^[0-9]{1,2} [A-Z]{3} [0-9]{4}$'
then to_date(col,'DD MMM YYYY')
. . .
end)
然而,这种方法仍然可能返回错误,并且很难生成一个 100% 匹配日期的正则表达式(想想闰年的问题)。
也就是说,真正的解决方案是修复您的数据模型。不要将日期存储为字符串。数据进入数据库时修复,这样你就不用处理坏数据了。
,我同意@Gordon Linoff 的观点,即最好的选择是清理数据。 还要感谢@Adrian Klaver 鼓励我查看postgresql cast function
我用过这个---
select cast ('2-Jan-20' as date)
union
select cast ('2-Jan-2025' as date)
union
select cast ('January 2 2023' as date)
union
select cast ('Sunday,2021' as timestamp)
union
select cast ('25/1/2028 4:25 PM' as timestamp);
结果是
虽然,无法理解如何直接从表格中做到这一点。