Skip to main content

This is documentation for Caché & Ensemble.

For information on converting to InterSystems IRIS, see the InterSystems IRIS Adoption Guide and the InterSystems IRIS In-Place Conversion Guide, both available on the WRC Distributions page (login required).

Previous sectionNext section


A date/time function that converts a date expression to a UNIX timestamp.




Argument Description
date-expression Optional — An expression that is the name of a column, the result of another scalar function, or a date or timestamp literal. UNIX_TIMESTAMP does not convert from one timezone to another. If date-expression is omitted, defaults to the current UTC timestamp.


UNIX_TIMESTAMP returns a UNIX® timestamp, the count of seconds (and fractional seconds) since '1970-01-01 00:00:00'.

If you do not specify date-expression, date-expression defaults to the current UTC timestamp. Therefore, UNIX_TIMESTAMP() is equivalent to UNIX_TIMESTAMP(GETUTCDATE(3)), assuming the system-wide default precision of 3.

If you specify date-expression, UNIX_TIMESTAMP converts the specified date-expression value to a UNIX timestamp, calculating the count of seconds to that timestamp. UNIX_TIMESTAMP can return a positive or negative count of seconds.

UNIX_TIMESTAMP returns its value as data type %Library.Numeric. It can return fractional seconds of precision. If you do not specify date-expression, it takes the currently configured system-wide precision. If you specify date-expression it takes its precision from date-expression.

date-expression Values

The optional date-expression can be specified as:

  • An ODBC timestamp value (data type %Library.TimeStamp): YYYY-MM-DD HH:MI:SS.FFF

  • A $HOROLOG date value (data type %Library.Date): a count of the number of days since December 31, 1840, where day 1 is January 1, 1841.

  • A $HOROLOG timestamp, with or without fractional seconds: 64412,54736.

UNIX_TIMESTAMP does not perform timezone conversion: if date-expression is in UTC time, UTC UnixTime is returned; if date-expression is local time, a local UnixTime value is returned.

Fractional Seconds Precision

Fractional seconds are always truncated, not rounded, to the specified precision. A date-expression in %Library.TimeStamp data type format can have a maximum precision of nine. The actual number of digits supported is determined by the date-expression 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.

Configuring Precision

The default precision can be configured using the following:

  • SET OPTION with the TIME_PRECISION option.

  • The $SYSTEM.SQL.SetDefaultTimePrecision() method call.

  • Go to the Management Portal, select System, Configuration, General SQL Settings. 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

UNIX_TIMESTAMP returns date and time expressed as a number of elapsed seconds from an arbitrary date.

GETUTCDATE returns a universal (independent of time zone) date and time as a %TimeStamp (ODBC timestamp) data type value.

You can also use the ObjectScript $ZTIMESTAMP special variable to return a universal (time zone independent) timestamp.

The ObjectScript $ZDATETIME function dformat -2 takes a Caché $HOROLOG date and returns a UNIX timestamp; $ZDATETIMEH dformat -2 takes a UNIX timestamp and returns a Caché %HOROLOG date. These ObjectScript functions convert local time to UTC time. UNIX_TIMESTAMP does not convert local time to UTC time.


The following example returns a UTC UNIX timestamp. The first select-item takes the date-expression default, the second specifies an explicit UTC timestamp:

Copy code to clipboard

The following example returns a local UNIX timestamp for the current local date and time, and a UTC UNIX timestamp for a UTC date and time value. The first select-item specifies the local CURRENT_TIMESTAMP, the second specifies $HOROLOG (local date and time), the third specifies the current UTC date and time:

Copy code to clipboard

The following example compares UNIX_TIMESTAMP (which does not convert local time) and $ZDATETIME (which does convert local time):

  SET tStatement = ##class(%SQL.Statement).%New()
  SET qStatus = tStatement.%Prepare(myquery)
   IF qStatus'=1 {WRITE "%Prepare failed:" DO $System.Status.DisplayError(qStatus) QUIT}
  SET rset = tStatement.%Execute(unixutc)
  DO rset.%Display()
Copy code to clipboard

See Also