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

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.

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");
[download]

Thanks. That seems to point the way forward.

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
[download]

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

DateTime->now with the appropriate DB format driver.