5

I have a set of data like so:

user1 1234 01/01/2020
user2 2345 02/02/2020

I need to convert the third column (date) to EPOCH while retaining the username and id number.

user1 1234 1577862000
user2 2345 1580626800

I have been able to get the conversion and printing working, but the new date is being appended to a new line.

$awk '{print($1,$2)}; system("date -d "$3" +%s")' data
user1 1234
1577862000
user2 2345
1580626800

How can I get the new EPOCH date to be on the same line with columns 1 & 2?

6

print appends a newline by default. Use printf:

awk '{printf "%s %s ",$1,$2; system("date -d "$3" +%s")}' data

If a special output field separator OFS had been defined, you could use

awk '{printf "%s%s%s%s",$1,OFS,$2,OFS; system("date -d "$3" +%s")}' data

Notice that each %s gets one of the remaining arguments to printf.

Output:

user1 1234 1577847600
user2 2345 1580612400
| improve this answer | |
  • 1
    It is working for a few input lines but spawns a subshell for each line. Using perl (or gnu awk) time functions will be many order of magnitude faster and easier on the CPU – Olivier Dulac Sep 24 at 20:16
6

If you have GNU awk (aka gawk) you could use its built-in time mktime function (although the input date format needs some manipulation first):

$ gawk 'split($3,a,"/") {$3 = mktime(sprintf("%04d %02d %02d 00 00 00",a[3],a[2],a[1]))} 1' data
user1 1234 1577854800
user2 2345 1580619600

Alternatively, Miller provides a C-style strptime and strftime:

$ mlr --nidx put '$3 = strftime(strptime($3,"%d/%m/%Y"),"%s")' data
user1 1234 1577854800
user2 2345 1580619600

or similarly in Perl, with the Time::Piece module:

$ perl -MTime::Piece -alne '
    $t = pop @F; print join " ", @F, Time::Piece->strptime($t,"%d/%m/%Y")->strftime("%s")
  ' data
user1 1234 1577854800
user2 2345 1580619600
| improve this answer | |
  • Nice alternative, thanks for sharing. I believe the zero-padding is not necessary here? – Quasímodo Sep 24 at 18:11
  • @Quasímodo looks like you're right, I seemed to remember having to do that before but must be mistaken – steeldriver Sep 24 at 18:31
  • @Alex: that one doesn't spawn a shell for each lines of input. It will be much faster if your input files has more than a few lines. – Olivier Dulac Sep 24 at 20:18

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.