How do you format dates for entry into MySql?

glamring has asked for the wisdom of the Perl Monks concerning the following question:

Questions: 1. How do you take a date in the form 'Month YYYY' and
convert it to the form 'YYYY-MM-DD'?
(I am assuming that DD will be 00 since I do not have any days to convert)
Example: March 1990 => 1990-03-00

2. How do you reverse the process, and convert
from'YYYY-MM-DD' to 'Month YYYY'?
Example: 1990-03-00 => March 1990

Background: I am using the CueCat sample code from Brent Michalski
(moderator of the Perl channel for Dr. Dobb's Online) to catalog my book collection.
The code is located at: Roll Your Own Data Capture

This code in and of itself works just fine, but I want to modify the code to reformat the date
into the format expected by MySql, so I can store the data in the table with a DATE datatype,
instead of VARCHAR, as I am using now. (I want to be able to sort each author's work in
chronological order, so Book 1 of a trilogy will show up before Book 2, etc)

I am assuming that since the code already parses out the date and puts it into a variable,
I just need to write a subroutine that will reformat this value. I just don't know what
Regular Expression or function to use to do the conversion. I've looked at Date::Ordinal,
which looks like what I need, but I don't understand how to use it.

Comment on How do you format dates for entry into MySql?

Replies are listed 'Best First'.
Re: How do you format dates for entry into MySql? by George_Sherston (Vicar) on Sep 01, 2001 at 00:16 UTC
I don't know if this is anything to be proud of, but how I format dates in MySQL is as the nine-digit `time` integer. So sorting works fine, and I don't have to learn how to do SQL dates and I have something readily manipulable back home in perl. And it hardly takes any space. For a limited period, you can store them in an INT(9) field, though for future-proofing, as the saintly merlyn points out, INT(10) might be worth the extra bytes... § George Sherston	[reply] [d/l]
Re: How do you format dates for entry into MySql? by maverick (Curate) on Sep 01, 2001 at 01:49 UTC
Here's some untested code: `my %xref = ( 'January' => "01", 'Feburary' => "02", . . . 'December' => "12"); my ($month, $year) = split(/\s+/,$my_input_date); my $insert_date = $year. "-" . $xref{$month} . "-01";` [download] MySql will correctly sort by dates stored in date columns without having to do anything special. To reverse the process, do something like: `my %xref2 = ("01" => "January", "02" => "Feburary", . . "12" => "December"); my ($year,$month,$day) = split(/-/,$input_date); my $orig_date = "$xref2{$month} $year";` [download] Hope this helps. /\/\averick perl -l -e "eval pack('h*','072796e6470272f2c5f2c5166756279636b672');"	[reply] [d/l] [select]
Re: How do you format dates for entry into MySql? by lestrrat (Deacon) on Sep 01, 2001 at 01:57 UTC
As George_Sherston says, it's usually more convenient and efficient to store the dates as an integer, if you plan to process it in your perl code later. As seen in this node I actually could cut down the process time by a third when I switched from using the Postgres' timestamp type to an integer As for the conversion, I think you get the idea from maverick's post :-)	[reply]
Re: How do you format dates for entry into MySql? by lo_tech (Scribe) on Sep 01, 2001 at 04:41 UTC
Here is a solution that might work for you. It makes use of the Date Calc module available on CPAN. You can also brush up by reading the review The following code will perform the date transmogrfication you need. It assumes that you really do want the days to be '00' and that you send the dates in the format you mentioned in your post. I'm paranoid by nature and would assume nothing. I'd check for a valid date, probably using `check_date($year,$month,$day)` before returning the value form the sub. Since this is a matter of neurosis, leave me to mine and I wont mention yours. ^.^ #!/usr/bin/perl -w use strict; use Date::Calc qw(Month_to_Text Decode_Month); sub mangle_dates { my $date = shift; if ( $date =~ /-/ ) { # we received a date with '-' in it, assume "YYYY-MM-DD" format my ($year,$month,$day) = split (/-/,$date); my $string = sprintf("%s %04d", Month_to_Text($month), $year); print "'$date' converted to '$string'\n"; return $string; } elsif ($date =~ / /) { my ($month,$year) = split (/ /,$date); # we received a date with ' ' in it, assume "MMM YYYY" format my $string = sprintf("%04d-%02d-00", $year, Decode_Month($month), +); print "'$date' converted to '$string'\n"; return $string; } } &mangle_dates("1999-05-15"); &mangle_dates("March 1925"); [download]	[reply] [d/l] [select]
Re: How do you format dates for entry into MySql? by nmerriweather (Friar) on Sep 05, 2002 at 18:57 UTC
you dont have to convert mysql does it for you! `$SQLstatement = «ENDSQL INSERT INTO table (datetime) VALUES ('FROM_UNIXTIME($time)') ENDSQL` [download]	[reply] [d/l]