CREATE OR REPLACE FUNCTION set_top_of_hour() RETURNS trigger AS $set_top_of_hour$
       
       declare
       old_row_id int;
       old_date_minute int;
       new_date text;
       comp_date_hour int;
       new_date_minute int;
       new_comp_date timestamp;
       new_comp_date_min timestamp;
       new_comp_date_max timestamp;
       
       begin
         
         --see whether to set comparison hour to this hour or next
         select to_char(new.m_date,'YYYY-MM-DD') into new_date;
         select to_char(new.m_date,'HH24') into comp_date_hour;  
         select to_char(new.m_date,'MI') into new_date_minute;
   
         if (new_date_minute > 30) then
             --go to the next hour unless close to midnight, then go to next day, 0 hour
             if (comp_date_hour <> 23) then 
                 comp_date_hour = comp_date_hour + 1;
             else
                 comp_date_hour = 0;
                 select to_char((new.m_date + interval '1 day'),'YYYY-MM-DD') into new_date;
             end if;
         end if;
       
         new_comp_date = new_date || ' ' || comp_date_hour || ':00:00';
         
         select to_char((new_comp_date - interval '30 minutes'),'YYYY-MM-DD HH24:MI:SS') into new_comp_date_min;
         select to_char((new_comp_date + interval '30 minutes'),'YYYY-MM-DD HH24:MI:SS') into new_comp_date_max;
         
         --new_comp_date_min = new_date || ' ' || comp_date_hour-1 || ':30';
         --new_comp_date_max = new_date || ' ' || comp_date_hour || ':30';
       
   
         --RAISE NOTICE 'new_comp_date = %', new_comp_date;
         --RAISE NOTICE 'new_comp_date_min = %', new_comp_date_min;
         --RAISE NOTICE 'new_comp_date_max = %', new_comp_date_max;
       
         --assign d_report_hour, each hour is associated with the half hour before and after the top of that hour
         update multi_obs set d_report_hour = new_comp_date where row_id = new.row_id;
       
         --select existing top_of_hour row(if exists) within hour comparison
         select into old_row_id,old_date_minute row_id,to_char(m_date,'MI')  
           from multi_obs
           where m_type_id = new.m_type_id and sensor_id = new.sensor_id and d_top_of_hour = 1
           and m_date > new_comp_date_min and m_date <= new_comp_date_max;
       
         --if not found then
         --	raise notice 'select not found';
         --end if;
         
         --decide whether new row is closer to top_of_hour or not
         if (old_row_id is null) then
           update multi_obs set d_top_of_hour = 1 , d_report_hour = new_comp_date where row_id = new.row_id;
         elsif (new_date_minute <= 30) then
           if (old_date_minute > new_date_minute) then
             update multi_obs set d_top_of_hour = 1 where row_id = new.row_id;
             update multi_obs set d_top_of_hour = null where row_id = old_row_id;
           end if;
         elsif (new_date_minute > 30) and (old_date_minute <> 0) then
           if (old_date_minute < new_date_minute) then
             update multi_obs set d_top_of_hour = 1 where row_id = new.row_id;
             update multi_obs set d_top_of_hour = null where row_id = old_row_id;
           end if;    
         end if;
       
         return new;
       end
$set_top_of_hour$ LANGUAGE plpgsql;

CREATE TRIGGER set_top_of_hour AFTER INSERT ON multi_obs
    FOR EACH ROW EXECUTE PROCEDURE set_top_of_hour();
    
CREATE INDEX i_multi_obs_top_hour ON multi_obs (d_top_of_hour,d_report_hour);