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

i've gone just about crazy today, realizing all my code is off by a month...

Perl deals with months 0 based, while MySQL treats months 1 based. I'm assuming Oracle and other DBs do the same.

How do you all deal with this? Force a 0-based date into MySQL, instead of letting it compute FROM_UNIXTIME? or use a few substr() on dates you pull out to drop the month back 1?

maybe there's some builtin function that can handle this? i'm kind of stressed and not happy right now, realizing i wrote several thousand lines of code i've got to now edit.

Replies are listed 'Best First'.
Re: Perl and Compatibility w/SQL dates
by BrowserUk (Patriarch) on Sep 02, 2002 at 23:44 UTC

    Format your dates derived from Perl with POSIX::strftime(localtime()) before adding them to the DB. and Date::Manip::parsedate to convert retrieved dates back to unix-style epoch+ numbers when you retrive them.

    Well It's better than the Abottoire, but Yorkshire!
[reply]
[d/l]
[select]
Re: Perl and Compatibility w/SQL dates
by fglock (Vicar) on Sep 03, 2002 at 00:14 UTC

    several thousand lines of code i've got to now edit

    If you used a (perl) date function, you may be able to redefine that function to work the way you expected. You don't have to rewrite the program unless you really want to.

[reply]
      nope. lots of diff. code to edit -- i was just running various functions off the datetime stored in mysql -- nothing was reused

      i've just been find/replacing $row->{datetime} with &DATETIME_FIX($row->datetime) in bbedit on a folder

      thank god for bbedit

      i heart bbedit

[reply]
Re: Perl and Compatibility w/SQL dates
by screamingeagle (Curate) on Sep 03, 2002 at 03:02 UTC
[reply]
Re: Perl and Compatibility w/SQL dates
by lachoy (Parson) on Sep 03, 2002 at 11:29 UTC

    Many database systems will accept different date formats. Some of them will allow you to specify the format you're using as you're using it.

    More practically, check out Time::Piece, which not only has output formatting via a non-piggy-POSIX strftime but also input date parsing via strptime.

    Chris
    M-x auto-bs-mode

[reply]
[d/l]
[select]
Re: Perl and Compatibility w/SQL dates
by tfrayner (Curate) on Sep 03, 2002 at 10:15 UTC
[reply]
Re: Perl and Compatibility w/SQL dates
by Anonymous Monk on Sep 03, 2002 at 11:59 UTC
    I sidestep this by not using MySQL dates at all, but just saving a unix date integer as an INT. I do all my date processing etc in Perl, and this makes doing that the easiest, no conversion required at all. I haven't ever found a situation where the MySQL dating system did something that the integer doesn't.
[reply]
      Until your database/code get moved to a machine with a different epoch.....

      g_White

[reply]
      i'm actually saving dates as a unix timstamp AND a mysql date in 2 seperate columns. why? its handy having the unix timestamp right there... but you can't easily take care of mysql date functions with it... kept as a mysql date, mysql can spit out date functions faster and easier in 1 sql statement
[reply]
        This is a bit dangerous isn't it? What happens when a process accidentally updates one of the columns and not the other?

        It would be safer/easier to write a function (either in perl or the db) to convert between the two formats, and only store one of them. I've not used mysql much, but unix_timestamp() might be useful there.

        Regarding the original problem - getting thousands of lines into a project before discovering bugs like this is a good example of why writing tests as you go (or before you go in some cases) is such a sanity-saver. :)

[reply]

Back to Seekers of Perl Wisdom