Thursday, December 23, 2010

Date functions 

Michael-Buens-MacBook:~ Michael$ cal 2010
                             2010

      January               February               March
Su Mo Tu We Th Fr Sa  Su Mo Tu We Th Fr Sa  Su Mo Tu We Th Fr Sa
                1  2      1  2  3  4  5  6      1  2  3  4  5  6
 3  4  5  6  7  8  9   7  8  9 10 11 12 13   7  8  9 10 11 12 13
10 11 12 13 14 15 16  14 15 16 17 18 19 20  14 15 16 17 18 19 20
17 18 19 20 21 22 23  21 22 23 24 25 26 27  21 22 23 24 25 26 27
24 25 26 27 28 29 30  28                    28 29 30 31
31                                          
       April                  May                   June
Su Mo Tu We Th Fr Sa  Su Mo Tu We Th Fr Sa  Su Mo Tu We Th Fr Sa
             1  2  3                     1         1  2  3  4  5
 4  5  6  7  8  9 10   2  3  4  5  6  7  8   6  7  8  9 10 11 12
11 12 13 14 15 16 17   9 10 11 12 13 14 15  13 14 15 16 17 18 19
18 19 20 21 22 23 24  16 17 18 19 20 21 22  20 21 22 23 24 25 26
25 26 27 28 29 30     23 24 25 26 27 28 29  27 28 29 30
                      30 31                 
        July                 August              September
Su Mo Tu We Th Fr Sa  Su Mo Tu We Th Fr Sa  Su Mo Tu We Th Fr Sa
             1  2  3   1  2  3  4  5  6  7            1  2  3  4
 4  5  6  7  8  9 10   8  9 10 11 12 13 14   5  6  7  8  9 10 11
11 12 13 14 15 16 17  15 16 17 18 19 20 21  12 13 14 15 16 17 18
18 19 20 21 22 23 24  22 23 24 25 26 27 28  19 20 21 22 23 24 25
25 26 27 28 29 30 31  29 30 31              26 27 28 29 30
                                            
      October               November              December
Su Mo Tu We Th Fr Sa  Su Mo Tu We Th Fr Sa  Su Mo Tu We Th Fr Sa
                1  2      1  2  3  4  5  6            1  2  3  4
 3  4  5  6  7  8  9   7  8  9 10 11 12 13   5  6  7  8  9 10 11
10 11 12 13 14 15 16  14 15 16 17 18 19 20  12 13 14 15 16 17 18
17 18 19 20 21 22 23  21 22 23 24 25 26 27  19 20 21 22 23 24 25
24 25 26 27 28 29 30  28 29 30              26 27 28 29 30 31
31

Months' first Mondays...
#
    base_date    | month_first_monday 
-----------------+--------------------
 Jan 01 2010 Fri | Jan 04 2010 Mon
 Feb 01 2010 Mon | Feb 01 2010 Mon
 Mar 01 2010 Mon | Mar 01 2010 Mon
 Apr 01 2010 Thu | Apr 05 2010 Mon
 May 01 2010 Sat | May 03 2010 Mon
 Jun 01 2010 Tue | Jun 07 2010 Mon
 Jul 01 2010 Thu | Jul 05 2010 Mon
 Aug 01 2010 Sun | Aug 02 2010 Mon
 Sep 01 2010 Wed | Sep 06 2010 Mon
 Oct 01 2010 Fri | Oct 04 2010 Mon
 Nov 01 2010 Mon | Nov 01 2010 Mon
 Dec 01 2010 Wed | Dec 06 2010 Mon
(12 rows)

...query (month_beginning_dow):

select to_char(z.d, 'Mon dd yyyy Dy') as base_date, 

    to_char( month_beginning_dow(extract(year from z.d)::int, 
        extract(month from z.d)::int, 1), 'Mon dd yyyy Dy') 
            as month_first_monday
from
(
     select 'Jan 1 2010'::date + (interval '1' month * x.n) as d
     from generate_series(0,11) as x(n)
) as z

Months' last Sundays...
#
    base_date    | month_last_date | month_last_sunday 
-----------------+-----------------+-------------------
 Jan 01 2010 Fri | Jan 31 2010 Sun | Jan 31 2010 Sun
 Feb 01 2010 Mon | Feb 28 2010 Sun | Feb 28 2010 Sun
 Mar 01 2010 Mon | Mar 31 2010 Wed | Mar 28 2010 Sun
 Apr 01 2010 Thu | Apr 30 2010 Fri | Apr 25 2010 Sun
 May 01 2010 Sat | May 31 2010 Mon | May 30 2010 Sun
 Jun 01 2010 Tue | Jun 30 2010 Wed | Jun 27 2010 Sun
 Jul 01 2010 Thu | Jul 31 2010 Sat | Jul 25 2010 Sun
 Aug 01 2010 Sun | Aug 31 2010 Tue | Aug 29 2010 Sun
 Sep 01 2010 Wed | Sep 30 2010 Thu | Sep 26 2010 Sun
 Oct 01 2010 Fri | Oct 31 2010 Sun | Oct 31 2010 Sun
 Nov 01 2010 Mon | Nov 30 2010 Tue | Nov 28 2010 Sun
 Dec 01 2010 Wed | Dec 31 2010 Fri | Dec 26 2010 Sun
(12 rows)

