CODEWITHSUNDEEP
bashawk
lanfeust9
lanfeust9

Reputation: 31

Bash: how to replace dates within a csv file and round the minutes to one of 4 values

I have a csv file looking like this:

datetime,nameval1,nameval2,nameval3,...,namevalx
2015/03/16 19:55:10,a,b,c,...,x
2015/03/16 19:30:30,a1,b1,c1,...,x1
2015/03/16 19:30:50,a2,b2,c2,...,x2

I need to modify the date which is the first value starting the 2nd line in this csv file so that the minutes are rounded to either 00, 15, 30, 45, 60 (if the minute is 60 then the hour needs to be incremented by one and the minute value is back to 00, if this was the last hour in the day 23 then in addition the day needs to be incremented by 1).

I found the awk command may be helping me to achieve what I want to do. I've simplified to just a simple test based on the minute rounded value - if the minute adjusted value is 60 then I change $5 back to 00.

awk 'BEGIN {FS="[,/ :]"; OFS=","} {
if ($1=="datetime")
{
  print
}
else
{
  min=(int(($5+15/2)/15))*15
  if(min == 60)
      $5="00"
      date=$1 "/" $2 "/" $3 " " $4 ":" $5 ":" $6
      $1=$2=$3=$4=$5=$6=""
      print date $0
}
;}' ./file.csv

I've got a number problem with this:

1) This is an example of the result I get for one line where I did not manage to merge the date back properly and/or I need awk to print the rest starting column $7 only:

2014/03/16 19:00:50,,,,,,a,b,c,...,x

2) The way I've used awk to split the csv file is not exactly what I need since other variables in the file could include / or spaces. I need to split using the comma only and then further split datetime into its components year/month/day/hour/minute/sec using one of these 3 characters [/ :].

3) I need to avoid touching the 1st line of the file which I do here by having an if statement which if awk can also do more simply I'd use

Thanks !

Upvotes: 3

Views: 283

Answers (1)

Wintermute
Wintermute

Reputation: 44023

Happily, your date format needs very little tweaking to work with mktime, so using GNU awk:

awk -F, 'BEGIN { OFS = FS } NR != 1 { gsub(/[/:]/, " ", $1); ts = mktime($1); ts = int((ts + 450) / 900) * 900; $1 = strftime("%Y/%m/%d %H:%M:%S", ts) } 1' filename

The heart of this is the mktime function that converts a suitably formatted time stamp to seconds since Epoch (1970-01-01 00:00:00 UTC). Having that number, rounding to quarter hours is easy, and as it so happens, the format mktime expects is that of your timestamp with / and : replaced by spaces. So:

BEGIN {                                    # In the beginning: 
   OFS = FS                                # set the output field separator to
                                           # the same as the input field sep.
                                           # so the output is delimited the
                                           # same way as the input
}
NR != 1 {                                  # Unless this is the first line
  gsub(/[/:]/, " ", $1)                    # replace / and : in the first field
                                           # with spaces
  ts = mktime($1)                          # calculate seconds since Epoch
  ts = int((ts + 450) / 900) * 900         # round to quarter hour
  $1 = strftime("%Y/%m/%d %H:%M:%S", ts)   # write back suitably formatted
                                           # time stamp
}
1                                          # then print.

The big caveat here is that most of this stuff is GNU-specific, so you'll need to use the GNU flavor of awk to run it. Your package manager will probably call it gawk.

Upvotes: 2

Related Questions