Including timestamp into awk command

Question

Consider a bunch of almost identical csv files. They all have in common the part shown, i.e. a line with Date;Time;... followed by four columns of data (some of the first lines have six columns, where the fifth column is empty and the sixth is mere descriptive text). There is also descriptive text separated into various columns preceding the line Date;Time;...

...
...
Date;Time;Airtemp;Quality;;Other info
1961-01-01;06:00:00;0.4;G;;...
1961-01-01;12:00:00;2.3;G;;...
1961-01-01;18:00:00;...;.;;...
1961-01-02;15:00:00;...;.  
..........;........;...;.
2015-09-01;........;...;.

Using the following commands (*)

awk -F ';' 'x==1 {print $1 " " $2 " " $3 " " $4} /Date/ {x=1}' file >> new_file

sed -i '' 's/[-:,]//g' new_file

creates a new_file with the following format

19610101   060000  0.4  G 
19610101   120000  2.3  G 
19610101   180000  ...  . 
19610102   150000  ...  .        
19610102   180000  ...  .       
19610103   060000  ...  .      
........   ......  ...  .
20150901   ......  ...  .

Now, using the command (**)

awk '
     {
        tspec = sprintf("%4d %.2d %.2d 00 00 00", substr($1,1,4), substr($2,1,2), substr($2,3,4))
        t = mktime(tspec)
        $(NF+1) = 0 + strftime("%j",t)
    } {print}' new_file

creates another column in new_file with a numbering of days.

19610101   060000  0.4  G 1
19610101   120000  2.3  G 1
19610101   180000  ...  . 1
19610102   150000  ...  . 2
19610102   180000  ...  . 2
19610103   060000  ...  . 3
........   ......  ...  . .
20150901   ......  ...  . .

Is there a way to combine commands (*) and (**) in one script? Currently these are run in two separate ones.

Answer 1

Combining the two awk commands (and the sed):

awk -F ';' '
    x == 1 {
        gsub("[:,-]", "")
        tspec = sprintf("%4d %.2d %.2d 00 00 00", substr($1,1,4), substr($1,5,2), substr($1,7,4))
        t = mktime(tspec)
        print $1, $2, $3, $4, 0 + strftime("%j", t)
    }
    /Date/ { x = 1 }' file

This assumes that you are using either GNU awk or mawk (not BSD awk, which may not implement mktime() or strftime()).

The code combines the two awk commands and the sed (using gsub()), and I've also taken the liberty to correct the parsing of the date for setting tspec.

Given a file,

...
...
Date;Time;Airtemp;Quality;;Other info
1961-01-01;06:00:00;0.4;G;;...
1961-01-01;12:00:00;2.3;G;;...
1961-01-01;18:00:00;...;.;;...
1961-01-02;15:00:00;...;.
2015-09-01;........;...;.

this would produce

19610101 060000 0.4 G 1
19610101 120000 2.3 G 1
19610101 180000 ... . 1
19610102 150000 ... . 2
20150901 ........ ... . 244

Note that space is the default output field separator (OFS), so if you are not setting OFS to anything explicitly you don't (shouldn't) have to output the spaces using " " between each output field.

Answer 2

This is hard to answer since you're not showing us real data. But, if I understand you correctly, you don't need to consider the timestamp at all. based on your desired output, you just want to remove the - and : from the input and add the extra column:

$ awk -F';' '{day=substr($1,9,2); gsub(/[:-]/,""); printf "%s;%.1d\n",$0,day}' file
19610101;060000;0.4;G;...;1
19610101;120000;2.3;G;...;1
19610101;180000;...;.;1
19610102;150000;...;.  ;2
..........;........;...;.;0
20150901;........;...;.;1

Or, if you want tab-separated columns as you show (I think) in your desired output:

 $ awk -F';' -vOFS="\t" '{day=substr($1,9,2); gsub(/[:-]/,""); print $1,$2,$3,$4, sprintf("%.1d",day)}' file
19610101    060000  0.4 G   1
19610101    120000  2.3 G   1
19610101    180000  ... .   1
19610102    150000  ... .   2
..........  ........    ... .   0
20150901    ........    ... .   1

Answer 3

Try changing $4 to $5 to show the day of year

{print $1 " " $2 " " $3 " " $4}