Advertisement
Guest User

Untitled

a guest
Apr 18th, 2014
53
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
text 0.88 KB | None | 0 0
  1. CREATE OR REPLACE FUNCTION f_mondays(dr daterange)
  2. RETURNS TABLE (day date) AS
  3. $func$
  4. SELECT generate_series(a + (8 - EXTRACT(ISODOW FROM a)::int) % 7
  5. , z
  6. , interval '7 days')::date
  7. FROM (
  8. SELECT CASE WHEN lower_inc(dr) THEN lower(dr) ELSE lower(dr) + 1 END AS a
  9. , CASE WHEN upper_inc(dr) THEN upper(dr) ELSE upper(dr) - 1 END AS z
  10. ) sub
  11. $func$ LANGUAGE sql;
  12.  
  13. SELECT day FROM f_mondays('[2014-04-14,2014-05-02)'::daterange);
  14.  
  15. day
  16. ----------
  17. 2014-04-14
  18. 2014-04-21
  19. 2014-04-28
  20.  
  21. create function f(dr daterange)
  22. returns setof date as $$
  23. select d::date
  24. from generate_series(
  25. lower(dr), upper(dr), interval '1 day'
  26. ) s (d)
  27. where
  28. extract(dow from d) = 1 and
  29. d::date <@ dr;
  30. ;
  31. $$ language sql;
  32.  
  33. select f(daterange('2014-01-01', '2014-01-20'));
  34. f
  35. ------------
  36. 2014-01-06
  37. 2014-01-13
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement