1 / 8

How does NEST read data from NMDB

How does NEST read data from NMDB. // Get the connection data from an external file $ fp = fopen (' connection_file ', 'r'); if($ fp ){ // username and password should be the first 2 lines of the file $ username = rtrim ( fgets ($ fp ));

ofira
Download Presentation

How does NEST read data from NMDB

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. How does NEST readdata from NMDB

  2. //Get the connection data from an external file $fp = fopen('connection_file', 'r'); if($fp){ //username and passwordshouldbe the first 2 lines of the file $username = rtrim(fgets($fp)); $password = rtrim(fgets($fp)); while(!feof($fp)){ $hosts[] = rtrim(fgets($fp)); } } fclose($fp); Connection to database Connection file is not stored in the same directory as the website: no access to external users if($username && $password && $hosts){ foreach($hosts as $host){ if(!$link){ $link = mysql_connect($host, $username, $password); }//try first db04,then db10, db20 etc… } if(!$link){die("Impossible to connect : " . mysql_error());} } else { print "Connectionparameterscould not beretrieved<br>"; } mysql_select_db('nmdb', $link) or die('Could not select database.');

  3. BUILD THE MYSQL QUERY Simple case SELECT start_date_time, measured_corr_for_efficiencyFROM KERG_oriWHERE start_date_time >= '2009-11-30 00:00:00'AND start_date_time <= '2009-11-30 23:59:00'ORDER BY start_date_time ASC Here we ask for the original corrected for efficiency data from KERG station between 2 dates: no table join and no average

  4. BUILD THE MYSQL QUERY Revised original case: table join SELECT o.start_date_time, CASE WHEN r.start_date_time IS NULLTHEN o.measured_corr_for_efficiencyELSE r.revised_corr_for_efficiencyEND AS corr_for_efficiencyFROM KERG_ori oLEFT JOIN KERG_rev r ON o.start_date_time = r.start_date_timeWHERE o.start_date_time >= '2009-11-30 00:00:00'AND o.start_date_time <= '2009-11-30 23:59:00'ORDER BY start_date_time ASC Here we ask for the revised corrected for efficiency data from KERG station between 2 dates. We need to look in 2 tables, original and revised. 2009-11-30 20:41:00 238.696 2009-11-30 20:42:00 238.079 2009-11-30 20:43:00 236.606 2009-11-30 20:44:00 252.01 2009-11-30 20:45:00 239.071 2009-11-30 20:46:00 237.839 2009-11-30 20:47:00 190.989 <=> 2009-11-30 20:44:00 237.50 <=> 2009-11-30 20:47:00 NULL

  5. BUILD THE MYSQL QUERY Average original data over 5 min: simplified version SELECT start_date_time, AVG( measured_corr_for_efficiency ) AS measured_corr_for_efficiencyFROM KERG_oriWHERE start_date_time >= '2009-11-30 00:00:00'AND start_date_time <= '2009-11-30 23:59:00'GROUP BY FLOOR( UNIX_TIMESTAMP (start_date_time) / 300 ) UNIX_TIMESTAMP: number of seconds since '1970-01-01 00:00:00' FLOOR (1.23) = 1 We group the start_date_time with the same « floor » result Ex: 969300s / 300 = 3231.00 will be in the same group as 969599s / 300 = 3231.99 but not 969601 BUT unix_timestamp does not work for dates before 1970

  6. BUILD THE MYSQL QUERY Average original data over 5 min: simplified version 2 SELECT start_date_time, AVG( measured_corr_for_efficiency ) AS measured_corr_for_efficiencyFROM KERG_oriWHERE start_date_time >= '2009-11-30 00:00:00'AND start_date_time <= '2009-11-30 23:59:00'GROUP BY FLOOR( (TIME_TO_SEC( TIME( start_date_time ) ) +86400 * TO_DAYS( start_date_time ) ) / ( 300 ) ) TIME function extract the hour part of start_date_time, Which is then converted to seconds. TO_DAYS gives the number of days since year 0 and works well for years after 1582. This number is converted to seconds and added to the first part. BUT weird behaviour of selected start_date_time OULU start_date_time 1HCOR_E 1964-04-01 00:00:00;107.164 1964-04-01 11:00:00;107.214 1964-04-01 17:00:00;108.050 1964-04-01 23:00:00;107.519 1964-04-02 01:00:00;106.831 1964-04-02 06:00:00;106.992 1964-04-02 12:00:00;107.775 1964-04-02 18:00:00;107.281 1964-04-03 00:00:00;107.317 OULU start_date_time 1HCOR_E 1964-04-01 00:00:00;107.164 1964-04-01 06:00:00;107.214 1964-04-01 12:00:00;108.050 1964-04-01 18:00:00;107.519 1964-04-02 00:00:00;106.831 1964-04-02 06:00:00;106.992 1964-04-02 12:00:00;107.775 1964-04-02 18:00:00;107.281 1964-04-03 00:00:00;107.317

  7. BUILD THE MYSQL QUERY Average original data over 5 min: full version SELECT DATE_ADD( '0000-01-01 00:00:00', INTERVAL 300 * FLOOR( (TIME_TO_SEC( TIME( start_date_time ) ) +86400 * ( TO_DAYS( start_date_time ) -1 ) ) / ( 300 ) ) SECOND ) AS start_date_time, AVG( measured_corr_for_efficiency ) AS measured_corr_for_efficiencyFROM KERG_oriWHERE start_date_time >= '2009-11-30 00:00:00'AND start_date_time <= '2009-11-30 23:59:00'GROUP BY FLOOR( (TIME_TO_SEC( TIME( start_date_time ) ) +86400 * TO_DAYS( start_date_time ) ) / ( 300 ) ) DATE_ADD(date,INTERVAL N SECOND) will add N seconds to date (-1 to account for start_date of date_add)

  8. BUILD THE MYSQL QUERY Averaged revised data SELECT DATE_ADD( '0000-01-01 00:00:00', INTERVAL 300 * FLOOR( (TIME_TO_SEC( TIME( subq.start_date_time ) ) +86400 * ( TO_DAYS( subq.start_date_time ) -1 ) ) / ( 300 )) SECOND ) AS start_date_time, AVG( subq.corr_for_efficiency ) AS corr_for_efficiencyFROM (SELECT o.start_date_time, CASE WHEN r.start_date_time IS NULL THEN o.measured_corr_for_efficiencyELSE r.revised_corr_for_efficiencyENDAS corr_for_efficiencyFROM KERG_ori oLEFTJOIN KERG_rev r ON o.start_date_time = r.start_date_time WHERE o.start_date_time >= '2009-11-30 00:00:00'AND o.start_date_time <= '2009-11-30 23:59:00'ORDERBY start_date_time ASC ) AS subqGROUPBYFLOOR( (TIME_TO_SEC( TIME( subq.start_date_time ) ) +86400 * TO_DAYS( subq.start_date_time ) ) / ( 300 ) )ORDERBY start_date_time ASC The query for revised data is used a subquery in the average query $rs = mysql_query($query);

More Related