?
Solved

convert apache log date time to mysql?

Posted on 2011-05-03
8
Medium Priority
?
1,921 Views
Last Modified: 2012-06-27
How can I convert the apache log date time,
such as, 26/Apr/2011:13:21:58, to a format
that can be stored in a mysql datetime field?
0
Comment
Question by:pillmill
8 Comments
 
LVL 27

Expert Comment

by:Cornelia Yoder
ID: 35517830
0
 

Author Comment

by:pillmill
ID: 35517982
Thanks, but strtotime() did not work. Also tried mysql  str_to_date.
Any other possibilities?
0
 
LVL 61

Expert Comment

by:Kevin Cross
ID: 35518050
Are you certain it is coming over in that format OR is it a UNIX timestamp?
i.e., have you tried using MySQL's FROM_UNIXTIME() function on the value being passed in?

What format did you use with strtotime and/or str_to_date when you tried? I suspect the issue is the '/' typically gets interpreted with US format which is m/d/y as you will see in the documentation provided by yodercm; therefore, it likely won't know what to do with 26/Apr/2011 by default.
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 61

Accepted Solution

by:
Kevin Cross earned 2000 total points
ID: 35518060
This works for me:
SELECT STR_TO_DATE('26/Apr/2011:13:21:58', '%d/%b/%Y:%H:%i:%S');

Open in new window

0
 
LVL 61

Expert Comment

by:Kevin Cross
ID: 35518066
Can try this for PHP strtotime format "j/M/Y:H:i:s"
0
 
LVL 27

Expert Comment

by:Cornelia Yoder
ID: 35518108
If you want the full brute-force method to do this, here it is.
<?php


$date="26/Apr/2011:13:21:58";

$monthnum = array(
"Jan"=>"01",
"Feb"=>"02",
"Mar"=>"03",
"Apr"=>"04",
"May"=>"05",
"Jun"=>"06",
"Jul"=>"07",
"Aug"=>"08",
"Sep"=>"09",
"Oct"=>"10",
"Nov"=>"11",
"Dec"=>"12"
);


$array1 = explode('/',$date);
$array2 = explode(':',$array1[2]);


$day=$array1[0];
$month=$monthnum[$array1[1]];
$year=$array2[0];
$hour=$array2[1];
$minute=$array2[2];
$second=$array2[3];

$mysqldate = "$year"."-"."$month"."-"."$day"." "."$hour".":"."$minute".":"."$second";

echo $mysqldate;

exit;
?>

Open in new window

0
 
LVL 27

Expert Comment

by:Cornelia Yoder
ID: 35518113
By the way, that code works if Aug 4th is   "04/Aug/...".  If you might have  "4/Aug/...", then you will need to check for that and add a leading zero.
0
 
LVL 111

Expert Comment

by:Ray Paseur
ID: 35690705
http://www.laprbass.com/RAY_temp_pillmill.php
Outputs the ISO-8601 DATETIME string:
string(25) "2011-04-26T13:21:58-05:00"
<?php // RAY_temp_pillmill.php
error_reporting(E_ALL);
date_default_timezone_set('America/Chicago');

$str = '26/Apr/2011:13:21:58';
$new = convert_log_time($str);
var_dump($new);


// A FUNCTION TO CONVERT THE STRING
function convert_log_time($s)
{
    $s = preg_replace('#:#', ' ', $s, 1);
    $s = str_replace('/', ' ', $s);
    if (!$t = strtotime($s)) return FALSE;
    return date('c', $t);
}

Open in new window

0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article shows the steps required to install WordPress on Azure. Web Apps, Mobile Apps, API Apps, or Functions, in Azure all these run in an App Service plan. WordPress is no exception and requires an App Service Plan and Database to install
There are times when I have encountered the need to decompress a response from a PHP request. This is how it's done, but you must have control of the request and you can set the Accept-Encoding header.
Learn how to match and substitute tagged data using PHP regular expressions. Demonstrated on Windows 7, but also applies to other operating systems. Demonstrated technique applies to PHP (all versions) and Firefox, but very similar techniques will w…
The viewer will learn how to create a basic form using some HTML5 and PHP for later processing. Set up your basic HTML file. Open your form tag and set the method and action attributes.: (CODE) Set up your first few inputs one for the name and …
Suggested Courses

621 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question