access.log date/time format

The date/time format in access.log isn't useable; php and mysql don't know what to do with a value like [09/Jun/2006:11:56:12 -0700]. Tried everything.

Any way to change the way the log writes dates? I really don't want to have to do a complicated time-consuming line-by-line preg replace on the log; need to get the date/time into a table.

You cannot alter how log formats are written, I'm sorry.

I'm shocked you're having problems. I bet you it's the [ and ]. The Date stamp is a standard RFC 2822 date format. PHP and MySQL both should recongize this format.

How about using regex to kill the [ and ]. Then try passing it through strtotime(). If that fails add GMT right at the end of -0700. It might be getting confused not seeing GMT there. -0700 is the time offset from GMT. If that still fails, just kill the -0700 all together. ^_^
(warning, -0700 will change to -0800 once we come off daylight saving time)

If ALL that fails, check out this post: http://us3.php.net/manual/en/ref.datetime.php#61092

strtotime("09/Jun/2006 11:56:12") returns -1

The log is a space-delimited file, the [ and ] should be quotes since there's a space in between.

Just grousing. More trouble than I'm used to for a bulk copy operation.

Here's what worked (the goal being to move yesterday's log to a table without using the sql INPUT command):

// read srclog
$srclog = "../logs/yourownstrippoker.com/http.1168349/access.log.0";
$rawlog = file_get_contents($srclog);

// fix the damned date
$pattern = array ('@/Jan/@', '@/Feb/@', '@/Mar/@', '@/Apr/@', '@/May/@', '@/Jun/@',
'@/Jul/@', '@/Aug/@', '@/Sep/@', '@/Oct/@', '@/Nov/@', '@/Dec/@',
'@[(d{2})/(d{2})/(d{4})@',
'@:(d{2}):(d{2}):(d{2}) -0(7|8)00]@');
$replace = array ('/01/', '/02/', '/03/', '/04/', '/05/', '/06/',
'/07/', '/08/', '/09/', '/10/', '/11/', '/12/',
'"3-2-1',
' 1:2:3"');
$newlog = preg_replace($pattern, $replace, $rawlog);

// write newlog
$handle = fopen("./lastlog.txt", "w");
$logbytes = fwrite($handle, $newlog);
fclose($handle);

// import log to table
$s = "mysqlimport --fields-optionally-enclosed-by='"'
--fields-terminated-by=' ' --lines-terminated-by='n'
--user=xxxxxx --password=yyyyyy --local
--host=dbhost.yourownstrippoker.com log_db ./lastlog.txt";
exec($s);

In reply to:

---

strtotime("09/Jun/2006 11:56:12") returns -1

---

Change the /'s to spaces and it'll work. You can even leave the -0700 in there, too:

$date = strreplace('/', ' ', "09/Jun/2006 11:56:12");
$stamp = strtotime($date);