DB agnostic SQL for CURRENT_TIMESTAMP

Question

I'm currently using Oracle, but most of the SQL is db agnostic. One exception is CURRENT_TIMESTAMP which fails in MSSQL. Is is possible to move to a more portable syntax for this too?

Answer 1

According to their documentation the following vendors support CURRENT_TIMESTAMP.

• Oracle
• Postgresql
• MySQL
• SQL Server
• Firebird
• H2
• HSQLDB
• DB2
• Vertica
• InterSystems Caché
• Doctrine
• Teradata

In summary, CURRENT_TIMESTAMP is the method stated in the SQL Standard, a summary of which is below, and if you are looking for DB agnostic SQL the best you can really do is stick to the standard, although this does not guarantee that anything will be fully DB agnostic:

6.32 <datetime value function>

Function

Specify a function yielding a value of type datetime.

Format

<current date value function> ::= CURRENT_DATE`
<current time value function> ::= CURRENT_TIME [ <left paren> <time precision> <right paren> ]
<current local time value function> ::= LOCALTIME [ <left paren> <time precision> <right paren> ]
<current timestamp value function> ::= CURRENT_TIMESTAMP [ <left paren> <timestamp precision> <right paren> ]
<current local timestamp value function> ::= LOCALTIMESTAMP [ <left paren> <timestamp precision> <right paren> ]

General Rules

1) The s CURRENT_DATE, CURRENT_TIME, and CURRENT_TIMESTAMP respectively return the current date, current time, and current timestamp; the time and timestamp values are returned with time zone displacement equal to the current default time zone displacement of the SQLsession.

Answer 2

The jOOQ user manual can be useful for these kinds of lookups, as well as the SQL translation website. From the manual:

-- Access
now()

-- Sybase ASE
current_bigdatetime()

-- MariaDB, MemSQL, MySQL, Snowflake
current_timestamp()

-- BigQuery, CockroachDB, CUBRID, Db2, Derby, EXASOL, Firebird, H2, HANA, HSQLDB,
-- Ignite, Ingres, Oracle, PostgreSQL, Redshift, SQLite, SQL Server, Teradata
-- Vertica
CURRENT_TIMESTAMP

-- Informix
CURRENT

-- Sybase SQL Anywhere
CURRENT TIMESTAMP

Disclaimer: I work for the company behind jOOQ.