时间戳

首页 > 解决方案 > 如何从闰年或正常年获得一致的周数

sql - 如何从闰年或正常年获得一致的周数

问题描述

使用 PostgreSQL 版本 9.4.18，PostGIS 版本 2.2 我正在使用此功能，并且我正在使用 WW 因为不想要 ISO 周数......只是从 1 月 1 日开始的周数：

CREATE FUNCTION f_woy(timestamp with time zone) RETURNS int LANGUAGE sql
IMMUTABLE AS $$SELECT to_char($1, 'WW')::int$$;

当我运行以下查询时，每 4 年，周数会发生变化，它是闰年。我试图在某个日期每年获得一致的周数。所以，例如，当我跑步时

SELECT f_woy(('2007-06-24 14:19:46.502-07'::timestamp at time zone 'UTC' at 
time zone 'america/los_angeles')::date)

作为 2004、2005、2006 和 2007 年的 woy，我得到：

woy integer
25

但是当我运行它时：

SELECT f_woy(('2008-06-24 14:19:46.502-07'::timestamp at time zone 'UTC' at 
time zone 'america/los_angeles')::date)

我明白了

woy integer
26

有没有办法可以忽略闰年并保持一切一致，以便所有 06-24 都是第 25 周。我看到一些关于确定是否是闰年的帖子，但我很难过集成该逻辑，以便我可以将其应用于我正在检查周数的所有查询。

标签： sqlpostgresql

解决方案

您可以使用CASE WHEN表达式检查今年是否是闰年

  1. 今年是闰年返回结果减1。
  2. 今年不是闰年返回结果。

判断闰年公式是

（年 % 4 = 0）与（年 % 100 <> 0）或（年 % 400 = 0）

在条件中添加此公式CASE WHEN以使其

看起来像这样。

CREATE FUNCTION f_woy(timestamp with time zone) 
RETURNS int AS $$  
SELECT CASE WHEN (date_part('year',$1)::int % 4 = 0) AND ((date_part('year',$1)::int % 100 <> 0) OR (date_part('year',$1)::int % 400 = 0)) 
         THEN to_char($1, 'WW')::int -1 
       ELSE to_char($1, 'WW')::int
END
$$ LANGUAGE sql IMMUTABLE STRICT;

当您使用您的f_woy功能时，闰年的周将与正常年的周相同。

SELECT f_woy(('2007-06-24 14:19:46.502-07'::timestamp at time zone 'UTC' at 
time zone 'america/los_angeles')::date), 
       f_woy(('2008-06-24 14:19:46.502-07'::timestamp at time zone 'UTC' at 
time zone 'america/los_angeles')::date)

结果

f_woy   f_woy
25      25

sqlfiddle：https ://dbfiddle.uk/?rdbms=postgres_9.6&fiddle=b43eb6f9c2accde5fc74ebe980a039d7

编辑

闰年意味着今年将增加2月29日，所以你可以试试这个。

IsLeapYear函数获取今年是或不是闰年。

create or replace function IsLeapYear(int)
returns boolean as $$
    select $1 % 4 = 0 and ($1 % 100 <> 0 or $1 % 400 = 0)
$$ LANGUAGE sql IMMUTABLE STRICT;

f_woy函数获取当前周数。

create or replace function f_woy(date)
returns int language plpgsql as $$
declare
    currentYear int = extract (year from $1);
    LeapYearShift int = 1 + (IsLeapYear(currentYear) and $1 > make_date(currentYear, 2, 28))::int;
begin
    return ((extract(doy from $1)::int)- LeapYearShift) / 7+ 1;
end;
$$;

笔记

  • 1 + (IsLeapYear(currentYear) and $1 > make_date(currentYear, 2, 28))::int意思是如果今年是 闰年需要减去更多的一天（2 月 29 日）。让闰年的日子与正常年份相同。

SQLFIDDLE

推荐阅读