Finding previous day of the week

Question

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.

Answer 1

Try this, works on other days too, blog about it http://www.ienablemuch.com/2010/12/finding-previous-day-of-week.html

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 'Dec 1 2010'::date + x.n as ds
     from generate_series(0,17) as x(n)
) as z