I'm currently doing a tail -f mysql.log and also have it color coded. But how would I use awk to convert the unix timestamps to human readable format?

tail -f /var/lib/mysql/mysql-slow.log | awk '/User@Host:/ { $0 = "\033[32m" $0 "\033[39m" } /Query_time:/ { $0 = "\033[36m" $0 "\033[39m" } 1'

enter image description here

  • 6
    Please use text instead of images and format your post properly. Also, what's your awk flavor ? May 18, 2016 at 20:35

2 Answers 2


If you have GNU awk (gawk), which is usually the case on non-embedded Linux and Cygwin, then you can use the strftime function.

if (match($0, /^(.*timestamp=)([0-9]+)(.*)$/, parts)) {
    time = strftime("%F %T", parts[2]);
    $0 = parts[1] time parts[3];

On an embedded system with BusyBox, you may have a more restricted version of awk but a date utility that's capable of doing the conversion.

if (match($0, /timestamp=[0-9]+/)) {
    system("date +'%F %T' -d " substr($0, RSTART+10, RLENGTH-10)) | getline time;
    $0 = substr($0, 1, RSTART) time substr($0, RSTART+RLENGTH);

With only POSIX tools, there's no way to convert between human-readable dates and Unix timestamps other than doing the calculation by yourself. (I know there's shell and awk code available on the web for that, but I don't have a link handy.) I recommend making sure you have something better installed, such as gawk, perl, python, etc.

  • gawk match has groups so you can simplify a bit with match($0, /timestamp=([0-9]+)/, a) { ... strftime("fmt", a[1]) ... } -- although for this specific case I think @aphorise FS trick is cleverer, in the sense of 'clever' that isn't unmixedly good. May 19, 2016 at 3:59

Discarding colours - you may use:

# | piped at the end 
awk -F'SET timestamp=' 'NF > 1{ system("date -d @" $2) }'

Stripping the colours out in-line with the reset makes it more challenging; I suspect that you can control / adjust that by removing the end included escape sequences that relate to the timestamp portion.

  • i dont get it. do i pipe that into awk? May 18, 2016 at 21:32

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .