Show
Ignore:
Timestamp:
03/27/08 20:51:08 (5 years ago)
Author:
Denish Patel <denish@omniti.com>
git-committer:
Denish Patel <denish@omniti.com> 1206651068 +0000
git-parent:

[0dbb5eb9b2c9f102370e5d34d527dde28d3588d4]

git-author:
Denish Patel <denish@omniti.com> 1206651068 +0000
Message:

updated functions and removed odd for rollups

Files:

Legend:

Unmodified
Added
Removed
Modified
Copied
Moved

  • sql/schema.sql

    r0dbb5eb r3a1a63f  
    165165 
    166166     
    167 CREATE FUNCTION stratcon.loading_dock_metric_numeric_s_whence_log() RETURNS trigger 
    168     AS $$ 
     167CREATE OR REPLACE FUNCTION stratcon.loading_dock_metric_numeric_s_whence_log()  
     168RETURNS trigger 
     169AS $$ 
    169170DECLARE 
    170171v_whence timestamptz; 
    171172BEGIN 
    172173IF TG_OP = 'INSERT' THEN 
    173     SELECT whence FROM stratcon.log_whence_s WHERE whence=NEW.whence 
     174    SELECT whence FROM stratcon.log_whence_s WHERE whence=date_trunc('H',NEW.WHENCE) + (round(extract('minute' from NEW.WHENCE)/5)*5) * '1 minute'::interval 
    174175     INTO v_whence; 
    175176   IF NOT FOUND THEN 
    176        UPDATE stratcon.log_whence_s SET whence=NEW.whence; 
     177       INSERT INTO  stratcon.log_whence_s VALUES(date_trunc('H',NEW.WHENCE) + (round(extract('minute' from NEW.WHENCE)/5)*5) * '1 minute'::interval); 
    177178   END IF; 
    178 ELSE 
    179         RAISE EXCEPTION 'Non-INSERT DML operation attempted on INSERT only table'; 
    180179END IF; 
    181180 
    …… 
    187186 
    188187 
    189  
    190188-- Generic rollup function (under progress) 
    191189 
    …… 
    200198v_min_whence TIMESTAMPTZ; 
    201199v_max_rollup_5 TIMESTAMPTZ; 
    202 v_max_rollup_60 TIMESTAMPTZ; 
     200v_cur_time TIMESTAMPTZ; 
    203201 
    204202BEGIN 
    …… 
    209207  select max(rollup_time) from  stratcon.rollup_matrix_numeric_5m  
    210208         INTO v_max_rollup_5;          
    211   
    212   select max(rollup_time) from  stratcon.rollup_matrix_numeric_60m  
    213          INTO v_max_rollup_60;  
     209  select now() 
     210         INTO v_cur_time; 
    214211          
    215212 IF v_max_rollup_5 IS NULL  THEN 
    …… 
    217214 END IF; 
    218215  
    219  IF v_max_rollup_60 IS NULL  THEN 
    220     v_max_rollup_60:=timestamp '2008-01-01 00:00:00'; 
    221   END IF; 
    222   
    223216          
    224217  IF v_min_whence >= v_max_rollup_5 THEN 
    …… 
    230223     -- HOURLY ROLLUP 
    231224      
    232      IF  date_trunc('H',v_min_whence)!= date_trunc('H',v_max_rollup_60) THEN 
     225     IF  extract('minutes' from v_cur_time)>55 and extract('minutes' from v_cur_time)<59 THEN 
    233226      
    234227       PERFORM stratcon.rollup_matrix_numeric_60m(v_min_whence); 
    …… 
    369362 
    370363COMMIT; 
     364