日期 月份周

代码片段

日期 LastDay()

适用于 PostgreSQL

任何版本

用

SQL

依赖于

无

由 Scott Bailey 'Artacus' 编写

在安排定期事件时，通常需要获取月份中的周数。 例如，感恩节总是 11 月的第四个星期四，或者本地 PostgreSQL 用户组会议是每个月的最后一个星期六。

从月初获取月份中的周数很容易。

  SELECT to_char(dtCol, 'W');

但获取三月的倒数第二个星期二就比较困难了。 在这里，我们将需要 last_day() 函数。

CREATE OR REPLACE FUNCTION week_of_month(
  p_date        DATE,
  p_direction   INT -- DEFAULT 1 -- for 8.4 and above
) RETURNS INT AS
$$
  SELECT CASE WHEN $2 >= 0 THEN
    CEIL(EXTRACT(DAY FROM $1) / 7)::int
  ELSE 
    0 - CEIL(
      (EXTRACT(DAY FROM last_day($1)) - EXTRACT(DAY FROM $1) + 1) / 7
    )::int
  END
$$ LANGUAGE 'sql' IMMUTABLE;

-- for 8.3 and below (no default params)
CREATE OR REPLACE FUNCTION week_of_month(
  p_date        DATE
) RETURNS INT AS
$$
  RETURN week_of_month($1, 1);
$$ LANGUAGE IMMUTABLE STRICT;

用法

-- Find Thanksgiving and Black Friday for next few years
SELECT cal_date, to_char(cal_date, 'Dy') dow
FROM info_calendar c
WHERE cal_date BETWEEN current_date AND '2012-12-01'
  AND EXTRACT(MONTH FROM cal_date) = 11 
  AND week_of_month(cal_date) = 4
  AND EXTRACT(DOW FROM cal_date) IN (4,5);

 cal_date     dow    
 -----------  ------ 
 11/26/2009   Thu    
 11/27/2009   Fri    
 11/25/2010   Thu    
 11/26/2010   Fri    
 11/24/2011   Thu    
 11/25/2011   Fri    
 11/22/2012   Thu    
 11/23/2012   Fri 


SELECT c.cal_date, week_of_month(cal_date,1),
  week_of_month(cal_date, -1), to_char(cal_date, 'Dy') dow
FROM info_calendar c
WHERE c.cal_date BETWEEN '2009-01-01' AND last_day('2009-01-15');

 cal_date     week_of_month     week_of_month     dow    
 -----------  ----------------  ----------------  ------ 
 1/1/2009     1                 -5                Thu    
 1/2/2009     1                 -5                Fri    
 1/3/2009     1                 -5                Sat    
 1/4/2009     1                 -4                Sun    
 1/5/2009     1                 -4                Mon    
 1/6/2009     1                 -4                Tue    
 1/7/2009     1                 -4                Wed    
 1/8/2009     2                 -4                Thu    
 1/9/2009     2                 -4                Fri    
 1/10/2009    2                 -4                Sat    
 1/11/2009    2                 -3                Sun    
 1/12/2009    2                 -3                Mon    
 1/13/2009    2                 -3                Tue    
 1/14/2009    2                 -3                Wed    
 1/15/2009    3                 -3                Thu    
 1/16/2009    3                 -3                Fri    
 1/17/2009    3                 -3                Sat    
 1/18/2009    3                 -2                Sun    
 1/19/2009    3                 -2                Mon    
 1/20/2009    3                 -2                Tue    
 1/21/2009    3                 -2                Wed    
 1/22/2009    4                 -2                Thu    
 1/23/2009    4                 -2                Fri    
 1/24/2009    4                 -2                Sat    
 1/25/2009    4                 -1                Sun    
 1/26/2009    4                 -1                Mon    
 1/27/2009    4                 -1                Tue    
 1/28/2009    4                 -1                Wed    
 1/29/2009    5                 -1                Thu    
 1/30/2009    5                 -1                Fri    
 1/31/2009    5                 -1                Sat

注意

此代码片段借鉴了 Chronos 时间扩展 项目。 Chronos 是一个完整的工具包，用于在 Postgres 和 Oracle 中处理时间数据。 我将在这里发布一些更通用的功能。（既然我是作者，我想这样是可以的。）