SHARE
TWEET

Untitled

a guest Jun 22nd, 2011 96 Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. REPLACE FUNCTION next_cm(now TIMESTAMP WITH TIME zone, months INTEGER) RETURNS TIMESTAMP WITH TIME zone AS $$
  2. << outerblock >>
  3. DECLARE
  4.     m_offset INTEGER;
  5. BEGIN
  6.     m_offset := months;
  7.     IF (get_cm(now) + ('17:00'::INTERVAL)) < now THEN
  8.         m_offset := m_offset + 1;
  9.     END IF;
  10.     RETURN get_cm(date_trunc('month',now) + (m_offset||'month')::INTERVAL) + ('16:30'::INTERVAL);          
  11. END;
  12. $$ LANGUAGE plpgsql;
  13.  
  14. CREATE OR REPLACE FUNCTION get_cm(now TIMESTAMP WITH TIME zone) RETURNS TIMESTAMP WITH TIME zone AS $$
  15. << outerblock >>
  16. DECLARE
  17. BEGIN
  18.     RETURN date_trunc('month', now) + ((((8 + 4 - date_part('isodow',date_trunc('month', now)))::integer%7) + 14)||'days')::INTERVAL;
  19. END;
  20. $$ LANGUAGE plpgsql;
  21.  
  22.  
  23. -- Ergebnis:
  24.  
  25. # SELECT next_cm(now(),0), next_cm(now),1);
  26.          next_cm         |        next_cm        
  27. ------------------------+------------------------
  28.  2011-07-15 16:30:00+02 | 2011-08-19 16:30:00+02
  29. (1 ROW)
RAW Paste Data
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy. OK, I Understand
Not a member of Pastebin yet?
Sign Up, it unlocks many cool features!
 
Top