Date WeekOfMonth

Snippets

Date LastDay()

Works with PostgreSQL

Any version

Written in

SQL

Depends on

Nothing

by Scott Bailey 'Artacus'

When scheduling recurring events it is often necessary to get the week of month. For instance, Thanksgiving is always the 4th Thursday in November or the local PostgreSQL Users Group meeting is the last Saturday of every month.

Getting the week of month from the beginning of the month is easy.

  SELECT to_char(dtCol, 'W');

But getting the second to last Tuesday of March is more difficult. Here we're going to need the last_day() function.

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;

Usage

-- 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

Note

This snippet was borrowed from the Chronos Temporal Extensions project. Chronos is a complete toolkit for working with temporal data in Postgres and Oracle. I'll post some of the more general functions here. (Since I'm the author, I guess that's alright.)