Beefy Boxes and Bandwidth Generously Provided by pair Networks
Your skill will accomplish
what the force of many cannot
 
PerlMonks  

Perl and Compatibility w/SQL dates

by nmerriweather (Friar)
on Sep 02, 2002 at 23:26 UTC ( [id://194651]=perlquestion: print w/replies, xml ) Need Help??

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!
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.

      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

Re: Perl and Compatibility w/SQL dates
by screamingeagle (Curate) on Sep 03, 2002 at 03:02 UTC
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

Re: Perl and Compatibility w/SQL dates
by tfrayner (Curate) on Sep 03, 2002 at 10:15 UTC
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.
      Until your database/code get moved to a machine with a different epoch.....

      g_White

      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
        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. :)

Log In?
Username:
Password:

What's my password?
Create A New User
Domain Nodelet?
Node Status?
node history
Node Type: perlquestion [id://194651]
Approved by Zaxo
help
Chatterbox?
and the web crawler heard nothing...

How do I use this?Last hourOther CB clients
Other Users?
Others having a coffee break in the Monastery: (4)
As of 2024-03-29 06:37 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    No recent polls found