3

Trying to convert first column in a csv file from unix timestamp to date(year-month format)

Tried date -d @number'+%Y-%m' and awk, but awk doesn't recognize @ when used together

Extract from a csv file :

 1556113878,60662402644292
 1554090396,59547403093308

Expected O/p

2019-04,60662402644292
2019-03,59547403093308
Paul Hodges
  • 13,382
  • 1
  • 17
  • 36
Bt_code
  • 41
  • 4

2 Answers2

3

If you have GNU awk (sometimes called gawk), try:

gawk -F,  '{print strftime("%Y-%m", $1),$2}' OFS=, file.csv

For example, consider this input file:

$ cat file.csv
1556113878,60662402644292
1554090396,59547403093308

Our command produces this output:

$ gawk -F,  '{print strftime("%Y-%m", $1),$2}' OFS=, file.csv
2019-04,60662402644292
2019-03,59547403093308

On many Linux systems, GNU awk is the default. On others like Ubuntu, it is not but it can be easily installed: sudo apt-get install gawk. On MacOS, GNU awk can be installed via homebrew.

John1024
  • 109,961
  • 14
  • 137
  • 171
  • This worked thanks a lot. Upvoted your post, but looks like a newbie's cast doesn't show up – Bt_code Apr 26 '19 at 17:43
  • @Bt_code Glad it worked for you and thanks. (If I recall correctly, it takes 15 rep points to be able to cast upvotes.) – John1024 Apr 26 '19 at 19:08
2

If you don't have GNU AWK, you may have a system Ruby, in which case you can do this:

▶ ruby -F, -ane \
   '$F[0] = Time.at($F[0].to_i).strftime("%Y-%m"); print $F.join(",")' FILE      
2019-04,60662402644292
2019-04,59547403093308

Further explanation:

  • Unlike Perl's POSIX::strftime, system Ruby should ship with the Time module. Thus my choice of Ruby.

  • The command line options are -F, is the same as AWK; -n is the same as sed; -a turns on AWK-like auto-split; -e is the same as sed.

  • $F is similar to AWK's $0 and $F[0] is similar to AWK's $1. $F[0].to_i converts the Epoch time string in the first field to an integer.

Alex Harvey
  • 14,494
  • 5
  • 61
  • 97