15

I have a file containing this:

1415602803,LOGIN SUCCESS,AUTH,user2,192.168.203.63,10.146.124.73,59996,22
1415602807,LOGIN SUCCESS,AUTH,user1,172.24.31.10,172.32.1.1,48191,22
1415602811,LOGIN FAILED,AUTH,root,172.24.166.153,10.146.124.73,52506,22
1415602815,LOGIN FAILED,AUTH,user3,192.168.123.55,10.146.32.99,55750,22

I want to convert the timestamp to a date in this format:

2014-11-10 02:00:03,LOGIN SUCCESS,AUTH,user2,192.168.203.63,10.146.124.73,59996,22
2014-11-10 02:00:07,LOGIN SUCCESS,AUTH,user1,172.24.31.10,172.32.1.1,48191,22
2014-11-10 02:00:11,LOGIN FAILED,AUTH,root,172.24.166.153,10.146.124.73,52506,22
2014-11-10 02:00:15,LOGIN FAILED,AUTH,user3,192.168.123.55,10.146.32.99,55750,22

How can I do that?

I know this works: perl -pe 's/(\d+)/localtime($1)/e' (from this question) but the output format is Mon Nov 10 02:00:03 2014.

I know this command can convert timestamps into my desired output: date -d@1415602803 +"%F %H:%M:%S", but I couldn't make it work with awk using system("cmd") because of all the quotations and whatnot.

6

Like this perhaps

perl -pe 'use POSIX qw(strftime); s/^(\d+)/strftime "%F %H:%M:%S", localtime($1)/e' 
17

Found something here: Stackoverflow - Convert from unixtime at command line.

Came up with this:

awk -F"," '{OFS=","; $1=strftime("%Y-%m-%d %H:%M:%S", $1); print $0}' file
  • -F"," to use a field separator of ,,
  • OFS=","; so that the output fields are also separated by a ,,
  • $1=strftime("%Y-%m-%d %H:%M:%S", $1); to change the value of the first field $1 into the specified format, and
  • print $0; to print the whole line.
  • 2
    awk 'BEGIN{ print strftime("%Y-%m-%d %H:%M:%S", 0) }' gives awk: line 2: function strftime never defined here. Please clarify that NOT EVERY AWK has strftime()! For a solution running in most (all?) AWKs, please use the external date command to stay portable. – yeti Nov 25 '14 at 5:37
  • 2
    @yeti, there are a few date implementations that can convert dates between formats as an extension, but how it's done varies completely between the implementations. For instance, the solution given by Costas uses a syntax specific to GNU date. If you want portability, use perl. – Stéphane Chazelas Aug 17 '16 at 17:22
5

If you like awk you can use external command date with any date formats

awk -F, -v OFS="," '{("date +%F\ %T -d @"$1)|getline $1}1'
  • +1 for NOT using strftime()! – yeti Nov 25 '14 at 5:39
  • 4
    Note that using an external command will incur a severe performance hit, especially noticeable if many thousands of rows are being processed – Jose Gómez Nov 23 '15 at 15:58
2

You can also try:

cat test.txt | sed 's/^/echo "/; s/\([0-9]\{10\}\)/`date -d @\1`/; s/$/"/' | bash
2

I asked a question 9 months ago that was marked a duplicate of this one. I only just now saw a request to post the answer that worked for me on this question. Here is a link to that question and answer.

https://unix.stackexchange.com/a/304009/172916

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.