20

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.

Improve this question

5 Answers 5

Reset to default
28

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.
Improve this answer
2
  • 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. Commented Nov 25, 2014 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. Commented Aug 17, 2016 at 17:22
9

Like this perhaps

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

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'
Improve this answer
1
  • 5
    Note that using an external command will incur a severe performance hit, especially noticeable if many thousands of rows are being processed Commented Nov 23, 2015 at 15:58
2

You can also try:

cat test.txt | sed 's/^/echo "/; s/\([0-9]\{10\}\)/`date -d @\1`/; s/$/"/' | bash
Improve this answer
0
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

Improve this answer

You must log in to answer this question.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.