CODEWITHSUNDEEP
postgresql
Rabin
Rabin

Reputation: 1583

Finding previous day of the week

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.

Upvotes: 3

Views: 1440

Answers (4)

Michael Buen
Michael Buen

Reputation: 39393

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

Upvotes: 2

mechanical_meat
mechanical_meat

Reputation: 169284

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

Upvotes: 1

nate c
nate c

Reputation: 9005

You solve it without using case:

select 
the_date 
from 
(
  select 
    now()::date - num as the_date, -- generate rows of possible dates
    extract(dow from (now()::date - num)) -- dow for the where condition
  from (select  generate_series(0,6) as num) as t
) as days 
where date_part = 5;

Upvotes: 1

Javed Akram
Javed Akram

Reputation: 15344

select case when extract(dow from your_date) < 5 then
        your_date - (extract(dow from your_date) + integer '2')
       else when extract(dow from your_date) > 5 then
        your_date - integer '1' 
       else 
        your_date
       end

Reference http://developer.postgresql.org/pgdocs/postgres/functions-datetime.html

Upvotes: 0

Related Questions