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

I've searched CPAN, but everything I can find (e.g., DateTime::Format::DBI) goes the other way, creating a datetime string from a DateTime object. I need to do the reverse, creating a DateTime object from a datetime string that comes from a MySQL database.

Of course, I could parse the datetime string and feed the individual bits to DateTime::new(), but shouldn't the various DateTime modules on CPAN provide a function to do this? I mean, I could write this easily enough, but it just astounds me that it isn't already there. Where did I miss it?

Alternately, if it's not there, how do I go about writing one and submitting it? I've not written a module as yet, much less anything worthy of CPAN... I'm willing to learn, and I have read the chapter in on h2xs in Effective Perl Programming, but I don't have that book here, and... my memory is imperfect, and... this seems like something useful enough to include in the existing DateTime::Format::DBI modules, and in short I don't really know where to begin. I can put together the function itself easily enough, but the rest of the details are daunting me.

Quidquid latine dictum sit altum viditur.
  • Comment on Converting MySQL datetime values to DateTime objects

Replies are listed 'Best First'.
Re: Converting MySQL datetime values to DateTime objects
by cees (Curate) on Jul 14, 2003 at 17:33 UTC

    I don't use the DateTime modules, but the Time::Piece::MySQL module will convert to and from MySQL date strings.

    use Time::Piece::MySQL;
my $time = Time::Piece->from_mysql_datetime( $mysql_datetime );

print $time->mysql_datetime;

    [download]

    You might be able to use it, or learn enough to write your own module.

    - Cees

[reply]
[d/l]
Re: Converting MySQL datetime values to DateTime objects
by pijll (Beadle) on Jul 14, 2003 at 20:45 UTC
    DateTime::Format::MySQL should be able to do this. From the manpage: 
    my $dt = DateTime::Format::MySQL->parse_datetime( '2003-01-16 23:12:01
+' );

    [download]
    There's also a parse_date() and a parse_timestamp() method.

    In general, if you really find something missing from a module, try contacting the author of that module first. You say you can write the function you want; send him that for inclusion in the next version of the module. Writing your own module just for that small bit of functionality you're missing should only be your last solution, when all else fails.

    For datetime modules you can also send your ideas, additions and patches to the datetime@perl.org mailing list. They can help you to get your code in the correct DateTime module or to write your own DateTime module.

[reply]
[d/l]

      ++

      I didn't see it because I was looking at DateTime::Format::DBI as a wrapper around DateTime::Format::MySQL and DateTime::Format::Pg, one that I assumed would wrap all their functionality, but the documentation for DateTime::Format::DBI doesn't mention any parse functions. DateTime::Format::MySQL::parse_datetime will do exactly what I wanted. Thanks!

      Quidquid latine dictum sit altum viditur.
[reply]
Re: Converting MySQL datetime values to DateTime objects (regex)
by tye (Sage) on Jul 14, 2003 at 19:09 UTC

    One thing I found helpful with PM is a simple regex that matches both a MySQL timestamp (which is a digit string) and MySQL datetime fields (which contains punctuation): 

        my( $yr, $mo, $day, $hr, $min, $sec )=
        $when =~ /(\d{4})(\d\d)(\d\d)(\d\d)(\d\d)(\d\d)/;

    [download]
    PerlMonks used to have separate code for dealing with timestamps vs. datetime fields.

    Then you can construct nearly any date/time object you wish from the individual values.

    Update: Gah! Serves me right for not looking at the code in question. The real/working code is more like: 

        my( $cc, $yr, $mo, $day, $hr, $min, $sec )=
        $when =~ /(\d\d)/g;
    $yr += $cc*100;

    [download]
    Sorry!

                    - tye
[reply]
[d/l]
[select]
      what am i doing wrong here? i've tried it w/ and w/o the parens around the regexp. 
      use strict;
use warnings;

my $when = '2003-07-14 21:39:26.357';
my ( $yr, $mo, $day, $hr, $min, $sec ) =
    ( $when =~ /(\d{4})(\d\d)(\d\d)(\d\d)(\d\d)(\d\d)/ );
print "( $yr, $mo, $day, $hr, $min, $sec )\n";

__DATA__
Use of uninitialized value in concatenation (.) or string at C:\dev\sc
+ripts\reg.pl line 6.
Use of uninitialized value in concatenation (.) or string at C:\dev\sc
+ripts\reg.pl line 6.
Use of uninitialized value in concatenation (.) or string at C:\dev\sc
+ripts\reg.pl line 6.
Use of uninitialized value in concatenation (.) or string at C:\dev\sc
+ripts\reg.pl line 6.
Use of uninitialized value in concatenation (.) or string at C:\dev\sc
+ripts\reg.pl line 6.
Use of uninitialized value in concatenation (.) or string at C:\dev\sc
+ripts\reg.pl line 6.
( , , , , ,  )

      [download]
      can somebody help a (clueless) brother out?
      Update:
      cool cool cool- never knew you could return an array from a regexp. here's another way too: 
      my ( $yr, $mo, $day, $hr, $min, $sec ) = 
( $when =~ /(\d{4})\D*(\d\d)\D*(\d\d)\D*(\d\d)\D*(\d\d)\D*(\d\d)/ );

      [download]
[reply]
[d/l]
[select]
            my $when = '2003-07-14 21:39:26.357';
    my ( $yr, $mo, $day, $hr, $min, $sec ) =
        ( $when =~ /(\d{4})(\d\d)(\d\d)(\d\d)(\d\d)(\d\d)/ );

        [download]
        You have to match all of the punctuation in your source string. E.g., to match 
            my $when = '2003-07';

        [download]
        you would use 
            my ($yr,$mo) = $when =~ /^(\d\d\d\d)-(\d\d)/;
                                        ^

        [download]
        Fixing the complete regex is a variation on this theme.

[reply]
[d/l]
[select]
Re: Converting MySQL datetime values to DateTime objects
by mojotoad (Monsignor) on Jul 15, 2003 at 03:59 UTC
    According to the source code, the new() constructor for DateTime::Format::DBI is just a front-end factory that will return one of the format classes based on the nature of your $dbh. So in your case, what you're actually getting is an instance of DateTime::Format::MySQL, except you weren't supposed to have to think about that.

    The docs list DateTime::Format::MySQL in the 'see also' section. This relationship with the other format classes should probably be more explicitly mentioned for the benefit of those not accustomed to factories (or polymorphism...depending on how much the various format classes agree with each other's API.)

    I'll forward the observation to the DateTime Project mailing list.

    Matt

[reply]
      what you're actually getting is an instance of DateTime::Format::MySQL, except you weren't supposed to have to think about that.

      Yes, I gathered that much, but shouldn't all of the classes it might return (depending on the db backend used) have a parse_datetime function, and shouldn't usage for this function be normalised across all of them and documented in DateTime::Format::DBI's documentation? It shows how to do the conversion the other direction, without caring which database is on the backend. Why not in this direction also? I was assuming that the underlying classes all simply implemented the things required by the general class in whatever manner is necessary for their respective backends. Apparently this is not the case?

      Quidquid latine dictum sit altum viditur.
[reply]
        Here is the response to my note on the mailing list -- you might find it of interest. Now's a good time to get involved with the DateTime project!

        Matt

[reply]

Back to Seekers of Perl Wisdom