Skip to main content

UNIX_TIMESTAMP (SQL)

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

Synopsis

UNIX_TIMESTAMP([date-expression])

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

  • A date-expression in %Library.PosixTimeOpens in a new tab 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()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.

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.PosixTimeOpens in a new tab class provides a UnixTimeToLogical()Opens in a new tab method to convert a UNIX timestamp to a PosixTime timestamp, and a LogicalToUnixTime()Opens in a new tab 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.

Arguments

date-expression

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

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

See Also

UPPER (SQL)Next section
Previous sectionUCASE (SQL)
FeedbackOpens in a new tab