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

perl
mysql (3)
perl CGI (3)

Exchange Links

About this site

Links to us

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

6 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);

Send a message to
Subject

Comments:

2005-11-15, 02:57:55

anonymous from India

2006-03-21, 09:25:22

anonymous from India

very very very very very very bad

2006-03-21, 10:50:37

anonymous from India

bad

2006-06-17, 15:48:07

anonymous from United States

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

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. Add a picture:


	Search: