CURRENT Operator

The CURRENT operator returns a DATETIME value with the date and time of day, showing the current instant.

If you do not specify a DATETIME qualifier, the default qualifier is YEAR TO FRACTION(3). The USEOSTIME configuration parameter specifies whether or not the database server uses subsecond precision when it obtains the current time from the operating system. For more information on the USEOSTIME configuration parameter, see your HCL OneDB™ Administrator's Reference.

You can use CURRENT in any context where a literal DATETIME is valid. (See Literal DATETIME). If you specify CURRENT as the default value for a column, it must be a DATETIME column and the qualifier of CURRENT must match the column qualifier, as the following example shows:

CREATE TABLE new_acct (col1 INT, col2 DATETIME YEAR TO DAY
   DEFAULT CURRENT YEAR TO DAY);

CURRENT is always evaluated in the database server where the current database is located. If the current database is in a remote database server, the returned value is from the remote host.

SQL is not a procedural language, and CURRENT might not execute in the lexical order of its position in a statement. You should not use CURRENT to mark the start, the end, nor a specific point in the execution of an SQL statement.

If you use the CURRENT operator in more than once in a single statement, identical values might be returned by each instance of CURRENT. You cannot rely on CURRENT to return distinct values each time it executes.

The returned value is based on the system clock and is fixed when the SQL statement that specifies CURRENT starts execution. For example, any call to CURRENT from inside the SPL function that an EXECUTE FUNCTION (or EXECUTE PROCEDURE) statement invokes returns the value of the system clock when the SPL function starts.

On UNIX™ and Linux™ systems, the precision of the value returned by the CURRENT operator is determined by its DATETIME Qualifier, which can range from a single time unit (such as MONTH TO MONTH) up to YEAR TO FRACTION (5). The system clock on Windows™, however, returns only millisecond precision. Even if you specify "FRACTION(5)" in the DATETIME Qualifier, the CURRENT operator on Windows supports no greater than "FRACTION(3)" precision.

If your platform does not provide a system call that returns the current time with subsecond precision, CURRENT returns a zero for the FRACTION field.

In the following example, the first statement uses CURRENT in a WHERE condition. The second statement uses CURRENT as an argument to the DAY function. The last query selects rows whose call_dtime value is within a range from the beginning of 2007 to the current instant:

DELETE FROM cust_calls WHERE res_dtime < CURRENT YEAR TO MINUTE;

SELECT * FROM orders WHERE DAY(ord_date) < DAY(CURRENT);

SELECT * FROM cust_calls WHERE call_dtime
   BETWEEN '2007-1-1 00:00:00' AND CURRENT;

For more information, see DATETIME Field Qualifier.