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