CURRENT_TIME (SQL)

A date/time function that returns the current local time.

Synopsis

CURRENT_TIME CURRENT_TIME(precision)

Description

CURRENT_TIME returns the current local time for this timezone. It adjusts for local time variants, such as Daylight Saving Time.

CURRENT_TIME takes either no arguments or a precision argument. Empty argument parentheses are not permitted.

CURRENT_TIME in Logical mode returns the current local time in $HOROLOG format; for example, 37065. CURRENT_TIME in Display mode returns the current local time in the default format for the locale; for example, 10:18:27.

To change the default time format, use the SET OPTION command with the TIME_FORMAT and TIME_PRECISION options. You can configure fractional seconds of precision, as described below.

To return just the current time, use CURRENT_TIME or CURTIME. These functions return their values in TIME data type. The CURRENT_TIMESTAMP, GETDATE and NOW functions can also be used to return the current date and time as a TIMESTAMP data type.

Note that all InterSystems SQL time and date functions except GETUTCDATE are specific to the local time zone setting. To get a current timestamp that is universal (independent of time zone) you can use GETUTCDATE or the ObjectScript $ZTIMESTAMP special variable.

These data types perform differently when using embedded SQL. The TIME data type stores values as integers in $HOROLOG format (as the number of seconds since midnight); when displayed in SQL they are converted to time display format; when returned from embedded SQL they are returned as integers. A TIMESTAMP data type stores and displays its value in the same format. You can use the CAST or CONVERT function to change the datatype of times and dates.

CURRENT_TIME can be used as a default specification keyword in CREATE TABLE or ALTER TABLE. CURRENT_TIME cannot specify a precision argument when used as a default specification keyword.

Fractional Seconds Precision

CURRENT_TIME can return up to nine digits of fractional seconds of precision. The default for the number of digits of precision can be configured using the following:

SET OPTION with the TIME_PRECISION option.
The system-wide $SYSTEM.SQL.Util.SetOption()Opens in a new tab method configuration option DefaultTimePrecision. To determine the current setting, call $SYSTEM.SQL.CurrentSettings()Opens in a new tab which displays Default time precision; the default is 0.
Go to the Management Portal, select System Administration, Configuration, SQL and Object Settings, SQL. View and edit the current setting of Default time precision for GETDATE(), CURRENT_TIME, and CURRENT_TIMESTAMP.

Specify an integer 0 through 9 (inclusive) for the default number of decimal digits of precision to return. The default is 0. The actual precision returned is platform dependent; digits of precision in excess of the precision available on your system are returned as zeroes.

Examples

The following example returns the current system time:

SELECT CURRENT_TIME

The following example returns the current system time with three digits of fractional seconds precision:

SELECT CURRENT_TIME(3)

The following Embedded SQL example returns the current time. Because this time is stored in $HOROLOG format, it is returned as an integer:

  &sql(SELECT CURRENT_TIME INTO :a)
  IF SQLCODE'=0 {
    WRITE !,"Error code ",SQLCODE }
  ELSE {
    WRITE !,"Current time is: ",a }

The following example sets the LastCall field in the selected row of the Contacts table to the current system time:

UPDATE Contacts SET LastCall = CURRENT_TIME
  WHERE Contacts.ItemNumber=:item

CURRENT_TIME (SQL)

Synopsis

Description

Fractional Seconds Precision

Examples

See Also