案例陈述：WHEN 日期距当前日期超过 90 年 THEN 最大日期距当前日期 90 年

Question

我有一个包含人们生日的日期列。其中一些包含 90 多年前的日期，我希望这些日期的上限为 90 年前。

示例

current      desired
1930-01-01   1932-01-01
1930-02-11   1932-02-11

正如您所看到的，如果出生日期自然超过 90 年前，则期望的状态是使出生日期距当前日期 90 年。

Answer 1

这比我最初猜到的要棘手，而且可以更简单地写下，因为似乎有“竞争”，尽管它是易于阅读的，而是“

90年以上的无日期：

SELECT column1 as "current"
    ,iff(column1 < dateadd('year',-90, CURRENT_DATE), 
         date_from_parts(
            year(CURRENT_DATE) - 90 + (month(column1)*100+day(column1) < month(CURRENT_DATE)*100 + day(CURRENT_DATE))::int, 
             month(column1), 
             day(column1)), 
         column1
        ) as desired
FROM VALUES 
    ('1934-01-01'::date),
    ('1932-03-28'::date),
    ('1932-03-20'::date),
    ('1931-03-28'::date),
    ('1931-03-20'::date),
    ('1930-01-01'::date),
    ('1930-02-11'::date);

当前	想要的
1934-01-- 01	1934-01-01
1932-03-28	1932-03-28
1932-03-20	1933-03-03-20
1931-03-28	1932-03-28 1931-28
1931-03-20 1933-20	1933-03-03-20
1930-20 1930-01--01- 01	1933-01-01
1930-02-11	1933-02-11

在90年前（又名标题）之前的一年（标题）版本：

SELECT column1 as "current"
    ,iff(year(column1) < year(CURRENT_DATE)-90, date_from_parts(year(CURRENT_DATE)-90, month(column1), day(column1)), column1) as desired
FROM VALUES
    ('1934-01-01'::date),
    ('1932-03-28'::date),
    ('1932-03-20'::date),
    ('1931-03-28'::date),
    ('1931-03-20'::date),
    ('1930-01-01'::date),
    ('1930-02-11'::date)
;

当前	想要的
1934-01-01-01-01	1934-01-01-01-01-01
1932-03-28	1932 -03-28
1932-03-20	1932-03-20
1931-03-28	1932-03-28
1931-03-20	1932-03-03-20
1930-01-01-01-01 1932-01-01-01-01-01	1930-02-11
1932-11 1932-11	1932-11 -02-11

唯一的案例：

因此，正如案例语句的背景下所要求的那样，我再次写了它（并修复了一年的部分），但也将其解构为解构，因此可以看到逻辑：

SELECT column1 as birth_day
    ,dateadd('year',-90, CURRENT_DATE) as ninety_years_ago
    ,year(ninety_years_ago) as ninety_years_ago_year
    ,(month(CURRENT_DATE)*100) + day(CURRENT_DATE) as current_partial_year
    ,(month(birth_day)*100) + day(birth_day) as birth_day_partial_year
    ,case when birth_day_partial_year < current_partial_year then 1 else 0 end as partial_year_correction
    ,(birth_day_partial_year < current_partial_year)::int as current_partial_year_alt_version /* not used just showing how it works */
    ,case
        WHEN column1 < ninety_years_ago then
            date_from_parts(ninety_years_ago_year + partial_year_correction, month(birth_day), day(birth_day))
        ELSE 
            birth_day
        END as desired
FROM VALUES 
    ('1934-01-01'::date),
    ('1932-04-28'::date),
    ('1932-03-20'::date),
    ('1931-04-28'::date),
    ('1931-03-20'::date),
    ('1930-01-01'::date),
    ('1930-02-11'::date);

给予：给予：

berth_day	ninety_years_ago	ninety_years_ago_ogo_year	current_partial_year	04-28	birth_day_day_year_year_year_year_year_correction	curruct	1932-03-
​	​	​	​	​	​	​	​
​	​	​	​	​	​	​	​
​20	1932-03-31	1932	331	320	1	1	1933-03-20
1931-04-28	1932-03-03-31	1932	331	428	0	0	1932-04-28
1931-031-03-03-20	​	​	​	0	0	1	1933-03-20
1930-01-01	1932-03-31	1932	331	101	1 1	1	1933-01-01
1930-02-11	1932-03-03-31	1932 331	331 331	211	1	1	1933-02-111 1933-02-11

Answer 2

肯定很棘手。我什至在timeanddate.com上检查了最大90年的日期差异 - 看起来还不错。如果有人在此发现问题，请感谢反馈。

select iff(extract(year,dateadd('year',90,column1::date))>extract(year,'2022-02-28'::date),
    column1,
    dateadd(day,
    datediff(day,column1::date,dateadd(year,-90,'2022-02-28'::date)),
    column1::date
    )) as test
    from
    values
    ('1932-02-29'::date)
    ,('1932-02-28'::date)
    ,('1932-03-01'::date)
    ,('1928-02-29'::date)
    ,('1924-02-29'::date)
    ,('1930-01-01'::date)
    ,('1940-05-01'::date)
    ,('1950-12-01'::date)
    ,('1980-10-01'::date)
    ,('1920-06-01'::date)
    ;
    
+------------+
| TEST       |
|------------|
| 1932-02-28 |
| 1932-02-28 |
| 1932-02-28 |
| 1932-02-28 |
| 1932-02-28 |
| 1932-02-28 |
| 1940-05-01 |
| 1950-12-01 |
| 1980-10-01 |
| 1932-02-28 |
+------------+