个人中心
gpt4 book ai didi

mysql - Cron 设置为使用 crontab 每 5 分钟插入一次数据到表中，但仅在整点插入 (1 :00) not every 5 minutes as expected

转载 作者：行者123 更新时间：2023-11-29 22:16:01 25 4
gpt4 key购买 nike

此脚本用于插入来自气象站的风数据。数据来自在线文本文件。气象站每 5 分钟报告并更新其文本文件。 http://www.ndbc.noaa.gov/data/realtime2/IMGP4.txt

Crontab 设置为每五分钟运行一次，但每小时只插入一次数据。作为测试模式，我们将 crontab 更改为每 :15 分钟运行一次，它正在这样做。假设它运行时间为 1:15。过去 1:00、1:05、1:10 和 1:15 的数据会插入表中，并且它将停止插入新数据(1:30、1:45、2:00 没有任何数据)，直到下一个小时将在 2:15 再次运行，并执行相同操作(插入过去的数据 2:00、2:05、2:10 和 2:15)。

数据示例

    #YY  MM DD hh mm WDIR WSPD GST  WVHT   DPD   APD MWD   PRES  ATMP  WTMP  DEWP  VIS PTDY  TIDE
    2015 07 13 17 20 120  7.7 11.3    MM    MM    MM  MM 1016.0  29.9    MM    MM   MM   MM    MM
    2015 07 13 17 15 120  7.7 11.8    MM    MM    MM  MM 1016.0  29.8    MM    MM   MM   MM    MM
    2015 07 13 17 10 110  7.7 11.8    MM    MM    MM  MM 1016.0  29.7    MM    MM   MM   MM    MM                   
    2015 07 13 17 05 110  7.2 11.3    MM    MM    MM  MM 1016.0  29.7    MM    MM   MM   MM    MM                   
    2015 07 13 17 00 120  7.7 11.8    MM    MM    MM  MM 1016.0  29.6    MM    MM   MM -0.8    MM

