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,
(
MAX(m.STATE_ID) STATE_ID,
m.REGISTRATION_ID
<OWNER>.MVTABLES_MODIFIED m
GROUP BY
) 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:
def updateMessages(self):
"""Modify the messages created by internal validation for each tool
parameter. This method is called after internal validation."""
STANDALONE PYTHON SCRIPT
# GetVersionedEditDates.py
# Created on: 2015-06-02
# 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.
# 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
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")
# Display the results only
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 fieldfc_path = os.path.join(sde_connection_file, fc_name)
# list of fields needed for tasksfields = [last_edited_date]
# sql_clause for SearchCursor argument to find only the one row with the most recent last_edited_datesql_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)
Angemeldete Mitglieder können Beiträge verfassen, Updates folgen und mehr. Neu hier? Registriere ein kostenloses Konto.
Find useful guides, FAQs, and documents to help you navigate and make the most of Esri Community.
