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.

Community
  • 1
Alaa Ali
  • 1,875

5 Answers5

26

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.
Community
  • 1
Alaa Ali
  • 1,875
  • 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. –  Nov 25 '14 at 05: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
9

Like this perhaps

perl -pe 'use POSIX qw(strftime); s/^(\d+)/strftime "%F %H:%M:%S", localtime($1)/e'
Marki
  • 855
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'
Costas
  • 14,916
2

You can also try:

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

Emily Shaffer
  • 85