Sunday, December 19, 2010

Finding previous day of the week




This question in Stackoverflow...

In PostgreSQL 8.4, given a date, if that date is not a Friday, I would like to find the date of the previous Friday. Can someone tell me if there is an inbuilt function or give the logic behind getting my own function.

...has an accepted answer of:

SELECT 
    CASE 
-- 1. if Friday, return date
    WHEN EXTRACT(DOW FROM my_date) = 5 
    THEN my_date
-- 2. if Saturday, subtract 1
    WHEN EXTRACT(DOW FROM my_date) = 6 
    THEN my_date - INTERVAL '1 day'
-- 3. all other days of the week, subtract `DOW + 2` from my_date
    -- should be ELSE for future-proofing ;-) MB
    ELSE -- WHEN EXTRACT(DOW FROM my_date) < 5 THEN
        my_date - ((EXTRACT(DOW FROM my_date) + 2)::TEXT||'days')::INTERVAL
    END AS tgif
FROM 
    my_table
WHERE 
    my_date IS NOT NULL

That query is hard to modify if the requirement changes. What's wrong with that? For one, there is special handling of a corner case, i.e. 6, for Saturday. Second, there's a magic number in that query, which is number 2. And there is complication when the requirement changes, when you change the parameter to Thursday, what need to be changed? The obvious are: change the 6 and 5 to 5 and 4 respectively; what's the not so obvious is the number 2, where is that number derived? Should we increment it, or should we decrement it? Though in the defense of the SOer, one cannot introduce a variable outside of function in Postgresql.

So to make the intent more clear, here's his functionalized routine:

create function recent_day(_dow int, out src text, out dst text) returns setof record
as
$$

SELECT 
 to_char(your_date, 'Mon dd yyyy dy'),

 to_char(
     CASE 
 -- 1. if Friday, return date
     WHEN EXTRACT(DOW FROM your_date) = $1
     THEN your_date
 -- 2. if Saturday, subtract 1
     WHEN EXTRACT(DOW FROM your_date) =  $1 + 1 
     THEN your_date - INTERVAL '1 day'
 -- 3. all other days of the week, subtract `DOW + 2` from your_date
     ELSE -- WHEN EXTRACT(DOW FROM your_date) < 4 THEN 
  your_date - ((EXTRACT(DOW FROM your_date) + (7 - $1))::TEXT||'days')::INTERVAL
     END,
  'Mon dd yyyy dy')
      AS tgif

from
(
     select 'Nov 5 2010'::date + x.n as your_date
     from generate_series(0,17) as x(n)
) as x
$$ language 'sql';


Here's the output of that function:
memdb=# select * from recent_day(5);
       src       |       dst       
-----------------+-----------------
 Nov 05 2010 fri | Nov 05 2010 fri
 Nov 06 2010 sat | Nov 05 2010 fri
 Nov 07 2010 sun | Nov 05 2010 fri
 Nov 08 2010 mon | Nov 05 2010 fri
 Nov 09 2010 tue | Nov 05 2010 fri
 Nov 10 2010 wed | Nov 05 2010 fri
 Nov 11 2010 thu | Nov 05 2010 fri
 Nov 12 2010 fri | Nov 12 2010 fri
 Nov 13 2010 sat | Nov 12 2010 fri
 Nov 14 2010 sun | Nov 12 2010 fri
 Nov 15 2010 mon | Nov 12 2010 fri
 Nov 16 2010 tue | Nov 12 2010 fri
 Nov 17 2010 wed | Nov 12 2010 fri
 Nov 18 2010 thu | Nov 12 2010 fri
 Nov 19 2010 fri | Nov 19 2010 fri
 Nov 20 2010 sat | Nov 19 2010 fri
 Nov 21 2010 sun | Nov 19 2010 fri
 Nov 22 2010 mon | Nov 19 2010 fri
(18 rows)

So it's correct :-)

Now let's try Thursday :-)

memdb=# select * from recent_day(4);
       src       |       dst       
-----------------+-----------------
 Nov 05 2010 fri | Nov 04 2010 thu
 Nov 06 2010 sat | Oct 28 2010 thu
 Nov 07 2010 sun | Nov 04 2010 thu
 Nov 08 2010 mon | Nov 04 2010 thu
 Nov 09 2010 tue | Nov 04 2010 thu
 Nov 10 2010 wed | Nov 04 2010 thu
 Nov 11 2010 thu | Nov 11 2010 thu
 Nov 12 2010 fri | Nov 11 2010 thu
 Nov 13 2010 sat | Nov 04 2010 thu
 Nov 14 2010 sun | Nov 11 2010 thu
 Nov 15 2010 mon | Nov 11 2010 thu
 Nov 16 2010 tue | Nov 11 2010 thu
 Nov 17 2010 wed | Nov 11 2010 thu
 Nov 18 2010 thu | Nov 18 2010 thu
 Nov 19 2010 fri | Nov 18 2010 thu
 Nov 20 2010 sat | Nov 11 2010 thu
 Nov 21 2010 sun | Nov 18 2010 thu
 Nov 22 2010 mon | Nov 18 2010 thu
