DelphiFAQ Home Search:

Convert a date from Apache's string format to mysql's date format in perl

 

comments7 comments. Current rating: 2 stars (4 votes). Leave comments and/ or rate it.

Question:

Apache logs dates in this form: [25/Apr/2005:11:45:59 -0400] and I need to insert this string in a mysql table as a datetime value.
How can I convert it?

Answer:

mysql has powerful date handling functions, which unfortunately vary from version to version substantially.
You may use the following perl code instead - it converts

25/Apr/2005:11:45:59

to

2005-04-25 11:45:59

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

$time = substr($time,7,4) . '-' . $mo{substr($time,3,3)} . '-' . substr($time,0,2) . ' ' . substr($time,12);

Content-type: text/html

Comments:

2005-11-15, 02:57:55
anonymous from India  
rating
2006-03-21, 09:25:22
anonymous from India  
rating
very very very very very very bad
2006-03-21, 10:50:37
anonymous from India  
rating
bad
2006-06-17, 15:48:07
anonymous from United States  
rating
'very very bad' isn't helpful, tool.

Use mysql's STR_TO_DATE function, like so:

select STR_TO_DATE('11/Jun/2006:06:28:16', '%d/%b/%Y:%k:%i:%s');

To handle the offset, you can do something like this:

select CONVERT_TZ(STR_TO_DATE('11/Jun/2006:06:28:16', '%d/%b/%Y:%k:%i:%s'), 'GMT', '-04:00');





2007-10-26, 00:26:30
anonymous from India  
its not that much clear


Keywords:
2009-07-13, 06:18:51
anonymous from United States  
It is much clearer.
2015-02-01, 02:04:19
anonymous from United States  
Your's is a point of view where real inlgltieence shines through.

 

 

NEW: Optional: Register   Login
Email address (not necessary):

Rate as
Hide my email when showing my comment.
Please notify me once a day about new comments on this topic.
Please provide a valid email address if you select this option, or post under a registered account.
 

Show city and country
Show country only
Hide my location
You can mark text as 'quoted' by putting [quote] .. [/quote] around it.
Please type in the code:

Please do not post inappropriate pictures. Inappropriate pictures include pictures of minors and nudity.
The owner of this web site reserves the right to delete such material.

photo Add a picture: