1

I've a file that looks like this

server1-adm.test.com,/var,dir,29987,2007-12-03 15:52:43,root,root server2.fs.com,/DATA_File.out,file,299076487,2008-10-15 05:12:23,user1,group1 server3-prd.mod.com,/opt,dir,29987,2009-05-03 00:13:23,user1,group1 server4,/var/tmp/xxz.zip,file,400,2007-12-03 15:52:43,root,root server1-adm.test.com,/usr,dir,34299876,2006-12-03 16:52:43,root,root server3-prd.mod.com,/local/home,dir,400,2009-05-03 12:13:23,user2,group1

The 5th column is date + time value that I want to modify. I tried using multiple awk statements together, but it looked too confusing, prone to mistakes. Instead of using multiple statements to do date format conversion, how can I use the date system command within awk to convert to below results. 

server1-adm.test.com,/var,dir,29987,2007-12-03 03:52:43 PM,root,root server2.fs.com,/DATA_File.out,file,299076487,2008-10-15 05:12:23 AM,user1,group1 server3-prd.mod.com,/opt,dir,29987,2009-05-03 00:13:23 AM,user1,group1 server4,/var/tmp/xxz.zip,file,400,2007-12-03 03:52:43 PM,root,root server1-adm.test.com,/usr,dir,34299876,2006-12-03 04:52:43 PM,root,root server3-prd.mod.com,/local/home,dir,400,2009-05-03 12:13:23 PM,user2,group1

Something in lines of date -d "2007-12-03 15:52:43" +%Y/%m/%d:%H%M%S. I don't know how we can get AM/PM in date command.

I already have multiple awk statements running together as part of a script to perform other text modification on the same incoming file, so I would like to use an awk + date statement to do it.

Improve this question
1
  • 2
    @Ed Morton, GNU awk as in gawk? Yes, the system has gawk. Commented Oct 4, 2018 at 4:22

4 Answers 4

Reset to default
3

If you can use GNU awk, it provides some time functions that would make your program run muuuuch faster than calling your shell + date for each line of your CSV file:

awk 'BEGIN { FS = OFS = "," } { gsub("[-:]", " ", $5); $5 = strftime("%F %r", mktime($5)) } 1'

Expanded form:

awk ' BEGIN { FS = OFS = "," } { gsub("[-:]", " ", $5) $5 = strftime("%F %r", mktime($5)) } 1 '

If you really want to use an external date command, as you state in your question, then use GNU awk co-processes so that only one date command is started and reused at each line:

awk 'BEGIN { FS = OFS = ","; cmd = "stdbuf -oL date -f - +%F\" \"%r" } { print $5 |& cmd; cmd |& getline $5 } 1'

Expanded form:

awk ' BEGIN { FS = OFS = "," cmd = "stdbuf -oL date -f - +%F\" \"%r" } { print $5 |& cmd cmd |& getline $5 } 1 '

But you really ought to go with the first solution.

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

1 Comment

Thanks for this. I'll stick to the GNU awk time function.
1

Could you please try following.

awk -F'[ ,]' '{split($6,array,":");$6=array[1]>12?sprintf("%02d",array[1]-12)":"array[2]":"array[3]" PM":(array[1]==12?$6 " PM":$6 " AM")} 1' Input_file

Explanation: Adding explanation too here for above code.

awk -F'[ ,]' ' ##Making field separator as space and comma for all the lines of Input_file. { split($6,array,":") ##using split function to split 6th field of current line by making : as field sep for it. $6=array[1]>12?sprintf("%02d",array[1]-12)":"array[2]":"array[3]" PM" :$6 " AM" ##re-creating $6 value by checking condition if its 1st value array value which is time is greater than 12 than subtracting its value with 12 here and adding PM and AM according to it too. } 1 ##Mentioning 1 will print the edited/non-edited value of line. ' Input_file ##Mentioning Input_file name here.
Improve this answer

3 Comments

I've already used multiple awk statements to convert the date-time to desired value, but I'm looking for a shorter way using date system command in awk.
@Marcos, did you try my one liner? That will give your correct output, please try it once and let me know then?
Yes the one-liner does work, but I'm looking for a solution where I can use date command to make things simpler.
1

You can just use the date command directly on the $5 variable as below. Be careful with the quoting of the command. Here we form the command string cmd from the $5 with the flags as needed with %p to print the appropriate AM or PM depending on time of the day.

awk -v FS=, -v OFS=, '{cmd = "date -d \""$5"\" \"+%Y-%m-%d %I:%M:%S %p\""}{ cmd|getline D; close(cmd); $5=D}1' file

The crucial part is in close(cmd) statement. It forces awk to execute cmd each time, so, date would be actual one each time. Also see Using getline into a Variable from a Pipe

Improve this answer

1 Comment

Thanks your solution works good. But then I didn't knew GNU awk had time functions, hence marking xhienne solution as answer, as this suits my requirements better.
1

Orders is a file of csv records, each of the records having three fields: order number,due date,lead time. I wanted to calculate each order's start date by subtracting the lead time from the due date, format the result as dd-Mmm-yyyy, and append this to the original record read from orders.

awk -F, '{"date --date=\""$2" -"$3" day\" +%d-%b-%Y" | getline sd; print $1","$2","$3","sd}' orders

So, let's break it down.

-F, tells Awk to use a comma as the field separator

'{command | getline variable;command}' is what I want awk to do with each record

"date --date=""$2" -"$3" day" +%d-%b-%Y" subtracts $3 days from date $2 and formats the result as dd-Mmm-yyyy.

command | getline variable assigns the result of command to variable

print $1","$2","$3","sd prints the desired output

Here's the result

$ cat orders order01,01-Mar-2024,1 order02,01-Jan-2021,2 $ awk -F, '{"date --date=\""$2" -"$3" day\" +%d-%b-%Y" | getline d; print $1","$2","$3","d}' orders order01,01-Mar-2024,1,29-Feb-2024 order02,01-Jan-2021,2,30-Dec-2020
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.