(18 rows)

Uh oh, something wrong. Look at Nov 20's recent Thursday, it should be November 18, yet in his query it is November 11. Lesson of the day, sometimes when you handle corner cases, it only handles corner cases very well ;-)

This is my solution(though unfortunately was not accepted):

create or replace function recent_day_mb(_dow int, out src text, out dst text) returns setof record
as
$$
select
 to_char(ds, 'Mon dd yyyy dy'), 

 to_char(

  ds - (extract(dow from ds) - $1)::int +

   case when extract(dow from ds) - $1 < 0 then -7 
   else 0 end

  ,'Mon dd yyyy dy') as recent_friday



from
(
     select 'Nov 5 2010'::date + x.n as your_date
     from generate_series(0,17) as x(n)
) as x
$$ language 'sql';


Let's try Friday

memdb=# select * from recent_day_mb(5);
       src       |       dst       
-----------------+-----------------
 Nov 05 2010 fri | Nov 05 2010 fri
 Nov 06 2010 sat | Nov 05 2010 fri
 Nov 07 2010 sun | Nov 05 2010 fri
 Nov 08 2010 mon | Nov 05 2010 fri
 Nov 09 2010 tue | Nov 05 2010 fri
 Nov 10 2010 wed | Nov 05 2010 fri
 Nov 11 2010 thu | Nov 05 2010 fri
 Nov 12 2010 fri | Nov 12 2010 fri
 Nov 13 2010 sat | Nov 12 2010 fri
 Nov 14 2010 sun | Nov 12 2010 fri
 Nov 15 2010 mon | Nov 12 2010 fri
 Nov 16 2010 tue | Nov 12 2010 fri
 Nov 17 2010 wed | Nov 12 2010 fri
 Nov 18 2010 thu | Nov 12 2010 fri
 Nov 19 2010 fri | Nov 19 2010 fri
 Nov 20 2010 sat | Nov 19 2010 fri
 Nov 21 2010 sun | Nov 19 2010 fri
 Nov 22 2010 mon | Nov 19 2010 fri
(18 rows)

It's correct

Now let's try Thursday:

memdb=# select * from recent_day_mb(4);
       src       |       dst       
-----------------+-----------------
 Nov 05 2010 fri | Nov 04 2010 thu
 Nov 06 2010 sat | Nov 04 2010 thu
 Nov 07 2010 sun | Nov 04 2010 thu
 Nov 08 2010 mon | Nov 04 2010 thu
 Nov 09 2010 tue | Nov 04 2010 thu
 Nov 10 2010 wed | Nov 04 2010 thu
 Nov 11 2010 thu | Nov 11 2010 thu
 Nov 12 2010 fri | Nov 11 2010 thu
 Nov 13 2010 sat | Nov 11 2010 thu
 Nov 14 2010 sun | Nov 11 2010 thu
 Nov 15 2010 mon | Nov 11 2010 thu
 Nov 16 2010 tue | Nov 11 2010 thu
 Nov 17 2010 wed | Nov 11 2010 thu
 Nov 18 2010 thu | Nov 18 2010 thu
 Nov 19 2010 fri | Nov 18 2010 thu
 Nov 20 2010 sat | Nov 18 2010 thu
 Nov 21 2010 sun | Nov 18 2010 thu
 Nov 22 2010 mon | Nov 18 2010 thu
(18 rows)

See the Nov 20, its recent Thursday is November 18, not November 11. Works well :-)

Happy querying!


[EDIT]


My most succint solution:
create or replace function previous_date_of_day(the_date date, dow int) returns date
as
$$
select 
    case when extract(dow from $1) < $2 then
        $1 - ( extract(dow from $1) + (7 - $2) )::int 
    else
        $1 - ( extract(dow from $1) - $2)::int
    end;
$$ language 'sql';


select to_char(z.ds, 'Mon dd yyyy dy') as source, 
       to_char( previous_date_of_day(z.ds, 5), 'Mon dd yyyy dy') as dest
from
(
     select 'Nov 5 2010'::date + x.n as ds
     from generate_series(0,17) as x(n)
) as z


[EDIT]

The asker(Rabin) already changed the accepted answer to mine. Thanks Rabin for acknowledging code correctness! :-)

[EDIT 2010-12-04]

Here's the other related functions, the function previous_date_of_day in this article is simplifed to date_previous_dow, check here : http://www.ienablemuch.com/2010/12/date-functions.html

No comments:

Post a Comment