I'm having a blonde moment.

Problem Statement: So I have some timestamped data in a file which constantly at arbitrary intervals gets updated, and I just want to filter out data for the previous full hour (i.e. if the time now is 14:35, I mean for the period between 13:00-14:00, similarly if the time 00:03 it will be 23:00-00:00). Once this data is obtained I want to shoot it off to the mysql table.Once complete this will be done automatically with crontab

What I want to accomplish: I would ideally like to get the data of the last full hour and send it over to the mysql table. If there is nothing for that hour I want it to send one message stating "there is no updates this hour". So essentially I thought it was an if/else statement if something new do this else no update. But this isn't working.

What I think is happening: The code checks for the last full hour, and sends it off to the mysql table. But also checks all the other records, and since the older records aren't in the last hour period they are put into the 'else' category and I get a lot of "there is no updates this hour". Q: How can I surpass this ?

This is my code (I've removed some things - so syntacally it might not be correct):

#   Adds all the transactions for the past full hour into a log file
#   named log.csv
#   input file:     mqtt.csv
#   output file:    outfile.csv

current_time_hr=`date +%H`      #Get the current hour only
current_time_date=`date +%F`    #Get the full date YYYY-MM-DD
current_time_full=$current_time_date' '$current_time_hr':00:00'  #Concatenate the date & current hour
current_time=`date -d "$current_time_full" +%s`     #convert the time to seconds
echo "$current_time"                    #Print out
period=3600                                     #3600 seconds = 60 min = 1 hour
one_hr_before=$((current_time-period))    # subtrace one hour in seconds - to get one hour ago in seoonds 
echo $one_hr_before                     #Print out

count=0                             #Initialize the counteR
out_file_name=outfile.csv               #Output file

`rm -f $out_file_name`              #Rmv to avoid appending

#Filter below
cat /DIR/SOME_DIR/mqtt.csv | grep SX | grep ',scan' | grep -v 'HDCU.*HDCU' | grep -v 'Sensor,Module' | grep '^[0-9]*,e,gf,STACKEXCHANGEe,HDCU.*,d,scan,.*,.*,.*,.*,.*,.*' | sed 's/,H[0-9][0-9]/,/g' | tail -n100  >>$out_file_name

cat $out_file_name | ( while read line
    echo $line    #Print out

    # This is to convert $line into an array named "awk_var_array"
    #----- ----- ----- ----- ----- ---------- ----- ---------- ----- -----
    read -a awk_var_array <<< "${line}"

    time_date=`date -d "@$record_time" +%F`' '`date -d "@$record_time" +%T`


    # Var is created & initialized; And is used to concatenate machine name parts 
    # in the consequtive fields before direction (IN/OUT) field
    k=$((${#awk_var_array[@]}-1)) # k is the last element of the array
    j=11                  # j is the element where the part name  starts
    while [ $j -lt $k ]       # do a for a loop with while
        echo "j:$j k:$k  ${awk_var_array[$j]}" 
                var=$var' '$new_var


    part_name=${var:1} #gets rid of the first character as this is a ' ';

    echo -e  "\t\t\t\t\t\t\tvar $part_name"

        count=$((count+1))   #Increase counter

        echo "c-o-u-n-t $count"   #Print out

    full_date=`date -d "@$record_time" +%F`' '`date -d "@$record_time" +%T`
    echo "FD:$full_date"
    dir=`echo $dir | sed 's/*//g'`
    echo "FD:$full_date, SID:$scan_id, PartName:$part_name, DIR:$dir"

  if [ "$record_time" -lt "$current_time" ] && [ "$record_time" -ge "$one_hr_before" ]   #Bouncries for permissible data count

        dbq="INSERT INTO mytable.table (time, part_number, direction, stock_point_name, scan_id, ignored) VALUES ('$time_date', '$part_name', '$dir', '$container', '$scan_id', '0');"


        dbq="INSERT INTO mytable.table (part_number) VALUES ('there is no updates this hour');"


mysql -uUSER -p'PASSWORD' << EOF


echo "count: $count"            #Print out
time_stamp_date=`date +%F`  #Get date
time_stamp_time=`date +%T`  #Get time
time_stamp=$time_stamp_date' '$time_stamp_time  #Concatenate current DATE TIME then print out to log.csv file
printf "%20s;%8d;records added\n"  "$time_stamp" "$count" >>$log )

You just need to update your logic a bit, only count lines actually added inside the loop. Then add the "nothing to do" message outside the loop if no data was added.

Here's the pseudo-code:

counter = 0
for each line
  if date in range
    insert data
    increment counter
    # do nothing
if counter > 0
  # some lines were imported, nothing more to do
  insert 'no updates' message

Opening a new database connection for each line is terribly inefficient. If you ever need to import sizable chunks of data, you should further refactor your code: In the main loop, build a .sql file with all the insert statements, and then, after the loop, execute them all at once (ideally, inside a transaction). Or rather than generating inserts, generate data in a format that is amenable to LOAD DATA INFILE.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.