0

Using awk, how to convert dates (yyyy-mm-dd) to week and quarter (first day of week set to monday)?

Input:

a;2016-04-25;10
b;2016-07-25;20
c;2016-10-25;30
d;2017-02-25;40

Wanted output:

a;2016-04-25;10;2016-w17;2016-q2
b;2016-07-25;20;2016-w30;2016-q3
c;2016-10-25;30;2016-w43;2016-q4
d;2017-02-25;40;2017-w8;2017-q1
Vadim Kotov
  • 8,084
  • 8
  • 48
  • 62
Markus
  • 69
  • 6
  • What did you try? I'd use `date` for it (ISO week, Sunday/Monday starting week etc.). – James Brown May 27 '17 at 09:02
  • @JamesBrown while read f1 f2 f3; do echo -n $f1; echo -n " "; echo -n $f2; echo -n " "; date -d $f2 +%V;done < input .... But not getting the quoting correct.. and not using awk... :p – Markus May 27 '17 at 09:18
  • 2
    @Markus add the code you tried to question instead of comments... and see http://mywiki.wooledge.org/BashFAQ/001 on specifying delimiters – Sundeep May 27 '17 at 09:22

1 Answers1

3

awk solution:

awk -F';' '{ split($2,d,"-"); w = strftime("%W", mktime(d[1]" "d[2]" "d[3]" 00 00 00")); 
           q = int((d[2]+2)/3); 
           print $0,d[1]"-w"w,d[1]"-q"q}' OFS=';' file

The output:

a;2016-04-25;10;2016-w17;2016-q2
b;2016-07-25;20;2016-w30;2016-q3
c;2016-10-25;30;2016-w43;2016-q4
d;2017-02-25;40;2017-w08;2017-q1

  • split($2,d,"-") - split the 2nd field (date) by separator -

  • mktime(datespec) - turn datespec (date specification) into a timestamp

  • strftime("%W", mktime(d[1]" "d[2]" "d[3]" 00 00 00")) - format the time returned by mktime() function according to %W (the week number of the year)

  • q = int((d[2]+2)/3) - calculating the quarter number. The quarter is equivalent to 3 months. So we'll use 3 as a divisor.

https://www.gnu.org/software/gawk/manual/html_node/Time-Functions.html

RomanPerekhrest
  • 88,541
  • 4
  • 65
  • 105