How To: Query a geodatabase archive class for a given moment in time using SQL


An archive class can be accessed, using direct SQL, to retrieve attributes or spatial geometries, by setting the correct predicate filter using the gdb_from_date and gdb_to_date attributes.


A geodatabase archive class maintains every object for an archive enabled objectclass or feature class, and each occurrence of every modified object.

The archive class uses two additional attributes for maintaining a time stamp when an object was inserted, updated, or deleted. The default DATE attribute gdb_from_date records the time stamp when the object is initially created or was last updated. The default DATE attribute gdb_to_date records the time stamp when the object is retired. If the current value for the gdb_to_date is infinity, then the object remains active (the object's current representation).

If the objective is to query the archive class for a given moment, the following example, in Oracle using SQL*Plus, demonstrates how to obtain all the objects that are current as of February 25, 2008 with a spatial filter.

SQL> SELECT objectid, segment_id
2 FROM sewers_h
3 WHERE sde.st_envintersects (shape,10,20,20,40) = 1
4 AND gdb_from_date <= TO_DATE('FEB-25-2008','MON-DD-YYYY')
5 AND gdb_to_date > TO_DATE('FEB-25-2008','MON-DD-YYYY');

The result set from the query contains only those rows that were current as of February 25, 2008.

If the objective is to obtain a result set that is equivalent to the DEFAULT version, first obtain the time stamp for the DEFAULT historical marker from the SDE schema's gdb_historicalmarkers table.

SQL> SELECT hm_timestamp
2 FROM sde.gdb_historicalmarkers
3 WHERE hm_name = ‘DEFAULT’;