Convert Apache Log Datetime Format Using Command Line

Question

I have a line like this to extract the datetime per hour of a apache log

awk '{print $4}' elasticsearch.log.* | cut -c2-15 | sed -e 's/$/:00:00/

The problem is that the output date is formatted like below

07/Jul/2014:06:00:00

Is there a way to convert the datetime format on the fly using command line to a more common format like 'YYYY-MM-DD HH:mm:ss' e.g. 2014-07-07 06:00:00 ?

One way I found right now is using an intermediary script

#!/usr/bin/env python

import sys
import re


months = {'Jan': '01', 'Feb': '02', 'Mar': '03', 'Apr': '04', 'May': '05', 'Jun': '06', 'Jul': '07', 'Aug': '08', 'Sep': '09', 'Oct': '10', 'Nov': '11', 'Dec' : '12'}
regex = re.compile("(\d{2})/(\w+)/(\d{4}):(\d{2}):(\d{2}):(\d{2})",re.IGNORECASE)
for line in sys.stdin:
    try:
        r = regex.search(line)
        g = r.groups()
        print g[2] + '-' + months[g[1]] + '-' + g[0] + ' ' + g[3] + ':' + g[4] + ':' + g[5]
    except:
        pass

but i'm looking if there is a much shorter way

Jeroen Janssens Jeroen Janssens · Accepted Answer · 2015-09-08T11:53:11

Perhaps date (of GNU coreutils at least) can be of help here. It can recognize many different date formats, but in your case the slashes have to be translated into spaces first.

$ echo '07/Jul/2014 06:00:00\n09/Aug/2015 07:01:02' |
> tr '/' ' ' | date -f - +%Y-%m-%d\ %H:%M:%S
2014-07-07 06:00:00
2015-08-09 07:01:02

Oops, I assumed that the first colon in your example was a typo and should have been a space. Your regex shows that I was wrong. In that case you could do:

$ echo '07/Jul/2014:06:00:00\n09/Aug/2015:07:01:02' |
> sed -e 's/\// /g;s/:/ /1' | date -f - +%Y-%m-%d\ %H:%M:%S
2014-07-07 06:00:00
2015-08-09 07:01:02

Hope this helps.

Convert Apache Log Datetime Format Using Command Line

1 Answers