oracle - “DD-MON-RR”日期格式模式未按预期工作

Question

我正在插入一个格式为

的查询

to_date('25-JUN-13','DD-MON-RR')

在 oracle 中，它工作正常，输出为 25-JUN-13。
在 postgresql 中，它的工作方式与 0001-06-25 BC 相同。

这是一个从oracle数据库到postgresql的迁移项目。任何与 oracle 情况相同的解决方案。

如果我使用 DD-MM-YY 格式，同样无法正常工作，那么结果就会大不相同。

在 PostgreSQL 中运行这个查询 -->

select to_char(to_date('25-JUN-53','DD-MON-YY'),'YYYY') as YEAR 

ANSWER IS --> 2053 

同时从查询中检索与 oracle 相同的结果

select to_char(to_date('25-JUN-53','DD-MON-RR'),'YYYY') as YEAR from dual

ASSWER IS --> 1953

当我迁移项目时，Postgresql 中应该有相同的功能，以便最终结果应该相同。

Answer 1

The same is not working correctly ...

当然它工作正常。 The manual:

If the year format specification is less than four digits, e.g. YYY,and the supplied year is less than four digits, the year will beadjusted to be nearest to the year 2020, e.g. 95 becomes 1995.

因此，70 变为 1970，而 69 变为 2069。

Oracle 对格式说明符RR 有不同的规则，这在Postgres 中不存在。基本上，年份将调整为最接近 2000 年(距当前日期最近的世纪):

• https://docs.oracle.com/cd/B28359_01/server.111/b28286/sql_elements004.htm#SQLRF00216

解决方法

将功能封装在根据字符串中的年份数字切换世纪的函数中。由于 Postgres 允许函数重载，您甚至可以使用具有不同参数类型的相同函数名 to_date()。见:

• ERROR: function addgeometrycolumn is not unique

根据上面的文档，Oracle 在 YY = '50' 处回绕，这个函数在 2049 年之前是等效的:

CREATE OR REPLACE FUNCTION to_date(varchar, text)
  RETURNS date
  LANGUAGE sql STABLE AS
$func$
SELECT CASE WHEN right($1, 2) > '49' THEN
         to_date(left($1, -2) || '19' || right($1, 2), 'DD-MON-YYYY')
      ELSE
         to_date(left($1, -2) || '20' || right($1, 2), 'DD-MON-YYYY')
      END
$func$;

只有STABLE，而不是IMMUTABLE，因为to_date 只是STABLE。否则你禁用函数内联。

我为第一个参数选择了varchar，以区别于原来使用text。
如果年数 > 49，该函数将在转换前将 20 世纪(带有“19”)添加到日期字符串中，否则将 21 世纪添加到日期字符串中。第二个参数被忽略。

调用:

SELECT to_date('25-JUN-53'         , 'DD-MON-YY') AS original
     , to_date('25-JUN-53'<b>::varchar</b>, 'DD-MON-YY') AS patched1
     , to_date('25-JUN-53'<b>::varchar</b>, 'DD-MON-RR') AS patched2
     , to_date('25-JUN-53'<b>::varchar</b>, 'FOO-BAR')   AS patched3

我们的自定义函数无论如何都会忽略第二个参数。

结果:

  original  |  patched1  |  patched2  |  patched3  
------------+------------+------------+------------
 2053-06-25 | 1953-06-25 | 1953-06-25 | 1953-06-25

db<> fiddle here
<子>旧sqlddle

您可能会在 2049 年之后工作并考虑第二个参数...

---

一句警告:对基本函数的函数重载最好小心处理。如果它保留在您的系统中，以后有人可能会得到令人惊讶的结果。

最好在特殊模式中创建该函数并设置 search_path选择性地只在适当的时候使用它。在这种情况下，您也可以使用 text 作为参数类型:

CREATE SCHEMA specialfunc;
CREATE OR REPLACE FUNCTION specialfunc.to_date(text, text) AS ...

然后:

SET search_path = specialfunc, pg_catalog;
SELECT to_date('25-JUN-53', 'DD-MON-YY') AS patched;

或者使用临时函数。见:

• How to create a temporary function in PostgreSQL?