Beefy Boxes and Bandwidth Generously Provided by pair Networks
laziness, impatience, and hubris
 
PerlMonks  

NOW SQL portability

by SilasTheMonk (Chaplain)
on Dec 22, 2009 at 10:08 UTC ( #813842=perlquestion: print w/ replies, xml ) Need Help??
SilasTheMonk has asked for the wisdom of the Perl Monks concerning the following question:

I have some SQL that works fine on mysql but which uses the "NOW()" function. However this does not work on SQLite because by default that has no "NOW()" function. The DBD::SQLite man page describes how you can add a NOW function, but that only fixes SQLite not the general portability problem. Looking at perlport scares me even more. The definition of epoch varies from OS to OS so you cannot even rely on the time function. So the question is what is the most portable and lightweight SQL compatible way of generating today's date?

Comment on NOW SQL portability
Re: NOW SQL portability
by almut (Canon) on Dec 22, 2009 at 10:31 UTC
    The definition of epoch varies from OS to OS

    Instead of storing the number of seconds relative to some OS-dependent idea of epoch, you could convert it to a proper date/time (e.g. in ISO format), and store that in the DB.

      Yes I know this. However my question was what is the most lightweight and portable way of getting today's date in an SQL compatible manner? Also the suggested code in DBD::SQLite uses "time" directly.
        portable way of getting today's date in an SQL compatible manner?

        Not sure if there is a portable way of getting a timestamp in SQL (such as MySQL's NOW()).  AFAIK, there's not even a 100% portable way of telling a DB to store one... (for example, see this for a quick comparison).

Re: NOW SQL portability
by herveus (Parson) on Dec 22, 2009 at 12:31 UTC
    Howdy!

    SQL portability can be a real red herring, given the variations found across the range of implementations. You have to ask yourself how many different flavors of SQL you want to try to support. You may have no choice but to have slightly different code bases for each flavor.

    I don't know if there is a portable way for getting "today's date". Oracle uses "sysdate"; Informix uses "current...". MSSQL and Sybase may actually use the same function, given their common heritage.

    yours,
    Michael
Re: NOW SQL portability
by Your Mother (Canon) on Dec 22, 2009 at 15:30 UTC

    I cannot remember if this is a final version or not (I pulled it out of my gmail note bin) but I used this or something like it not too long ago. DBD::SQLite::Cookbook. Some simple stuff like this can work but I generally agree that chasing engine agnostic SQL is a losing proposition. :(

    use Date::Calc; $dbh->func("NOW", 0, sub { sprintf('%d-%02d-%02d %02d:%02d:%02d', Date::Calc::Today_and_Now) }, "create_aggregate");
      Thanks. That seems to point the way forward.
Re: NOW SQL portability
by rpnoble419 (Pilgrim) on Dec 22, 2009 at 21:07 UTC

    The NOW() function is not part of the ANSI SQL function standard.

    Your best solution is to use perl to read the local OS date and time and store the result in your database. I use the Date::Calc Today_and_Now function. This works for basic inserts, but you would need to use the function you described above if you are using a trigger to update your database.

      Yes, CURRENT_TIMESTAMP (without parens) is what the SQL standard (SQL2008) prescribes (and it looks like mysql does actually implement it).

      (In law-abiding systems like postgres, now() and current_timestamp are synonymous)

      However, I don't see any CURRENT_TIMESTAMP in sqlite.

      update:

      I now actually tried it: turns out that SQLite does implement current_timestamp after all (without parens).

      DBD::SQLite::VERSION = 1.27

      So, the best solution for the OP: use the standard current_timestamp (in both mysql and sqlite) instead of non-standard now().

Re: NOW SQL portability
by stonecolddevin (Vicar) on Dec 23, 2009 at 20:01 UTC

Log In?
Username:
Password:

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

How do I use this? | Other CB clients
Other Users?
Others making s'mores by the fire in the courtyard of the Monastery: (9)
As of 2014-08-20 08:05 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    The best computer themed movie is:











    Results (107 votes), past polls