日志显示问题出在以下几行:

        $date = $realvalues[0]."-".$realvalues[1]."-".$realvalues[2];
        $time = $realvalues[3].":".$realvalues[4].":00";
        $wdir = str_replace('MM','',$realvalues[5]);
        $spd = str_replace('MM','',$realvalues[6])*1.94384449;
        $gust = str_replace('MM','',$realvalues[7])*1.94384449;
        $atem = str_replace('MM','',$realvalues[14]);
        $timeval = gmmktime($realvalues[3],$realvalues[4],"00",$realvalues[1],$realvalues[2],$realvalues[0]);
        //echo $timeval." <= ".$timemax.'<br>';$control++;
        if($timeval <= $timemax){unset($realvalues);continue;}
        //echo $realvalues[3].",".$realvalues[4].","."00".",".$realvalues[1].",".$realvalues[2].",".$realvalues[0].'<br>';
        //echo $info2['MAX(date)']." == ".$date." && ".$maxTime[0]."==".$realvalues[3]." && ".$maxTime[1]."==".$realvalues[4]."<br/>";
        if($info2['MAX(date)']<$date){
            $query = sprintf("INSERT INTO `ndbc` (`station_id`,`date`,`time`,`Wind Direction (-4m)`,`Wind Speed (-4m)`,`Air Temperature (-3m)`,`Wind Gust (-4m)`) VALUES ('%s', '%s', '%s', '%s', '%s', '%s', '%s')",$info['id'], $date, $time, $wdir, $spd, $atem, $gust);echo $query.'<br/>';//die();
            mysql_query($query);

创建表脚本:

        <?php 
        require_once("../connections/connection_index.php");
        function getData($id, &$xdata, &$ydata, &$dirdata, &$yesterday,     &$future, &$realStation, &$xdata2, &$ydata2, &$dirdata2, &$gust, &$gust2){  
    $query = sprintf("SELECT `type`,`wind station relation`,`wind platform relation`,`name`,`platform` FROM `stations` WHERE id = '".$id."'");//echo $query;die();
    $result = mysql_query($query);
    $info = mysql_fetch_assoc($result);
    $realStation = $info['name'];   
    $now_datetime = gmdate("Y-m-d H:00:00");
    $val = explode(' ',$now_datetime);
    $time = explode(':',$val[1]);
    $date = explode('-',$val[0]);
    $timestamp = gmmktime($time[0], 0, 0, $date[1], $date[2], $date[0]);
    $yesterday = $timestamp-3600*16;
    $future = $timestamp;//+3600*18;
    $old_datetime = gmdate("Y-m-d H:00:00",$yesterday);
    $old_val = explode(' ',$old_datetime);
    $future_datetime = gmdate("Y-m-d H:00:00",$future);
    $future_val = explode(' ',$future_datetime);
    $query = sprintf("SELECT `date`,`time`,`Wind Speed (-4m)`,`Wind         Direction (-4m)`,`Wind Gust (-4m)` FROM `".$info['platform']."` WHERE station_id = '".$id."' AND date BETWEEN '%s' AND '%s'",$old_val[0],$future_val[0]);//echo $query;die();
    $result2 = mysql_query($query);
    while($info2 = mysql_fetch_assoc($result2)){
        $time = explode(':',$info2['time']);
        $date = explode('-',$info2['date']);
        $timestamp = gmmktime($time[0], 0, 0, $date[1], $date[2], $date[0]);
        if($info['type']=='observation'){
            $xdata[]= $timestamp-3600*4;
            $ydata[]= $info2['Wind Speed (-4m)'];
            $dirdata[]= $info2['Wind Direction (-4m)'];
            $gust[]= $info2['Wind Gust (-4m)'];
        }
        else{
            $xdata2[]= $timestamp-3600*4;
            $ydata2[]= $info2['Wind Speed (-4m)'];
            $dirdata2[]= $info2['Wind Direction (-4m)'];
            $gust2[]= $info2['Wind Gust (-4m)'];
        }
    }
    if(!empty($info['wind station relation']) && !empty($info['wind station relation'])){
        if($info['type']=='observation'){
            $type='forecast';
        }
        else{
            $type='observation';
        }
        $query = sprintf("SELECT `id` FROM `stations` WHERE `code` = '".$info['wind station relation']."' AND type = '".$type."'");//echo $query;die();
        $result3 = mysql_query($query);
        $info3 = mysql_fetch_assoc($result3);
        $query = sprintf("SELECT `date`,`time`,`Wind Speed (-4m)`,`Wind Direction (-4m)` FROM `".$info['wind platform relation']."` WHERE station_id = '".$info3['id']."' AND date BETWEEN '%s' AND '%s'",$old_val[0],$future_val[0]);//echo $query;die();
        $result3 = mysql_query($query);
        while($info3 = mysql_fetch_assoc($result3)){
            $time = explode(':',$info3['time']);
            $date = explode('-',$info3['date']);
            $timestamp = gmmktime($time[0], 0, 0, $date[1], $date[2], $date[0]);
            if($type=='observation'){
                $xdata[]= $timestamp-3600*4;
                $ydata[]= $info3['Wind Speed (-4m)'];
                $dirdata[]= $info3['Wind Direction (-4m)'];
                $gust[]= $info3['Wind Gust (-4m)'];
            }
            else{
                $xdata2[]= $timestamp-3600*4;
                $ydata2[]= $info3['Wind Speed (-4m)'];
                $dirdata2[]= $info3['Wind Direction (-4m)'];
                $gust2[]= $info3['Wind Gust (-4m)'];
            }
        }
    }
        }
        function cmp($a, $b) {
        return strcmp($a[0], $b[0]);
        }
       function getticks(&$Ticks,$yesterday,$future){
    $value=$yesterday;
    $i=0;
    $Ticks=array();
    while($value<=$future){
        $Ticks[$i]=$value;
        $value=$value+3600;
        $i++;
    }
}

        $station=$_GET["ID"];
        $numOfValues=12;
          getData($station,$xdata,$ydata,$dirdata,$yesterday,$future,$realStation,$xdata2,$ydata2,$dirdata2,$gust,$gust2);
        getticks($Ticks,$yesterday,$future);
        $observation = array(array());
        $j=0;
        foreach($xdata as $x){
        $i=0;
        $observation[$j][$i]=$x;
        $i++;
        $observation[$j][$i]=$ydata[$j];
        $i++;
        $observation[$j][$i]=$dirdata[$j];
        $i++;
        $observation[$j][$i]=$gust[$j];
       $j++;
       }
       usort($observation, "cmp");
       $realxData = array();
       $realyData = array();
       $realdirData = array();
       $realgustData = array();
       $j=0;
       $TestVar=false;
       foreach($observation as $real){
       if($TestVar && $real[0]!=$realxData[$j-1]){
        $realxData[$j]=$real[0];
        $realyData[$j]=number_format($real[1],1);
        $realdirData[$j]=(int)($real[2]);
        $realgustData[$j]=$real[3];
        $j++;}
        if($yesterday<=$real[0] && $TestVar==false){
        $realxData[$j]=$real[0];
        $realyData[$j]=number_format($real[1],1);
        $realdirData[$j]=(int)($real[2]);
        $realgustData[$j]=$real[3];
        $j++;
        $TestVar=true;}
     }
       if(count($realxData)>12){
    array_splice($realxData,0,1);
    array_splice($realyData,0,1);
    array_splice($realdirData,0,1);
    array_splice($realgustData,0,1);
    /*unset($realxData[0]);
    unset($realyData[0]);
    unset($realdirData[0]);*/
 }
         /*$last_time=$real[0];
        $forecast = array(array());
        $j=0;
        foreach($xdata2 as $x){
        $i=0;
  $forecast[$j][$i]=$x;
  $i++;
  $forecast[$j][$i]=$ydata2[$j];
  $i++;
  $forecast[$j][$i]=$dirdata2[$j];
    $i++;
  $forecast[$j][$i]=$gust2[$j];
  $j++;
}
usort($forecast, "cmp");
$realxData2 = array();
$realyData2 = array();
$realdirData2 = array();
$realgustData2 = array();
$j=0;
$TestVar=false;
foreach($forecast as $real){
         if($last_time < $real[0]){
          if($TestVar && $real[0]!=$realxData2[$j-1]){
            $realxData2[$j]=$real[0];
            $realyData2[$j]=number_format($real[1],1);
            $realdirData2[$j]=(int)($real[2]);
            $realgustData2[$j]=$real[3];
            $j++;}
        if($yesterday<=$real[0] && $TestVar==false){
            $realxData2[$j]=$real[0];
            $realyData2[$j]=number_format($real[1],1);
            $realdirData2[$j]=(int)($real[2]);
            $realgustData2[$j]=$real[3];
            $j++;
            $TestVar=true;}
    }
}*/
/*echo '<pre> OBSERVATION X: '; print_r($realxData); echo '</pre>';
echo '<pre> OBSERVATION Y: '; print_r($realyData); echo '</pre>';
echo '<pre> OBSERVATION DIR: '; print_r($realdirData); echo '</pre>';
echo '<pre> OBSERVATION Gust: '; print_r($realgustData); echo '</pre>';
echo '<pre> FORECAST X: '; print_r($realxData2); echo '</pre>';
echo '<pre> FORECAST Y: '; print_r($realyData2); echo '</pre>';
echo '<pre> FORECAST DIR: '; print_r($realdirData2); echo '</pre>';
echo '<pre> FORECAST gust: '; print_r($realgustData2); echo '</pre>';
die();*/
mysql_close($connection);
?>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>Table</title>
<style type="text/css">
#data_type{
    background-color:#87AFC7;
    font-weight:bold;
    padding:0px;
    margin:0px; 
}
        #legend{
        background-color:#CCC;
        text-align:center;
        font-weight:bold;
        font-size:larger;
        padding:0px;
        margin:0px; 
}
#name{
    background-color:#CCC;
    text-align:center;
    font-weight:bold;
    padding:0px;
    margin:0px; 
}
#dates{
    background-color:#CCC;
    text-align:center;
    font-weight:bold;
    padding:0px;
    margin:0px;
}
#hours{
    background-color:#BDBDBD;
    text-align:center;
    width:70px;
    padding:0px;
    margin:0px;
}
#data_ob{
    background-color: #9F3;
    text-align:center;
    padding:0px;
    margin:0px;
}
#data_fore{
    background-color: #0F9;
    text-align:center;
    padding:0px;
    margin:0px;
}
</style>
</head>
<body>
<table cellspacing="1"  style="margin:auto;">
    <tr>
    <td id="data_type">Legend:</td>
    <td id="legend" colspan="<?php echo $numOfValues ?>"><span style="background-color: #9F3;">&#160;Observation&#160;</span>
    <!--&#160;|&#160;
    <span style="background-color: #0F9;">&#160;Forecast&#160;</span>
    <span><img src="/app/elmer_test/icon.png" width="24" height="24"/>Missing Data</span>--></td>
    </tr><tr>
  <?php
    echo "<td id=\"data_type\">Name:</td>";
    echo "<td id=\"name\" colspan=\"".$numOfValues."\">".$realStation."</td>";
    ?>
  </tr><tr>
  <?php 
    //create days
    echo "<td id=\"data_type\">Date:</td>";
    $control=0;
    $time=$yesterday;
    $day=gmdate("d",$time);
    //observation
    foreach($realxData as $time){
        if(gmdate("d",$time) == $day){
            $control++;
        }
        else{
            echo "<td id=\"dates\" colspan=\"".$control."\"><div style=\"width:80px; margin:auto;\">".gmdate("Y-m-d",$time-3600)."</div></td>";
            $last_day=gmdate("d",$time-3600);
            $control=1;         
        }
        $day = gmdate("d",$time);
    }
    //new   
    if($control!=0){
        //$control++;
        echo "<td id=\"dates\" colspan=\"".$control."\"><div style=\"width:90px; margin:auto;\">".gmdate("Y-m-d",$time)."</div></td>";
    }
    //forecast
    /*$control2=0;
    $store_time=$time;
    foreach($realxData2 as $time){
        if($control2==0){
            if(gmdate("d",$time) != $day){
                echo "<td id=\"dates\" colspan=\"".$control."\">".gmdate("Y-m-d",$store_time)."</td>";
                $last_day=gmdate("d",$store_time);
                $control=0;     
            }
            $control2=1;
            $day = gmdate("d",$time);
        }
        if(gmdate("d",$time) == $day){
            $control++;
        }
        else{
            echo "<td id=\"dates\" colspan=\"".$control."\">".gmdate("Y-m-d",$time-3600*3)."</td>";
            $last_day=gmdate("d",$time-3600*3);
            $control=0;         
        }
        $day = gmdate("d",$time);           
    }           
    $control++;
    if($last_day != $day){
        echo "<td id=\"dates\" colspan=\"".$control."\">".gmdate("Y-m-d",$time)."</td>";
    }*/
    ?>
  </tr><tr>
  <?php 
    echo "<td id=\"data_type\">Time: (Local PR):</td>";
    //create observation hours
    $control=0;
    foreach($realxData as $time){
        echo "<td id=\"hours\">".gmdate("H",$time)."</td>";
        $control++;
    }
    //create forecast hours
    /*$control=0;
    foreach($realxData2 as $time){
        echo "<td id=\"hours\">".gmdate("H",$time)."</td>";
        $control++;
    }*/
    ?>
  </tr><tr>
  <?php
    //create observation data
    echo "<td id=\"data_type\">Wind Speed (kt):</td>";
    $control=0;
    foreach($realxData as $time){
            echo "<td id=\"data_ob\">".$realyData[$control]."</td>";
        $control++;
    }
    //create forecast data
    /*$control=0;
    foreach($realxData2 as $time){
        echo "<td id=\"data_fore\">".$realyData2[$control]."</td>";
        $control++;
    }*/
    ?>
  </tr><tr>
  <?php
    //create observation direction
    echo "<td id=\"data_type\">Wind Direction (deg):</td>";
    $control=0;
    foreach($realxData as $time){       
        echo "<td id=\"data_ob\">".$realdirData[$control]."</td>";
        $control++;
    }
    //create forecast direction
    /*$control=0;
    foreach($realxData2 as $time){
        echo "<td id=\"data_fore\">".$realdirData2[$control]."</td>";
        $control++;
    }*/
    ?>
  </tr><tr>
  <?php
    //create observation vector
    echo "<td id=\"data_type\">Wind Vector:</td>";
    $control=0;
    foreach($realxData as $time){
        if($realdirData[$control]<=360 && $realdirData[$control]>=0){
            if(((($realdirData[$control])+180)%360)<10){
                echo "<td id=\"data_ob\"><img src=\"/app/elmer_test/new_red_arrow/s0".((($realdirData[$control])+180)%360).".png\"/></td>";
            }
            else{
                echo "<td id=\"data_ob\"><img src=\"/app/elmer_test/new_red_arrow/s".((($realdirData[$control])+180)%360).".png\"/></td>";
            }
        }
        else{
            echo "<td id=\"data_ob\"><img src=\"/app/elmer_test/icon.png\" width=\"24\" height=\"24\"/></td>";
        }
        $control++;
    }
    //create forecast vector
    /*$control=0;
    foreach($realxData2 as $time){
        if($realdirData2[$control]<=360 && $realdirData2[$control]>=0){
            if(((($realdirData2[$control])+180)%360)<10){
                echo "<td id=\"data_fore\"><img src=\"/app/elmer_test/new_red_arrow/s0".((($realdirData2[$control])+180)%360).".png\"/></td>";
            }
            else{
                echo "<td id=\"data_fore\"><img src=\"/app/elmer_test/new_red_arrow/s".((($realdirData2[$control])+180)%360).".png\"/></td>";
            }
        }
        else{
            echo "<td id=\"data_fore\"><img src=\"/app/elmer_test/icon.png\" width=\"24\" height=\"24\"/></td>";
        }
        $control++;
    }*/
    ?>
  </tr>
