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 '16 at 20:35

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 '16 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.


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.