UNIX_TIMESTAMP (SQL)

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

Synopsis

UNIX_TIMESTAMP([date-expression])

Arguments

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.

Description

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 PosixTime timestamp value (data type %Library.PosixTime): an encoded 64-bit signed integer.
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.
A date-expression in %Library.PosixTime data type format has a maximum precision of six. Every POSIXTIME value is computed using six digits of precision; these fractional digits default to zeros unless supplied.

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() method configuration option DefaultTimePrecision. To determine the current setting, call $SYSTEM.SQL.CurrentSettings() 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

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

TO_POSIXTIME returns an encoded 64-bit signed (a %PosixTime timestamp) that is calculated from the UNIX timestamp.

GETUTCDATE returns a universal (independent of time zone) date and time as either a %TimeStamp (ODBC timestamp) data type or a %PosixTime (encoded 64-bit signed integer) data type value. A %PosixTime value is calculated from the corresponding UNIX timestamp value. The %PosixTime encoding facilitates rapid timestamp comparisons and calculations. The %Library.PosixTime class provides a UnixTimeToLogical() method to convert a UNIX timestamp to a PosixTime timestamp, and a LogicalToUnixTime() method to convert a PosixTime timestamp to a UNIX timestamp. Neither of these methods perform timezone conversion.

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

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

Examples

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

SELECT 
   UNIX_TIMESTAMP() AS DefaultUTC,
   UNIX_TIMESTAMP(GETUTCDATE(3)) AS ExplicitUTC

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:

SELECT 
   UNIX_TIMESTAMP(CURRENT_TIMESTAMP(2)) AS CurrTSLocal,
   UNIX_TIMESTAMP($HOROLOG) AS HorologLocal,
   UNIX_TIMESTAMP(GETUTCDATE(3)) AS ExplicitUTC

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

  SET unixutc=$ZDATETIME($HOROLOG,-2)
  SET myquery = "SELECT UNIX_TIMESTAMP($HOROLOG) AS UnixLocal,? AS UnixUTC"
  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()