'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

同样不能正常工作…

当然工作正常。手动:

如果年份格式规格小于四位，例如YYY，而提供的年份小于四位数字，则年份为调整为最接近年份2020，例如95变为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()与不同的参数类型。看到:

• 错误:函数addgeometrycolumn不是唯一的

根据上面的文档，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'::varchar, 'DD-MON-YY') AS patched1
     , to_date('25-JUN-53'::varchar, 'DD-MON-RR') AS patched2
     , to_date('25-JUN-53'::varchar, 'FOO-BAR')   AS patched3

我们的自定义函数忽略第二个形参。

结果:

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

db<>
<子>老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;

或使用临时函数。看到:

如何在PostgreSQL中创建临时函数?

Answer 2

我写了一个示例函数来解决这个问题。我自己也在做同样的迁移。希望它对你有所帮助。你也可以用函数重载来实现，只要把函数重命名为你喜欢的名字就可以了。

CREATE OR REPLACE FUNCTION to_date_rr(TEXT, TEXT)
  RETURNS DATE AS
  $$
      DECLARE
        date_v DATE;
        fmt text := upper($2);
        DATE_VALUE TEXT :=$1;
        digit_diff numeric := length($1) - length($2);
      BEGIN
    $2 = upper($2);
    IF substring(fmt from position('RRRR' in fmt) for 4) = 'RRRR' THEN
        IF digit_diff < 0 THEN
            fmt := replace($2, 'RRRR', 'YYYY');
            IF substring(DATE_VALUE from position('RRRR' in $2) for 2) > '50' THEN
                date_v := to_date(overlay(DATE_VALUE placing '19' from position('RRRR' in $2) for 0), fmt);
            ELSE
                date_v := to_date(overlay(DATE_VALUE placing '20' from position('RRRR' in $2) for 0), fmt);
            END IF;
        ELSE
            fmt := replace($2, 'RRRR', 'YYYY');
            date_v := to_date($1, fmt);
        END IF;
    ELSIF substring(fmt from position('RR' in fmt) for 2) = 'RR' THEN
        IF digit_diff = 0 THEN
            fmt := replace($2, 'RR', 'YY');         
            IF substring(DATE_VALUE from position('RR' in $2) for 2) > '50' THEN
                date_v := to_date(overlay(DATE_VALUE placing '19' from position('RR' in $2) for 0), fmt);
            ELSE
                date_v := to_date(overlay(DATE_VALUE placing '20' from position('RR' in $2) for 0), fmt);
            END IF;

        ELSE
            fmt := replace($2, 'RR', 'YY');
            date_v := to_date($1, fmt);
        END IF;

    ELSIF substring(fmt from position('YY' in fmt) for 2) = 'YY' and substring(fmt from position('YYYY' in fmt) for 4) != 'YYYY' THEN
        IF digit_diff = 0 THEN  
            IF substring(DATE_VALUE from position('YY' in $2) for 2) >= '00' THEN
                date_v := to_date(overlay(DATE_VALUE placing '20' from position('YY' in $2) for 0), fmt);
            END IF;
        ELSIF digit_diff < 0 THEN
            IF substring(DATE_VALUE from position('YY' in $2) for 2) >= '00' THEN
                date_v := to_date(overlay(DATE_VALUE placing '200' from position('YY' in $2) for 0), fmt);      
            END IF;
        ELSE
            date_v := to_date($1, fmt);
        END IF;
    ELSE
        SELECT to_date($1, $2) INTO date_v;
    END IF;

        RETURN date_v;
      END;
  $$
LANGUAGE plpgsql;

Answer 3

可以使用to_char(Date Field， 'format to display')