Skip to main content

GETUTCDATE (SQL)

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

Synopsis

GETUTCDATE([precision])

Description

GETUTCDATE returns Universal Time Constant (UTC) date and time as a timestamp. Because UTC time is the same everywhere on the planet, does not depend on the local timezone and is not subject to local time variants (such as Daylight Saving Time), this function is useful for applying consistent timestamps when users in different time zones access the same database.

GETUTCDATE 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 UTC timestamp is being supplied to a field of data type %PosixTime, this timestamp value is returned in POSIXTIME data type format. For example, WHERE PosixField=GETUTCDATE() or INSERT INTO MyTable (PosixField) VALUES (GETUTCDATE()).

  2. If the current UTC timestamp is being supplied to a field of data type %TimeStamp, this timestamp value is returned in TIMESTAMP data type format. Its ODBC type is TIMESTAMP, LENGTH is 16, and PRECISION is 19. For example, WHERE TSField=GETUTCDATE() or INSERT INTO MyTable (TSField) VALUES (GETUTCDATE()).

  3. If the current UTC timestamp is being supplied without context, this timestamp value is returned in TIMESTAMP data type format. For example, SELECT GETUTCDATE().

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

Typical uses for GETUTCDATE are in the SELECT statement select list or in the WHERE clause of a query. In designing a report, GETUTCDATE can be used to print the current date and time each time the report is produced. GETUTCDATE is also useful for tracking activity, such as logging the time that a transaction occurred.

GETUTCDATE can be used in CREATE TABLE to specify a field’s default value.

Other SQL Functions

GETUTCDATE returns the current UTC date and time as a timestamp in either TIMESTAMP or POSIXTIME format.

All other timestamp functions return the local date and time: GETDATE, CURRENT_TIMESTAMP, NOW, and SYSDATE return the current local date and time as a timestamp in either TIMESTAMP or POSIXTIME format.

GETDATE and CURRENT_TIMESTAMP provide a precision argument.

NOW, argumentless CURRENT_TIMESTAMP, and SYSDATE do not provide a precision argument; they take the system-wide default time precision.

CURDATE and CURRENT_DATE return the current local date. CURTIME and CURRENT_TIME return the current local time. These functions use the DATE or TIME data type. None of these functions support precision.

A TIMESTAMP data type stores and displays its value in the same format. A POSIXTIME data type stores its value as an encoded 64-bit signed integer. The TIME and DATE data types store their values as integers in $HOROLOG format and can be displayed in a variety of formats.

Note that all InterSystems SQL timestamp 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 also use the ObjectScript $ZTIMESTAMP special variable. Note that you can set the precision for GETUTCDATE; $ZTIMESTAMP always returns a precision of 3.

Fractional Seconds Precision

GETUTCDATE can return up to nine digits of precision. The number of digits of precision returned is set using the precision argument. The default for the precision argument 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.

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

Arguments

precision

An optional 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.

Examples

The following example returns the current date and time as a UTC timestamp and as a local timestamp, both in TIMESTAMP format:

SELECT GETUTCDATE() AS UTCDateTime,
       GETDATE() AS LocalDateTime

The following example returns the current UTC date and time with fractional seconds having two digits of precision:

SELECT GETUTCDATE(2) AS DateTime

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

SELECT GETDATE(),GETUTCDATE()

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

UPDATE Orders SET LastUpdate = GETUTCDATE()
  WHERE Orders.OrderNumber=:ord

In the following example, the CREATE TABLE statement uses GETUTCDATE to set a default value for the OrderRcvd field:

CREATE TABLE Orders(
     OrderId     INT NOT NULL,
     ItemName    CHAR(40) NOT NULL,
     Quantity    INT NOT NULL,
     OrderRcvd   TIMESTAMP DEFAULT GETUTCDATE())

See Also

FeedbackOpens in a new tab