DB agnostic SQL for CURRENT_TIMESTAMP

问题:

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?

回答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.