How to convert date with awk

Question

My file temp.txt

ID53,20150918,2015-09-19,,0,CENTER<br>
ID54,20150911,2015-09-14,,0,CENTER<br>
ID55,20150911,2015-09-14,,0,CENTER

I need to replace and convert the 2nd field (yyyymmdd) for seconds

I try it, but only the first line is replaced

awk -F"," '{ ("date -j -f ""%Y%m%d"" ""20150918"" ""+%s""") | getline $2; print }' OFS="," temp.txt

and tried to like this

awk -F"," '{system("date -j -f ""%Y%m%d"" "$2" ""+%s""") | getline $2; print }' temp.txt

the output is:

1442619474
sh: 0: command not found
ID53,20150918,2015-09-19,,0,CENTER
1442014674
ID54,20150911,2015-09-14,,0,CENTER
1442014674
ID55,20150911,2015-09-14,,0,CENTER

Using gsub also could not

awk -F"," '{gsub($2,"system("date -j -f ""%Y%m%d"" "$2" ""+%s""")",$2); print}' OFS="," temp.txt 

awk: syntax error at source line 1
 context is
    {gsub($2,"system("date -j -f ""%Y%m%d"" "$2" >>>  ""+% <<< s""")",$2); print}
awk: illegal statement at source line 1
    extra )

I need the output to be so. How to?

ID53,1442619376,2015-09-19,,0,CENTER
ID54,1442014576,2015-09-14,,0,CENTER
ID55,1442014576,2015-09-14,,0,CENTER

I think you have your answer [here][1]. [1]: http://stackoverflow.com/questions/20306217/timestamp-to-epoch-in-a-csv-with-gawk — tstark81, Sep 25 '15 at 00:07
@tstark81: the only problem is that this is tagged [tag:osx] meaning Mac OS X, and Mac OS X has BSD `awk`, not GNU `awk`. GNU `awk` has some powerful extensions added for date processing (and [Timestamp to epoch in a CSV file with `gawk`](http://stackoverflow.com/questions/20306217/timestamp-to-epoch-in-a-csv-with-gawk) likely covers them. It isn't much help on Mac OS X unless the user installs GNU `awk`, though. — Jonathan Leffler, Sep 25 '15 at 00:10
In a [previous answer of mine](http://stackoverflow.com/a/17692959/1259917), I used date on a mac with its default awk. It's not formatted for your case, but maybe it will help you. — n0741337, Sep 25 '15 at 00:50

Renaud Pacalet · Accepted Answer · 2015-09-25T05:45:45.600

This GNU awk script should make it. If it is not yet installed on your mac, I suggest installing macport and then GNU awk. You can also install a decent version of bash, date and other important utilities for which the default are really disappointing on OSX.

BEGIN { FS = ","; OFS = FS; }
{
    y = substr($2, 1, 4);
    m = substr($2, 5, 2);
    d = substr($2, 7, 2);
    $2 = mktime(y " " m " " d " 00 00 00");
    print;
}

Put it in a file (e.g. txt2ts.awk) and process your file with:

$ awk -f txt2ts.awk data.txt
ID53,1442527200,2015-09-19,,0,CENTER<br>
ID54,1441922400,2015-09-14,,0,CENTER<br>
ID55,1441922400,2015-09-14,,0,CENTER

Note that we do not have the same timestamps. I let you try to understand where it comes from, it is another problem.

Explanations: substr(s, m, n) returns the n-characters sub-string of s that starts at position m (starting with 1). mktime("YYYY MM DD HH MM SS") converts the date string into a timestamp (seconds since epoch). FS and OFS are the input and output filed separators, respectively. The commands between the curly braces of the BEGIN pattern are executed at the beginning only while the others are executed on each line of the file.

score 0 · Answer 2 · answered Sep 25 '15 at 05:39

0

You could use substr:

printf "%s-%s-%s", substr($6,0,4), substr($6,5,2), substr($6,7,2)

Assuming that the 6th field was 20150914, this would produce 2015-09-14

answered Sep 25 '15 at 05:39

Srini V

11,045
14
66
89

How to convert date with awk

2 Answers2