I'd like to be able to get the current date/time (timestamp) from the DB sever I have a Connection to, regardless of the DB. (Ideally, I'd like something that works for Oracle, DB2, SQL Server, and Postgres).
Searching the internet, I've seen allusions to the LOCALTIMESTAMP function and to NOW() and to CURRENT_TIMESTAMP, but I've seen no clarity as to whether any of these truly works in an interoperable fashion across RDBMSes.
I'm willing to code up alternatives for different DBs if necessary, but haven't even seen any clarity on the best way to do this for the individual DB vendors. And surely if it needs to be done differently for each there must be some Java library that wraps JDBC connections and does this?
Aside from figuring out which SQL function to use, I'm also wondering if there's a standard way to do a SQL query that is just a function invocation -- some DBs seem to support
SELECT fn();
...but Oracle seems to require adding
...FROM DUAL;
I know that the SQL "standard" is a bit dodgy, but I wondered if anyone who has more experience than me with working across databases and with JDBC and related libraries might be able to point me in the right direction.
thanks!
Function calls differs on various database. While with PostgreSQL it is simple SELECT fun_name() in Oracle you must add FROM dual. In other database I work with: Informix you call procedure/function not with SELECT but with execute procedure/function.
I think the simplest solution is to check during run time what database is currently in use and then build query required by that database.
