4

Increment date with AWK

Hi all, I need your help. I have a file "test.csv" and I want to increase the dates for "6 months" and "10 days" so that I am getting the following output:

test.csv:

"000001","TEST1","2013-05-07 16:02:07","ACTIVE" "000003","TEST3","2013-05-09 16:02:07","ACTIVE" "000004","TEST4","2013-05-10 16:02:07","ACTIVE" "000005","TEST5","2013-05-11 12:02:07","ACTIVE"

Output test-increment-10days.csv:

"000001","TEST1","2013-05-07 16:02:07","ACTIVE","2013-05-17 16:02:07" "000003","TEST3","2013-05-09 16:02:07","ACTIVE","2013-05-19 16:02:07" "000004","TEST4","2013-05-10 16:02:07","ACTIVE","2013-05-20 16:02:07" "000005","TEST5","2013-05-11 12:02:07","ACTIVE","2013-05-21 12:02:07"

Output test-increment-6months.csv:

"000001","TEST1","2013-05-07 16:02:07","ACTIVE","2013-11-07 16:02:07" "000003","TEST3","2013-05-09 16:02:07","ACTIVE","2013-11-09 16:02:07" "000004","TEST4","2013-05-10 16:02:07","ACTIVE","2013-11-10 16:02:07" "000005","TEST5","2013-05-11 12:02:07","ACTIVE","2013-11-11 12:02:07"

I have tried it with this commands:

awk -F"\",\"" '{{ cmd="date \"+%Y-%m-%d %T\" -d \""$3" +10 days\""; cmd | getline datum; close(cmd); print $0 ",\""datum"\""}}' test.csv > test-increment-10days.csv

awk -F"\",\"" '{{ cmd="date \"+%Y-%m-%d %T\" -d \""$3" +6 months\""; cmd | getline datum; close(cmd); print $0 ",\""datum"\""}}' test.csv > test-increment-6months.csv

but I am getting this wrong outputs.

test-increment-10days.csv:

"000001","TEST1","2013-05-07 16:02:07","ACTIVE","2013-05-08 16:02:07" "000003","TEST3","2013-05-09 16:02:07","ACTIVE","2013-05-10 16:02:07" "000004","TEST4","2013-05-10 16:02:07","ACTIVE","2013-05-11 16:02:07" "000005","TEST5","2013-05-11 12:02:07","ACTIVE","2013-05-12 12:02:07"

test-increment-6months.csv:

"000001","TEST1","2013-05-07 16:02:07","ACTIVE","2013-06-07 16:02:07" "000003","TEST3","2013-05-09 16:02:07","ACTIVE","2013-06-09 16:02:07" "000004","TEST4","2013-05-10 16:02:07","ACTIVE","2013-06-10 16:02:07" "000005","TEST5","2013-05-11 12:02:07","ACTIVE","2013-06-11 12:02:07"

The dates in "test-increment-10days.csv" are only incremented one day and in the "test-increment-6months.csv" only one month. Hope somebody can help.

Improve this question

3 Answers 3

Reset to default
3

Try this:

awk -F"\",\"" '{cmd="date -d \"$(date -d \""$3"\")+10days\" \"+%Y-%m-%d %T\"";cmd | getline datum; close(cmd); print $0 ",\""datum"\""}' test.csv > test-increment-10days.csv

An example of the underlying date command is:

date -d "$(date -d '2013-05-07 16:02:07')+10days" "+%Y-%m-%d %T"

There are two date commands. The inner date creates a date with a specified value. Then add 10 days to it. The outer date creates a new incremented date and formats it.

Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

3

Here's one way using GNU awk. You will first need to convert your requirements into seconds. Alternatively, write some awk to do this. This should get you up and running:

awk -f script.awk test.csv

Contents of script.awk:

BEGIN { FPAT = "([^,]+)|(\"[^\"]+\")" OFS="," } { string = "\"(....)-(..)-(..) (..):(..):(..)\"" format = "\\1 \\2 \\3 \\4 \\5 \\6" date_spec = gensub(string, format, "", $3) timestamp = mktime(date_spec) # 10 days = 864000 seconds # 6 months = 15638400 seconds # uncomment one of the following: # new_stamp = "\"" strftime("%F %T", timestamp + 864000) "\"" # new_stamp = "\"" strftime("%F %T", timestamp + 15638400) "\"" print $0, new_stamp }

Results:

"000001","TEST1","2013-05-07 16:02:07","ACTIVE","2013-05-17 16:02:07" "000003","TEST3","2013-05-09 16:02:07","ACTIVE","2013-05-19 16:02:07" "000004","TEST4","2013-05-10 16:02:07","ACTIVE","2013-05-20 16:02:07" "000005","TEST5","2013-05-11 12:02:07","ACTIVE","2013-05-21 12:02:07"

Results:

"000001","TEST1","2013-05-07 16:02:07","ACTIVE","2013-11-04 16:02:07" "000003","TEST3","2013-05-09 16:02:07","ACTIVE","2013-11-06 16:02:07" "000004","TEST4","2013-05-10 16:02:07","ACTIVE","2013-11-07 16:02:07" "000005","TEST5","2013-05-11 12:02:07","ACTIVE","2013-11-08 12:02:07"
Improve this answer

1 Comment

+1 Nice use of FPAT and built in functions. Though will work for GAWK version 4 or later because of FPAT.
0

An alternate version of dogbane's answer with lesser quoting and using a simplified date construct would be

awk 'BEGIN { FPAT = "([^,]+)|(\"[^\"]+\")" OFS="," } { gsub(/["]/,"",$3); cmd = "date -d \""$3" 10 days\" \"+%Y-%m-%d %I:%M:%S %p\"" } { cmd|getline D; close(cmd); $3="\042"D"\042" }1' file

and for 6 months would be

awk 'BEGIN { FPAT = "([^,]+)|(\"[^\"]+\")" OFS="," } { gsub(/["]/,"",$3); cmd = "date -d \""$3" 6 months\" \"+%Y-%m-%d %I:%M:%S %p\"" } { cmd|getline D; close(cmd); $3="\042"D"\042" }1' file

The enhancements done are:

  1. A fewer quotes while forming the command string cmd for the date compute, which makes it elegant to use in the getline function of awk.
  2. Making use of the date -d<date-string> 10 days, without using the + operator.
  3. Adding FPAT regex construct for a more strict parsing of the individual CSV fields.
Improve this answer

Comments

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.