/* * messwerte_metar2ready.sql * MeteoNews GmbH, Beat Vontobel, 2004-02-24 * * SQL-Query to... * transfer and convert data from metar to messwerte_ready * compute pressure tendency ptend * * 2004-04-15 Bugfix: Added check for 'old.ppnn IS NULL' for cases * where no last measurement row exists * 2004-09-29 Adapted from dbCopy to standalone SQL query * */ REPLACE INTO ms.messwerte_ready SELECT stations.mexs_id AS mexs_id, SUBSTRING(NOW() + 0, 1, 10) AS datum, 'metar' AS herkunft, IF(metar.dd IS NULL, -99, metar.dd) AS dd, IF(metar.ff IS NULL, -99, metar.ff) AS ff, IF(metar.fxx IS NULL, -99, metar.fxx) AS fxx, IF(metar.tt IS NULL, -99, metar.tt) AS tt, IF(metar.td IS NULL, -99, metar.td) AS td, NULL AS tc, IF(metar.rh IS NULL, -99, metar.rh) AS rh, IF(metar.ppnn IS NULL, -99, metar.ppnn) AS ppnn, IF( /* if we don't have a new measurement we keep */ /* the old value for ptend */ metar.datetime = old.messzeit, old.ptend, /* else we check if we really have two valid */ /* measurements to compute tendency, if not */ /* we return -99 */ IF( metar.ppnn IS NULL OR old.ppnn = -99 OR old.ppnn IS NULL, -99, /* we compute the pressure change */ /* normalized to the period of one */ /* hour */ ROUND( (metar.ppnn - old.ppnn) / (UNIX_TIMESTAMP(metar.datetime) - UNIX_TIMESTAMP(old.messzeit)) * 3600 ) ) ) AS ptend, -99 AS rrr, -99 AS ssd, wx AS ww, NULL AS bearbeitet, `datetime` AS messzeit FROM mn.metar INNER JOIN ms.stationen AS stations ON metar.id = stations.icao LEFT JOIN ms.messwerte_ready AS old USING (mexs_id) WHERE metar.datetime >= (NOW() - INTERVAL 3 HOUR)