Accessing information about ArcSDE historical markers via arcpy or SQL?

Question

I would like to be able to access the list of historical markers that is presented in the "Change Version" dialog in ArcMap - https://desktop.arcgis.com/en/arcmap/latest/manage-data/geodatabases/changing-versions-in-arcmap.htm

I have searched the ArcSDE system tables, but I am unable to find this information - http://desktop.arcgis.com/en/arcmap/10.3/manage-data/gdbs-in-oracle/system-tables-oracle.htm

Ideally, I would like to be able to access the name of the historical marker and other pertinent information like the creation date/time - much like the information that is stored in the GDB "VERSIONS" table for transactional versions.

Answer 1

You should be able to get everything you need from the gdb_items and gdb_itemtypes tables. The following query should get you started on SQL Server.

SELECT items.Name AS "Marker Name", 
    Definition.value('(/GPHistoricalMarker/TimeStamp/node())[1]','nvarchar(max)') AS "TimeStamp",
    CONVERT(datetime, Definition.value('(/GPHistoricalMarker/TimeStamp/node())[1]','nvarchar(max)')) AS "DateTime"
FROM dbo.GDB_ITEMS AS items INNER JOIN dbo.GDB_ITEMTYPES AS itemtypes
    ON items.Type = itemtypes.UUID
WHERE itemtypes.Name = 'Historical Marker'

More information: Query geodatabase system tables with SQL

If you are using Oracle, make sure to read the documentation. You'll need to access the GDB_ITEMS_VW view and set you the extproc.

Edit - This worked for me on ArcSDE 10.3 running on Oracle 11g:

SELECT items.name AS marker_name,
extractvalue(xmltype(items.definition), '*/TimeStamp') AS marker_timestamp_string,
to_timestamp(extractvalue(xmltype(items.definition), '*/TimeStamp'), 'YYYY-MM-DD"T"HH24:MI:SS.ff3') AS marker_timestamp_timestamp,
cast(to_timestamp(extractvalue(xmltype(items.definition), '*/TimeStamp'), 'YYYY-MM-DD"T"HH24:MI:SS.ff3') AS DATE) AS marker_timestamp_date
FROM sde.gdb_items_vw items
INNER JOIN sde.gdb_itemtypes itemtypes
ON items.type = itemtypes.uuid
WHERE itemtypes.name = 'Historical Marker'

Thanks!