Querying IIQ (epoch) dates in Microsoft SQL Server (MSSQL)

SailPoint stores many (all?) dates as Unix timestamps rather than a date-specific data type.

MSSQL does not have Oracle or MySQL’s nice parsing functions for handling Unix timestamps. Further complicating matters, all Unix timestamps are GMT+0 (UTC) offset by definition, which may be different from your IIQ server’s or database server’s time zone (which themselves don’t have to be the same).

Here’s the pattern to query a date, e.g. from SPT_IDENTITY:

DATEADD(S, SPT_IDENTITY.CREATED / 1000, dateadd(s, 0, '1970-01-01') at time zone 'UTC') at time zone 'your time zone'

What you will get is an MSSQL DATETIMEOFFSET object, which consists of a date, a time, and a timezone offset. These can be safely compared to other DATETIMEOFFSET objects (taking time zone into account) or other non-offset MSSQL date types (which will be assumed to be at the database’s local time zone offset).

For example, if the value of CREATED is 1562954414006, the conversion above using “Eastern Standard Time” will give 2019-07-12 14:00:14.000 -04:00. (Observe that SQLServer automatically takes care of Daylight Savings Time conversions.)

Note: You won’t be able to use this structure from Hibernate HQL, since it exposes a limited subset of database functions OOTB. This could be used, for example, with a SQL-based report, custom code in a rule, or an administrator query.

1 Like