Caché SQL Reference
UNIX_TIMESTAMP
[Home] [Back] [Next]
InterSystems: The power behind what matters   
Class Reference   
Search:    

A date/time function that converts a date expression to a UNIX timestamp.
Synopsis
UNIX_TIMESTAMP(date-expression)
Arguments
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:
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:
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()
 
See Also