How can I convert timestamps in a column to a date?

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 [email protected] +"%F %H:%M:%S", but I couldn’t make it work with awk using system("cmd") because of all the quotations and whatnot.

Answers:

Thank you for visiting the Q&A section on Magenaut. Please note that all the answers may not help you solve the issue immediately. So please treat them as advisements. If you found the post helpful (or not), leave a comment & I’ll get back to you as soon as possible.

Method 1

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.

Method 2

Like this perhaps

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

Method 3

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'

Method 4

You can also try:

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

Method 5

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

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0