<p>我不相信这是最好的方法,但是这里有两个Postgres函数,它们完成了将模式转换为最短日期和最长日期这一难题:</p>
<p>{<kbd><kbd><kb>></p>
<pre><code>Create Function preprocessPattern(pat varchar(11), out cpat varchar(10), out neg boolean) as $$
declare
y varchar(4);
m varchar(2);
d varchar(2);
i int;
begin
neg = false;
if left(pat, 1) = '-' then
neg = true;
pat = right(pat, -1);
end if;
i = position('-' in pat);
y = right('000' || left(pat, i - 1), 4);
pat = right(pat, -i);
i = position('-' in pat);
m = right('0' || left(pat, i - 1), 2);
pat = right(pat, -i);
d = right('0' || pat, 2);
cpat = y || '-' || m || '-' || d;
end;
$$ Language plpgsql;
Create Function dateFromFmt(fmt varchar(10), neg boolean) returns date as $$
begin
if neg then
return to_date(fmt || ' BC', 'yyyy-mm-dd BC');
else
return to_date(fmt, 'yyyy-mm-dd');
end if;
end;
$$ Language plpgsql;
Create Function minDateFromPattern(pat varchar(11)) returns date as $$
declare
i int;
neg boolean;
n varchar(10);
begin
select * into pat, neg from preprocessPattern(pat);
i = position('#' in pat);
if i = 0 then
return dateFromFmt(pat, neg);
else
n = left(pat, i - 1) || right('0000-00-00', 0 - position('#' in pat) + 1);
n = replace(n, '-00', '-01');
return dateFromFmt(n, neg);
end if;
end;
$$ Language plpgsql;
Create Function maxDateFromPattern(pat varchar(11)) returns date as $$
declare
i int;
y int;
m int;
d int;
x varchar(10);
neg boolean;
res date;
begin
select * into pat, neg from preprocessPattern(pat);
i = position('#' in pat);
if i = 0 then
return dateFromFmt(pat, neg);
elsif i = 1 then
return date '9999-12-31';
from here down, pick the next highest mask, convert to min date then subtract one day
elsif i <= 6 then just add 1 to year
if i = 6 then i = 5; end if; skip - char
x = cast(cast(left(pat, i - 1) as int) + 1 as varchar) || right(pat, 0 - i + 1);
else
y = cast(left(pat, 4) as int);
if i = 7 then
m = cast(substr(pat, 6, 1) as int) + 1;
if m = 2 then
m = 0;
y = y + 1;
end if;
x = left(to_char(y, 'FM0000'), 4) || '-' || to_char(m, 'FM0') || '#-##';
elsif i = 9 then
m = cast(substr(pat, 6, 2) as int) + 1;
if m > 12 then
m = 1;
y = y + 1;
end if;
x = left(to_char(y, 'FM0000'), 4) || '-' || to_char(m, 'FM00') || '-##';
elseif i = 10 then
m = cast(substr(pat, 6, 2) as int);
d = cast(substr(pat, 9, 1) as int) + 1;
if (m = 2 and d = 3) or d = 4 then
m = m + 1;
d = 0;
if m > 12 then
m = 1;
y = y + 1;
end if;
end if;
x = left(to_char(y, 'FM0000'), 4) || '-' || to_char(m, 'FM00') || '-' || to_char(d, 'FM0') || '#';
end if;
end if;
the original logic looks a little silly now as we're preprocessing twice
res = minDateFromPattern(x) - interval '1 day';
if neg then
return dateFromFmt(to_char(res, 'yyyy-mm-dd'), neg);
else
return res;
end if;
end;
$$ Language plpgsql;
</code></pre>
<p>这已经经历了足够多的迭代,可以使用一些重构</p>