Advertisement
Guest User

Untitled

a guest
Jun 22nd, 2011
163
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
SQL 0.94 KB | None | 0 0
  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)
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement