I spent a lot of time researching the pieces to make the following work and was wondering if it might be useful to anyone else. Your comments are appreciated. Thanks!

Problem: I log customer sessions and orders placed through a store. The store program gave me, in one case, the date in both epoch seconds and in string format. In the other case, just as a string. I wanted to get the dates into the mySQL database as DATE datatypes to make future use of the data more smooth (had just been using the strings). Unfortunately, it didn't appear that mySQL handles epoch seconds. Plus it is picky as to how dates may be enterred. So I had to do all the converting up front, in Perl.

Solution: I found bits and pieces of the information I needed at PerlMonks, on Google, on CPAN, etc. I wonder if it could save anyone else some time in the future?

Create the tables with datatypes:

#create Session table.
$sql_line = "create table Session (...date DATETIME NOT NULL, ... )";
#create Order table.
$sql_line = "create table Orders (...order_date DATE NOT NULL, ...)";
[download]

Convert date formats:

### This is useful for converting epoch seconds
use Time::localtime; 

### This is useful for converting strings to useable time formats 
### (installation did not work as advertised, however!)
use Date::Manip;  

sub SA2_getDate  {
    my ($line);
    my ($fieldvalue);
    my ($fieldname);
    $line = $_;
    ($fieldname,$fieldvalue) = split(/:\s\s*/, $line);
    chomp ($fieldvalue);
    ###string received in format like: 991751732 OR Tue Jun 05 2001
    ($fieldvalue) = split(/\sOR\s/, $fieldvalue); 
    $fieldvalue =~ s/(^\d*)\s*/$1/; 
    $fieldvalue = localtime($fieldvalue); #convert from epoch seconds.
    my $formatteddate = sprintf ("%04d%02d%02d %02d:%02d:%02d ", $fiel
+dvalue->year+1900, $fieldvalue->mon+1, $fieldvalue->mday, $fieldvalue
+->hour, $fieldvalue->min, $fieldvalue->sec); #make into format requir
+ed by mySQL
    if ($session{$currentsession}[0] eq '')  {
        $session{$currentsession}[0] = $formatteddate;
    }
}

sub SA8_getOrderDate  {
    my ($line);
    my ($fieldvalue);
    my ($fieldname);
    $line = $_;
    
    ###string received in format like: Tue Jun  5 14:59:46 2001
    ($fieldname,$fieldvalue) = split(/:\s\s*/, $line); 
    chomp ($fieldvalue);
    my $formatteddate = ParseDate ($fieldvalue); #Courtesy Date::Manip
    $formatteddate =~ s/\d\d:\d\d:\d\d$//;#don't need the seconds
    if ($session{$currentsession}[6] eq '')  {
        $session{$currentsession}[6] = $formatteddate;
    }
}
[download]

Write the SQL:

###stuff from the hash can now be inserted, as is, into mySQL
    print SQL "insert into Orders (..., order_date,...)";
    print SQL "values (...,'$order_date', ...);\n";
[download]

---

---

Posts are HTML formatted. Put <p> </p> tags around your paragraphs. Put <code> </code> tags around your code and data!

Titles consisting of a single word are discouraged, and in most cases are disallowed outright.

Read Where should I post X? if you're not absolutely sure you're posting in the right place.

Please read these before you post! —

• How do I compose an effective node title?
• How do I post a question effectively?
• Markup in the Monastery

Posts may use any of the Perl Monks Approved HTML tags:

a, abbr, b, big, blockquote, br, caption, center, col, colgroup, dd, del, details, div, dl, dt, em, font, h1, h2, h3, h4, h5, h6, hr, i, ins, li, ol, p, pre, readmore, small, span, spoiler, strike, strong, sub, summary, sup, table, tbody, td, tfoot, th, thead, tr, tt, u, ul, wbr

You may need to use entities for some characters, as follows. (Exception: Within code tags, you can put the characters literally.)

	For:		Use:
	&		&
	<		&lt;
	>		&gt;
	[		&#91;
	]		&#93;

Link using PerlMonks shortcuts! What shortcuts can I use for linking?

See Writeup Formatting Tips and other pages linked from there for more info.