0

I want change Unix epoch (in milliseconds) to normal date in a generic text file. I accept bash, sed, awk or perl solutions.

Example file1 (only numbers with 13 digits are dates):

Foo bar 1397497040418 foo 1526 bar foobar, bar 1397497060518.
Bar, 1357407040418 foo 10 foobar.

I want this output:

Foo bar 2014-04-14 19:37:20 foo 1526 bar foobar, bar 2014-04-14 19:37:40.
Bar, 2013-01-05 18:30:40 foo 10 foobar.

I tried unsuccessfully:

sed -E "s/([0-9]{10})/date -d @\1/;e" file1

Any idea?

Thank you.

  • 1
    date -d is non-portable – schily Jul 7 at 12:52
  • 1
    What operating system are you using? Is it Linux? Can we assume you have access to GNU date? – terdon Jul 7 at 12:54
  • 1
    Also, your numbers don't make sense. Date 1397497040418 in seconds since the epoch corresponds to Sat 02 Dec 46254 06:20:18 AM GMT. Is that really what you want? Are you sure you don't want to take only the first 10 digits instead of 13? – terdon Jul 7 at 13:07
  • Presumably the last three digits should appear as HH:MM:SS.418 – Paul_Pedant Jul 7 at 13:11
  • 2
    The first sentence does say milliseconds, note. – JdeBP Jul 7 at 16:22
3

With your input file:

perl -MTime::Piece -pe '
    s{ \b (\d{10}) \d{3}\b }
     { localtime($1)->strftime("%F %T") }xge
' file1
Foo bar 2014-04-14 13:37:20 foo 1526 bar foobar, bar 2014-04-14 13:37:40.
Bar, 2013-01-05 12:30:40 foo 10 foobar.
| improve this answer | |
  • Time::Piece is a core module since version 5.10.0, so it should not require any extra installation. – glenn jackman Jul 7 at 15:24
2

Assuming you want us to ignore the extra digits and limit the date conversion to only the 1st 10 that would mean the date isn't hundreds of thousands of years in the future, you could do this using GNU date and perl:

$ perl -pe 's/\b(\d{10})\d{3}\b/chomp($i=`date -d \@$1 "+%F %T"`);$i/eg' file1 
Foo bar 2014-04-14 18:37:20 foo 1526 bar foobar, bar 2014-04-14 18:37:40.
Bar, 2013-01-05 17:30:40 foo 10 foobar.

Glenn's solution is cleaner and more portable, however, so if you don't mind using a perl module (and you shouldn't), I would recommend his over mine.


As for your sed attempt, in order to use the result of a command, you need to use backticks or $(command). So you were almost there:

$ sed -E "s/([0-9]{10})/$(date -d @\1)/" file1
Foo bar Thu 01 Jan 1970 01:00:01 AM BST418 foo 1526 bar foobar, bar 1397497060518.
Bar, Thu 01 Jan 1970 01:00:01 AM BST418 foo 10 foobar.

Of course, that doesn't remove the extra digits and it doesn't limit it to only 13-digit numbers, but since you haven't clarified what you need, I am not sure how to fix it. My Perl solution deals with it, so you can use the sed if you don't want to remove them.

| improve this answer | |
  • Sed does have the minor disadvantage of running a new external date process for every date in the file. awk and perl have date conversions built-in. – Paul_Pedant Jul 7 at 16:47
  • @Paul_Pedant true, but not relevant here since my clunky perl solution has the same problem (if problem it is, I doubt it would ever be an issue unless the files are really large). So since I'm not using the perl tools for date manipulation, the sed is no worse than the perl :) – terdon Jul 7 at 17:06
2

Using GNU awk with match(), substr() and strftime() functions. Scan a given row to identify records with 10 or more digits, as they would be the EPOCH timestamps.

On those records extract only the first 10 digits and pass it to the strftime() function and apply the date format as needed. Replace the original record with new timestamp with the sub() function.

awk '
{ 
    for(col=1; col<=NF; col++) { 
        if ( match($col, /[[:digit:]]{10,}/) ) { 
            sub( substr($col, RSTART, RLENGTH), 
              strftime("%Y-%m-%d %H:%M:%S", substr($col, RSTART, RLENGTH-3) ) )   
        }  
    }
}1' file1
| improve this answer | |
  • You should mention that requires GNU awk for time functions. That would fail if a 10-digit number like 0123456789 occurred in a line though as it'd try to treat it as the first 10 digits of a 13-digit number. – Ed Morton Jul 7 at 13:55
  • Match any field of length 13 and all digits, and /1000 for strftime. – Paul_Pedant Jul 7 at 16:45
1

With GNU awk for time functions, the 3rd arg to match() and word boundaries:

$ awk '{
    while ( match($0,/(.*)\<([0-9]{10})[0-9]{3}\>(.*)/,a) ) {
        $0 = a[1] strftime("%F %T",a[2]) a[3]
    }
    print
}' file
Foo bar 2014-04-14 12:37:20 foo 1526 bar foobar, bar 2014-04-14 12:37:40.
Bar, 2013-01-05 11:30:40 foo 10 foobar.
| improve this answer | |

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.