UNIX_TIMESTAMP

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.TimeStampOpens in a new tab): YYYY-MM-DD HH:MI:SS.FFF
A $HOROLOG date value (data type %Library.DateOpens in a new tab): 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.TimeStampOpens in a new tab 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()Opens in a new tab 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.

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):

  ZNSPACE "SAMPLES"
  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()