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?

Interrogative

now() won't work in oracle. I think you might be somewhat outta luck on this one. – Headcloth

Yes. It seems CURRENT_TIMESTAMP is not as portable as #187072 suggests. GETDATE() works though. Thank you for the responses – Interrogative

CURRENT_TIMESTAMP is ANSI SQL, getdate() is not and will fail on Oracle – Baiel

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.

Drawtube

@a_horse_with_no_name Thanks, I have added them to the list of DBMS that support it, along with links the documentation. – Drawtube

DB2 also supports current_timestamp: publib.boulder.ibm.com/infocenter/db2luw/v9r5/topic/… – Baiel

SQL Fiddle links appear to be broken. – Roesler

@MikePartridge Thanks. SQL Fiddle has upgraded numerous times since this answer so I have just removed the reference to SQL Fiddle. – Drawtube

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.

Sevik