Ken,
I am doing the same thing with versioning. Can you point me to the additional information I would need to find the last modfication date of a versioned feature class in SDE (on Oracle 11 g if that matters). I have used ARCSDESQLEXECUTE, I just can't get the correct date.
Ken,
I'm using the following query to get the last edit date for versioned feature classes:
SQL CODE
SELECT
r.OWNER,
r.TABLE_NAME,
TO_DATE ('19700101000000','YYYYMMDDHH24MISS') + NUMTODSINTERVAL(r.REGISTRATION_DATE,'SECOND') REGISTRATION_DATE,
m.STATE_ID,
s.CLOSING_TIME EDIT_DATE
FROM
<OWNER>.TABLE_REGISTRY r,
(
SELECT
MAX(m.STATE_ID) STATE_ID,
m.REGISTRATION_ID
FROM
<OWNER>.MVTABLES_MODIFIED m
GROUP BY
m.REGISTRATION_ID
) m,
<OWNER>.STATES s
WHERE
r.TABLE_NAME NOT LIKE '%DIRTYAREAS' AND
r.TABLE_NAME NOT LIKE '%LINEERRORS' AND
r.TABLE_NAME NOT LIKE '%POINTERRORS' AND
r.TABLE_NAME NOT LIKE '%POLYERRORS' AND
r.TABLE_NAME NOT LIKE 'GDB_%' AND
r.REGISTRATION_ID ( + ) = m.REGISTRATION_ID AND
s.STATE_ID = m.STATE_ID
ORDER BY
m.STATE_ID DESC
Substitute <OWNER> for the name of your geodatabase owner in the SQL above.
I wrote the following geoprocessing script tool In Python for the user to use with ArcToolbox:
GUI
GUI VALIDATOR CODE
# -*- coding: utf-8 -*-
# ---------------------------------------------------------------------------
# GetVersionedEditDates_Validator.py
# Created on: 2015-06-03
# John P. Lovato
# Description:
# Validator for GetVersionedEditDates script tool.
# Updated on:
# 2015-06-03 by John P. Lovato - Created.
#
# ---------------------------------------------------------------------------
import arcpy
class ToolValidator(object):
"""Class for validating a tool's parameter values and controlling
the behavior of the tool's dialog."""
def __init__(self):
"""Setup arcpy and the list of tool parameters."""
self.params = arcpy.GetParameterInfo()
def initializeParameters(self):
"""Refine the properties of a tool's parameters. This method is
called when the tool is opened."""
# disable the CSV File location parameter on start up
self.params[3].enabled = 0
return
def updateParameters(self):
"""Modify the values and properties of parameters before internal
validation is performed. This method is called whenever a parameter
has been changed."""
# toggle the state of the CSV file location parameter baed on
# checkbox.
if self.params[2].value == True:
self.params[3].enabled = 1
else:
self.params[3].enabled = 0
return
def updateMessages(self):
"""Modify the messages created by internal validation for each tool
parameter. This method is called after internal validation."""
return
STANDALONE PYTHON SCRIPT
# -*- coding: utf-8 -*-
# ---------------------------------------------------------------------------
# GetVersionedEditDates.py
# Created on: 2015-06-02
# John P. Lovato
# Description:
# Displays registration date and last edit date for versioned objects
# Arguments:
# <SDE_Workspace> - Workspace connection.
# <GDB_Owner> - Geodatabase Owner (User who owns GDB metadata tables)
# <Write_CSV> - Should the results go to a CSV file.
# Updated on:
# 2015-06-03 by John P. Lovato - Added parameters for writing csv file and code
# for creating CSV file.
# ---------------------------------------------------------------------------
import arcpy, sys, os, datetime, csv
from arcpy import env
from datetime import datetime
# SDE Workspace
sdeWorkspace = arcpy.GetParameter(0)
# GDB Owner
gdbOwner = arcpy.GetParameter(1)
# Write CSV
writeCSV = arcpy.GetParameter(2)
# CSV File
outputFolder = arcpy.GetParameterAsText(3)
# Establish the gp workspace
env.workspace = sys.path[0]
# Create the SDE Connection
sdeConn = arcpy.ArcSDESQLExecute(sdeWorkspace)
# Build the SQL Statement for last versioned edits (DML changes only)
SQLStatement= """SELECT
r.OWNER,
r.TABLE_NAME,
TO_DATE ('19700101000000','YYYYMMDDHH24MISS') + NUMTODSINTERVAL(r.REGISTRATION_DATE,'SECOND') REGISTRATION_DATE,
m.STATE_ID,
s.CLOSING_TIME EDIT_DATE
FROM
<OWNER>.TABLE_REGISTRY r,
(
SELECT
MAX(m.STATE_ID) STATE_ID,
m.REGISTRATION_ID
FROM
<OWNER>.MVTABLES_MODIFIED m
GROUP BY
m.REGISTRATION_ID
) m,
<OWNER>.STATES s
WHERE
r.TABLE_NAME NOT LIKE '%DIRTYAREAS' AND
r.TABLE_NAME NOT LIKE '%LINEERRORS' AND
r.TABLE_NAME NOT LIKE '%POINTERRORS' AND
r.TABLE_NAME NOT LIKE '%POLYERRORS' AND
r.TABLE_NAME NOT LIKE 'GDB_%' AND
r.REGISTRATION_ID ( + ) = m.REGISTRATION_ID AND
s.STATE_ID = m.STATE_ID
ORDER BY
m.STATE_ID DESC"""
# Customize the SQL Statement
SQLStatement = SQLStatement.replace('<OWNER>',gdbOwner)
# Display the SQL Statement
arcpy.AddMessage("\n" + SQLStatement + "\n")
# Pass the SQL statement to the database.
sdeReturn = sdeConn.execute(SQLStatement)
if writeCSV:
# Directory of current script
dirScript = os.path.dirname(__file__)
# Output Folder
if outputFolder:
dirScript = outputFolder
# Report Date
reportDate = str(datetime.now()).replace(' ','_').replace(':','-').replace('.','-')
# Output File
outputFile = os.path.join(dirScript,gdbOwner + '_DML_Modified_' + reportDate + '.csv')
arcpy.AddMessage('Report will be written to ' + outputFile + '\n')
# Display the header to output
arcpy.AddMessage('{:<30}'.format('OWNER')+' '+'{:<30}'.format('TABLE')+' '+'{:^21}'.format('REG_DATE')+' '+'{:^8}'.format('STATE_ID')+' '+'{:^21}'.format('EDIT_DATE'))
arcpy.AddMessage('{:<30}'.format('------------------------------')+' '+'{:<30}'.format('------------------------------')+' '+'{:^21}'.format('---------------------')+' '+'{:^8}'.format('--------')+' '+'{:^21}'.format('---------------------'))
# Display (and capture) the results
with open(outputFile, 'wb') as csvfile:
csvwriter = csv.writer(csvfile)
# write the header
csvwriter.writerow(['OWNER','TABLE','REG_DATE','STATE_ID','EDIT_DATE'])
for sde in sdeReturn:
arcpy.AddMessage('{:<30}'.format(sde[0])+' '+'{:<30}'.format(sde[1])+' '+'{:<21}'.format(sde[2])+' '+'{:^8}'.format(sde[3])+' '+'{:<21}'.format(sde[4]))
csvwriter.writerow([str(sde[0]),str(sde[1]),str(sde[2]),str(sde[3]),str(sde[4])])
arcpy.AddMessage("\n+++++++++\n")
else:
# Display the header to output
arcpy.AddMessage('{:<30}'.format('OWNER')+' '+'{:<30}'.format('TABLE')+' '+'{:^21}'.format('REG_DATE')+' '+'{:^8}'.format('STATE_ID')+' '+'{:^21}'.format('EDIT_DATE'))
arcpy.AddMessage('{:<30}'.format('------------------------------')+' '+'{:<30}'.format('------------------------------')+' '+'{:^21}'.format('---------------------')+' '+'{:^8}'.format('--------')+' '+'{:^21}'.format('---------------------'))
# Display the results only
for sde in sdeReturn:
arcpy.AddMessage('{:<30}'.format(sde[0])+' '+'{:<30}'.format(sde[1])+' '+'{:<21}'.format(sde[2])+' '+'{:^8}'.format(sde[3])+' '+'{:<21}'.format(sde[4]))
arcpy.AddMessage("\n+++++++++\n")
Hope this helps you.
Sincerely,
John P. Lovato
USDA Forest Service
Geospatial Database Engineer
In 2019 using SQL Server, this is how I do determine what the last edited date was for all rows in a feature class. In my script where I use this code, I also have fallback code to handle situations where there is no last_edited_date values in the table, or where editor tracking is not enabled (this happens for our organization sometimes, hence the need for extra code).
* Note: One reason for this approach is that ArcPy should be handling any versioning scenarios, but you may want to investigate the results of using versioning and ensure to run a compress and then make sure the last_edited_dates are what you expect before/after a compress.
Steps
# which feature class to find last_edited_date field
fc_path = os.path.join(sde_connection_file, fc_name)
# list of fields needed for tasks
fields = [last_edited_date]
# sql_clause for SearchCursor argument to find only the one row with the most recent last_edited_date
sql_clause = ('TOP 1', 'ORDER BY last_edited_date DESC')
try:
# execute cursor query
rows = arcpy.da.SearchCursor(in_table=fc_path, field_names=fields, sql_clause=sql_clause)
# list comprehension to create list with only the necessary field at row index position 0
# should only be one value in resulting list due to use of the 'TOP 1' sql clause
# then get the resulting value at position 0
last_edited = [row[0] for row in rows][0]
# localize the date to UTC for a common timezone spec and to make the datetime object timezone aware (aka not naive)
last_edited_utc_date = utc.localize(last_edited) if last_edited else None
except Exception as e:
print(e)