Is there an ANSI/ISO SQL syntax to obtain the current time?-CodePudding

I would like to get a DB's current time, using Standard SQL. The result must either be in UTC or come with timezone information. Is that possible? If yes, how?

Apparently, there's CURRENT_TIMESTAMP, which is ANSI SQL, but is there an ANSI SQL syntax to retrieve just that? Some DBMS support select CURRENT_TIMESTAMP, others require select CURRENT_TIMESTAMP from DUAL. Any way to do this DBMS-independent?

CodePudding user response：

The ANSI/ISO SQL standard has CURRENT_TIME to get time with timezone information, and LOCALTIME to get time without timezone information.

However, many products have their own functions instead.

CodePudding user response：

Short answer:

~~There is no standard function to do this (as far as I know). Each vendor implemented their own functions to handle date and time, including retrieving the current time.~~

See @jarlh's answer on this.

Possible solution (given you have control over the database):

You can create a view containing one record using (or a function proxying) the vendor specific function and use that as a source in your application.

For example (SQL Server)

CREATE VIEW dbo.[DateTime] AS
SELECT
  SYSUTCDATETIME() AS UTCDateTime
;

or in (MySQL)

CREATE VIEW DateTime AS
SELECT
  UTC_TIMESTAMP as UTCDateTime
;

CodePudding user response：

Assuming you use SQL server:

SELECT CONVERT(varchar(10), GETUTCDATE(), 108)

CodePudding user response：

The ANSI SQL way of doing this is to use the VALUES statement. This is similar to the SELECT statement, in that it returns a result set. But it doesn't reference any table. It's the standard way of doing something like what Oracle's DUAL pseudo-table is used for.

Here's a demo using MySQL 8.0:

mysql> VALUES ROW(CURRENT_TIMESTAMP);
 --------------------- 
| column_0            |
 --------------------- 
| 2021-11-22 14:56:58 |
 ---------------------

See: