Skip to main content

CURRENT_TIMESTAMP (SQL)

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

Synopsis

CURRENT_TIMESTAMP 
CURRENT_TIMESTAMP(precision)

Arguments

Argument Description
precision A positive integer that specifies the time precision as the number of digits of fractional seconds. The default is 0 (no fractional seconds); this default is configurable.

CURRENT_TIMESTAMP returns the TIMESTAMP data type.

Description

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

CURRENT_TIMESTAMP returns the current local date and time for this timezone; it adjusts for local time variants, such as Daylight Saving Time.

CURRENT_TIMESTAMP can return a timestamp in either %TimeStamp data type format (yyyy-mm-dd hh:mm:ss.ffff) or %PosixTime data type format (an encoded 64-bit signed integer). The following rules determine which timestamp format is returned:

  1. If the current timestamp is being supplied to a field of data type %PosixTime, the current timestamp value is returned in POSIXTIME data type format. For example, WHERE PosixField=CURRENT_TIMESTAMP or INSERT INTO MyTable (PosixField) VALUES (CURRENT_TIMESTAMP).

  2. If the current timestamp is being supplied to a field of data type %TimeStamp, the current timestamp value is returned in TIMESTAMP data type format. For example, WHERE TSField=CURRENT_TIMESTAMP or INSERT INTO MyTable (TSField) VALUES (CURRENT_TIMESTAMP).

  3. If the current timestamp is being supplied without context, the current timestamp value is returned in TIMESTAMP data type format. For example, SELECT CURRENT_TIMESTAMP.

You can use $HOROLOG to store or return the current local date and time in internal format.

To change the default datetime string format, use the SET OPTION command with the various date and time options.

You can specify CURRENT_TIMESTAMP, with or without precision, as the field default value when defining a datetime field using CREATE TABLE or ALTER TABLE. CURRENT_TIMESTAMP can be specified as the field default value for a field of data type %Library.PosixTimeOpens in a new tab or %Library.TimeStampOpens in a new tab; the current date and time is stored in the format specified by the field’s data type.

Fractional Seconds Precision

CURRENT_TIMESTAMP has two syntax forms:

  • Without argument parentheses, CURRENT_TIMESTAMP is functionally identical to NOW. It uses the system-wide default time precision.

  • With argument parentheses, CURRENT_TIMESTAMP(precision), is functionally identical to GETDATE, except that the CURRENT_TIMESTAMP() precision argument is mandatory. CURRENT_TIMESTAMP() always returns its specified precision and ignores the configured system-wide default time precision.

Fractional seconds are always truncated, not rounded, to the specified precision.

  • In TIMESTAMP data type format, the maximum possible digits of precision is nine. The actual number of digits supported is determined by the precision argument, the configured default time precision, and the system capabilities. If you specify a precision larger than the configured default time precision, the additional digits of precision are returned as trailing zeros.

  • In POSIXTIME data type format, the maximum possible digits of precision is six. Every POSIXTIME value is computed using six digits of precision; these fractional digits default to zeros unless supplied. The actual number of non-zero digits supported is determined by the precision argument, the configured default time precision, and the system capabilities.

Configuring Precision

The default 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; precision digits in excess of the precision available on your system are returned as zeroes.

Date and Time Functions Compared

GETDATE and NOW can also be used to return the current local date and time as either a TIMESTAMP data type or a POSIXTIME data type value. GETDATE supports precision, NOW does not support precision.

SYSDATE is identical to CURRENT_TIMESTAMP, with the exception that SYSDATE does not support precision. CURRENT_TIMESTAMP is the preferred InterSystems SQL function; SYSDATE is provided for compatibility with other vendors.

All InterSystems SQL time and date functions except GETUTCDATE are specific to the local time zone setting. To get a universal (time zone independent) timestamp, you can use either GETUTCDATE to return the universal date and time as either a TIMESTAMP data type or a POSIXTIME data type value, or the ObjectScript $ZTIMESTAMP special variable.

To return just the current local date, use CURDATE or CURRENT_DATE. To return just the current local time, use CURRENT_TIME or CURTIME. These functions return their values in DATE or TIME data type. None of these functions support precision.

The TIMESTAMP data type storage format and display format are the same. The POSIXTIME data type storage format is an encoded 64-bit signed integer. The TIME and DATE data types store their values as integers in $HOROLOG format; when displayed in SQL they are converted to date or time display format. Embedded SQL returns them in logical (storage) format by default. You can change the Embedded SQL returned value format using the #sqlcompile select macro preprocessor directive.

You can use the CAST or CONVERT function to change the data type of dates and times.

Examples

The following example returns the current local date and time three different ways: in TIMESTAMP data type format with system default time precision, with a precision of two digits of fractional seconds, and in $HOROLOG internal storage format with full seconds:

SELECT 
   CURRENT_TIMESTAMP AS FullSecStamp,
   CURRENT_TIMESTAMP(2) AS FracSecStamp,
   $HOROLOG AS InternalFullSec

The following example sets a locale default time precision. The first CURRENT_TIMESTAMP specifies no precision; it returns the current time with the default time precision. The second CURRENT_TIMESTAMP specifies precision; this overrides the configured default time precision. The precision argument can be larger or smaller than the default time precision setting:

SELECT CURRENT_TIMESTAMP, CURRENT_TIMESTAMP(2)

The following example compares local (time zone specific) and universal (time zone independent) time stamps:

  SELECT CURRENT_TIMESTAMP,GETUTCDATE()

The following example sets the LastUpdate field in the selected row of the Orders table to the current system date and time. If LastUpdate is data type %TimeStamp, CURRENT_TIMESTAMP returns the current date and time as an ODBC timestamp; if LastUpdate is data type %PosixTime, CURRENT_TIMESTAMP returns the current date and time as an encoded 64-bit signed integer:

UPDATE Orders SET LastUpdate = CURRENT_TIMESTAMP
  WHERE Orders.OrderNumber=:ord

The following example creates a table named Orders, which records product orders received:

CREATE TABLE Orders (
     OrderId     INT NOT NULL,
     ClientId    INT,
     ItemName    CHAR(40) NOT NULL,
     OrderDate   TIMESTAMP DEFAULT CURRENT_TIMESTAMP(3),
     PRIMARY KEY (OrderId))

The OrderDate column contains the date and time that the order was received. It uses the TIMESTAMP data type and inserts the current system date and time as the default value using the CURRENT_TIMESTAMP function with a precision of 3.

See Also

FeedbackOpens in a new tab