utils pgsql dump dados

utils.sql

-- BASE DE DADOS LEPTEN

SELECT * FROM tb_history,tb_station WHERE tb_history.id_station=tb_station.id_station AND tb_station.nm_station='COPEL_6' AND hr_data>='2010-08-01' AND hr_data<='2010-09-01' ORDER BY hr_data ASC

SELECT hr_data FROM tb_history,tb_station WHERE tb_history.id_station=tb_station.id_station AND tb_station.nm_station='COPEL_1' AND hr_data>='2010-09-01 09:00:00' AND hr_data<='2010-09-01 16:00:00' AND ((to_char(hr_data,'MI') like  '%30%' or  to_char(hr_data,'MI') like '%00%'))  ORDER BY hr_data ASC

-- MEDIAS
SELECT  avg(vl_air_temp_avg),sum(vl_rain_yearly),max(vl_solar_avg),max(vl_wind_speed_max),avg(vl_wind_speed_avg),min(vl_wind_speed_avg) FROM tb_history,tb_station WHERE tb_history.id_station=tb_station.id_station AND tb_station.nm_station='COPEL_4' AND hr_data BETWEEN '2011-05-01' and '2011-05-31 23:50:00' AND tb_history.vl_solar_avg<>-999999 AND  tb_history.vl_wind_speed_avg<>-999999 AND  tb_history.vl_air_temp_avg<>-999999  

-- RAJADAS
SELECT ,max(vl_wind_speed_max),avg(vl_wind_speed_avg) FROM tb_history,tb_station WHERE tb_history.id_station=tb_station.id_station AND tb_station.nm_station='CHESF_6' AND hr_data BETWEEN '2011-01-01' AND '2011-02-01' 

-- BASE DE DADOS PCD

SELECT * FROM pcdmet,pcdagro,pcdhidro,pcdqagua WHERE nomepcd='30151' and datahora>='2010-08-30' ORDER BY datahora DESC
SELECT * FROM pcdhidro WHERE nomepcd='31983' and datahora>='2010-12-30' ORDER BY datahora DESC limit 100
SELECT * FROM pcdmet WHERE nomepcd='30151' and datahora>='2010-08-30' ORDER BY datahora DESC
SELECT * FROM pcdmet,gestpcd_2 WHERE pcdmet.nomepcd=gestpcd_2.estacao AND gestpcd_2.estacao='CHESF_2' AND datahora BETWEEN '2010-09-07' AND '2011-03-31' ORDER BY datahora DESC
select a.datahora,estacao,a.dirvento,a.velventomax,a.velvento,a.pressaoatm,a.radsolacum,a.tempar,a.umidrel,a.pluvio,a.precipitacao3hrs,a.flagumidrel,a.flagdirvento,a.flagvelvento,
a.flagvelventomax,a.flagpressao,a.flagtempar,a.flagprecipitacao,a.pressaored,a.pressaoqnh,a.temparmax,a.nivregua,
b.estacao from public.pcdmet as a left join public.gestpcd_2 as b on a.nomepcd = b.id order by 1 desc limit 200

-- BASE DE DADOS INMET

select data.value from "INMET".data, "INMET".sample, "INMET".variable, "INMET".station where data.sample = sample.id and sample.datetime > '20100901 000000' and sample.datetime < '20100930 000000' and data.variable=variable.id and variable.name = 'Precipitacao' and data.station=station.id and station.name='FLORIANOPOLIS' and station.station_type = 2

SELECT sample.datetime,variable.name,data.value FROM "INMET".data, "INMET".sample, "INMET".variable, "INMET".station WHERE data.sample = sample.id AND extract(month from sample.datetime)=9 AND sample.datetime >= '20100101 000000' AND sample.datetime <= '20101231 000000' AND data.variable=variable.id AND variable.name = 'Precipitacao' AND data.station=station.id AND station.name='FLORIANOPOLIS' AND station.station_type = 2

SELECT
     to_char(HR_DATA ,'dd/MM/YYYY') AS dia_mes_ano,
     --to_char(hr_data,'HH:MI:SS') AS hr_min_sec,
       vl_wind_speed_avg,
     vl_wind_dir_std_dev,
     vl_wdgust,
     vl_wgust_h,
     vl_wgust_m,
     vl_wgust_s,
     vl_wind_dir_avg,
     vl_wind_speed_std_dev,
     vl_wind_speed_max,
     vl_wind_speed_min,
     '-999999' as VN1,
     '-999999' as VN2,
     vl_solar_avg,
     vl_air_temp_avg,
     vl_rh_avg,
     vl_press_avg,
     vl_rain_yearly,
     vl_batt_volt,
     vl_current,
     vl_int_temp,
     id_station,
     (select nm_station from tb_station where id_station = a.id_station limit 1) as nm_station,
     '1' as contador

FROM tb_history as a
WHERE id_station in(44) 
order by HR_DATA ASC
limit 10

-- XML_CEMIG

SELECT
	id_station,
	To_char(hr_data,'dd/MM/yyyy') AS dt_leitura,
	To_char(hr_data,'hh24:mi:ss') AS hr_leitura,
	vl_air_temp_avg, vl_batt_volt, vl_press_avg,
	vl_rain_yearly,
	vl_rh_avg,
	vl_solar_avg,
	vl_wind_dir_avg,
	vl_wind_dir_max,
	vl_wind_speed_avg,
	vl_wind_speed_max,
	vl_wind_speed_min,

(SELECT nm_station FROM tb_station WHERE id_station = a.id_station limit 1) AS nm_station,
(SELECT cd_lat FROM tb_station WHERE id_station = a.id_station limit 1) AS latitude,
(SELECT cd_long FROM tb_station WHERE id_station = a.id_station limit 1) AS longitude,
	id_qualified_data FROM PUBLIC.tb_qualified_data AS a

	WHERE flag = 0 AND codigodoerro = 0 AND id_station IN(85,92,86,90,89,87,88)
	ORDER BY to_char(dthist,'dd/MM/yyyy') DESC, hr_data DESC limit 1000

-- FLAG == 0 significa que o dado ainda não foi exportado!
-- codigodoerro == 0 significa que o dado é bom
-- codigodoerro == 1 significa que o dado é ruim
-- FLAG == 1 significa que o dado já foi exportado!