...query:
select to_char(z.d, 'Mon dd yyyy Dy') as base_date,
to_char(month_last_date(extract(year from z.d)::int, 
    extract(month from z.d)::int), 'Mon dd yyyy Dy') 
        as month_last_date,
to_char( month_ending_dow(extract(year from z.d)::int, 
    extract(month from z.d)::int, 0), 'Mon dd yyyy Dy') 
        as month_last_sunday       
from
(
     select 'Jan 1 2010'::date + (interval '1' month * x.n) as d
     from generate_series(0,11) as x(n)
) as z

Date library:
CREATE FUNCTION month_last_date(_year integer, _month integer)
  RETURNS date AS
$$
 select (($1 || '-' || $2 || '-1')::date + interval '1' month)::date - 1;
$$
  LANGUAGE sql immutable;



-- this function and the next function, has nice symmetry to each other
CREATE FUNCTION date_next_dow(_base_date date, _dow integer)
  RETURNS date AS
$$
 select
  case when extract(dow from $1) <= $2 then
   $1 + ($2 - (extract(dow from $1)::int))
  else
   $1 + ($2 - (extract(dow from $1)::int)) + 7
  end 
$$
  LANGUAGE sql immutable;


CREATE FUNCTION date_previous_dow(_base_date date, _dow integer)
  RETURNS date AS
$$
 select
  case when $2 <= extract(dow from $1) then
   $1 + ($2 - (extract(dow from $1)::int))
  else
   $1 + ($2 - (extract(dow from $1)::int)) - 7
  end;
$$
  LANGUAGE sql immutable;  



CREATE FUNCTION month_beginning_dow(_year integer, _month integer, _dow integer)
  RETURNS date AS
$$
 select date_next_dow( ($1 || '-' || $2 || '-1')::date, $3);
  
$$
  LANGUAGE sql immutable;


CREATE FUNCTION month_ending_dow(_year integer, _month integer, _dow integer)
  RETURNS date AS
$$
 select date_previous_dow( month_last_date( $1, $2 ), $3);
$$
  LANGUAGE sql immutable;



Other usage, next Friday...

#
    base_date    |   next_friday   
-----------------+-----------------
 Nov 05 2010 Fri | Nov 05 2010 Fri
 Nov 06 2010 Sat | Nov 12 2010 Fri
 Nov 07 2010 Sun | Nov 12 2010 Fri
 Nov 08 2010 Mon | Nov 12 2010 Fri
 Nov 09 2010 Tue | Nov 12 2010 Fri
 Nov 10 2010 Wed | Nov 12 2010 Fri
 Nov 11 2010 Thu | Nov 12 2010 Fri
 Nov 12 2010 Fri | Nov 12 2010 Fri
 Nov 13 2010 Sat | Nov 19 2010 Fri
 Nov 14 2010 Sun | Nov 19 2010 Fri
 Nov 15 2010 Mon | Nov 19 2010 Fri
 Nov 16 2010 Tue | Nov 19 2010 Fri
 Nov 17 2010 Wed | Nov 19 2010 Fri
 Nov 18 2010 Thu | Nov 19 2010 Fri
 Nov 19 2010 Fri | Nov 19 2010 Fri
 Nov 20 2010 Sat | Nov 26 2010 Fri
 Nov 21 2010 Sun | Nov 26 2010 Fri
 Nov 22 2010 Mon | Nov 26 2010 Fri
(18 rows)

..., query:

select to_char(z.d, 'Mon dd yyyy Dy') as base_date, 
       to_char( date_next_dow(z.d, 5), 'Mon dd yyyy Dy') as next_friday
from
(
     select 'Nov 5 2010'::date + x.n as d
     from generate_series(0,17) as x(n)
) as z

Previous Saturday...
#
    base_date    |  previous_saturday  
-----------------+-------------------
 Nov 05 2010 Fri | Oct 30 2010 Sat
 Nov 06 2010 Sat | Nov 06 2010 Sat
 Nov 07 2010 Sun | Nov 06 2010 Sat
 Nov 08 2010 Mon | Nov 06 2010 Sat
 Nov 09 2010 Tue | Nov 06 2010 Sat
 Nov 10 2010 Wed | Nov 06 2010 Sat
 Nov 11 2010 Thu | Nov 06 2010 Sat
 Nov 12 2010 Fri | Nov 06 2010 Sat
 Nov 13 2010 Sat | Nov 13 2010 Sat
 Nov 14 2010 Sun | Nov 13 2010 Sat
 Nov 15 2010 Mon | Nov 13 2010 Sat
 Nov 16 2010 Tue | Nov 13 2010 Sat
 Nov 17 2010 Wed | Nov 13 2010 Sat
 Nov 18 2010 Thu | Nov 13 2010 Sat
 Nov 19 2010 Fri | Nov 13 2010 Sat
 Nov 20 2010 Sat | Nov 20 2010 Sat
 Nov 21 2010 Sun | Nov 20 2010 Sat
 Nov 22 2010 Mon | Nov 20 2010 Sat
(18 rows)

...query:

select to_char(z.d, 'Mon dd yyyy Dy') as base_date, 
       to_char( date_previous_dow(z.d, 6), 'Mon dd yyyy Dy') 
           as previous_saturday
from
(
     select 'Nov 5 2010'::date + x.n as d
     from generate_series(0,17) as x(n)
) as z


Happy new year folks!

Related to: Finding previous day of the week
Posted by at
Labels: , ,

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)