</table>
</body>
</html>

它确实在 1:00“整个”小时正确插入数据。但不会每五分钟插入一次数据(1:05、1:10、1:15……我得到这个Log. undefined variable :/insert/ndbc.php 第 43 - 49 行中的实值

    43  $date = $realvalues[0]."-".$realvalues[1]."-".$realvalues[2];
        $time = $realvalues[3].":".$realvalues[4].":00";
        $wdir = str_replace('MM','',$realvalues[5]);
        $spd = str_replace('MM','',$realvalues[6])*1.94384449;
        $gust = str_replace('MM','',$realvalues[7])*1.94384449;
        $atem = str_replace('MM','',$realvalues[14]);
        $timeval = gmmktime($realvalues[3],$realvalues[4],"00",$realvalues[1],$realvalues[2],$realvalues[0]);
        //echo $timeval." <= ".$timemax.'<br>';$control++;
        if($timeval <= $timemax){unset($realvalues);continue;}
        //echo $realvalues[3].",".$realvalues[4].","."00".",".$realvalues[1].",".$realvalues[2].",".$realvalues[0].'<br>';
        //echo $info2['MAX(date)']." == ".$date." && ".$maxTime[0]."==".$realvalues[3]." && ".$maxTime[1]."==".$realvalues[4]."<br/>";
        if($info2['MAX(date)']<$date){
            $query = sprintf("INSERT INTO `ndbc` (`station_id`,`date`,`time`,`Wind Direction (-4m)`,`Wind Speed (-4m)`,`Air Temperature (-3m)`,`Wind Gust (-4m)`) VALUES ('%s', '%s', '%s', '%s', '%s', '%s', '%s')",$info['id'], $date, $time, $wdir, $spd, $atem, $gust);echo $query.'<br/>';//die();
            mysql_query($query);
            unset($realvalues);
            continue;

最佳答案

在 cron 中每 5 分钟运行一次作业:

*/5 * * * */path/to/script.php

关于mysql - Cron 设置为使用 crontab 每 5 分钟插入一次数据到表中，但仅在整点插入 (1 :00) not every 5 minutes as expected，我们在Stack Overflow上找到一个类似的问题： https://stackoverflow.com/questions/31203965/

25 4 0
文章推荐： MySQL查询反转数据
文章推荐： android - 用于数据访问代码生成的 sqlitegen
文章推荐： android - 我可以在运行时全局增加所有 UI 小部件的文本大小吗？
文章推荐： mysql - 对于一个引擎为InnoDB的表，最坏情况下会锁定多少行？
行者123
个人简介

我是一名优秀的程序员,十分优秀！

滴滴打车优惠券免费领取
滴滴打车优惠券
全站热门文章
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com