Converting to MySQL-Friendly Timestamps in Perl

Album Cover: Graduation

"Y'all pop the trunk, I pop the hood (Ferrari)."
Kanye West / Good Life

Posted on May 27, 2006 11:22 AM in Programming
Warning: This blog entry was written two or more years ago. Therefore, it may contain broken links, out-dated or misleading content, or information that is just plain wrong. Please read on with caution.

I'm working on rejuvenating some really old website content today. The last time I used it, it was stored in an Access database. Now I need to use it in a MySQL database. The problem I'm running into, and the problem I frequently run into when converting from one database to another, is that the old date formats do not jive with the new database.

Because I'm using a quick and dirty Perl script to extract and scrub all of the old content, I figured, why not use Perl to convert the old dates to the newer format as well? Luckily for me, the Date::Parse Perl module and Perl's built-in sprintf() function were all the tools I needed.

I used Date::Parse's strptime() function as the equivalent to PHP's strtotime() function. All I had to do was pass in my old Access-formatted date, and get a handful of Perl date/time components back in return:

my ($s, $m, $h, $d, $mon, $yr, $tz) = strptime($access_date);

If you're familiar with Perl date/time components, however, you'll remember that there are a few funky tweaks you have to make to the year and month (in addition to adding some padding here and there) to make those components human- and MySQL-friendly. The details of those tweaks are covered over at, so I won't go into it here. I will say, though, that I was able to use sprintf() to get the components into the format necessary for importing into MySQL:

$stamp = sprintf("%4d-%02d-%02d %02d:%02d:%02d", $yr+1900, $mon+1, $d, $h, $m, $s);

Using these two techniques, I was successfully able to convert dates that look like 9/4/1997 0:00:42 AM into dates that look like 1997-09-04 00:00:42.


wallens on November 25, 2016 at 11:30 PM:

That is truly decent to listen. much obliged to you for the upgrade and good fortunes. have a peek at this web-site


Post Comments

If you feel like commenting on the above item, use the form below. Your email address will be used for personal contact reasons only, and will not be shown on this website.


Email Address:



Check this box if you hate spam.