topic Re: Date return issue with ArcSDESQLExecute in Python Questions

Date return issue with ArcSDESQLExecute

CassKalinski — Thu, 07 Jan 2021 15:35:15 GMT

I am converting a script that uses pyodbc for a couple of select statements to using ArcSDESQLExecute instead. The change works fine on most of the data retrieval but I am hitting an issue on a date field in one of the queries.

With pyodbc, the field is returned as a date object. With ArcSDESQLExecute, the date comes back as a string like 'mm/dd/yyyy'. No hours, minutes, etc. I need either a date object (preferred) or a string with the full %Y%m%d%H%M%S format.

Is there a flag I am missing? A change needed in the SQL statement?

* MS SQL data base
* Field is type datetime in the database
* Records are timestamps every ~15 minutes, so having the full date object is important
* Same SQL string used in both pyodbc and ArcSDESQLExecute and it worked fine before the update.

def _get_timeseries_data(start, end, senID):

# start = datetime object
# end = datetime object

DB_TIMESERIES = '[{}].[{}].[timeseries]'.format(DB,SCHEMA)
DB_CONN = r'C:\Users\blah\blah\blah\Connection to 10.20.2.5.sde'
cnx = arcpy.ArcSDESQLExecute(DB_CONN)

# [Timestamp] = table field of type datetime (don't like the naming, but that is from the app vendor)
# Example from db: 2014-11-17 14:49:01.000

sqlString = "SELECT [Timestamp], [RecordedValue], [GradeName], [ApprovalName] \
FROM {} WHERE (SensorID = '{}') AND (Timestamp BETWEEN CONVERT(datetime, '{}') AND CONVERT(datetime, '{}'))\
ORDER BY Timestamp"\
.format(DB_TIMESERIES, senID, start, end)

rows = cnx.execute(sqlString)
logging.debug('TS query row: %s', row)
dRows = []
for row in rows:
dRows.append([row[0], row[1], row[2], row[3]])

del cnx
return dRows

Re: Date return issue with ArcSDESQLExecute

DanPatterson — Thu, 07 Jan 2021 15:40:23 GMT

Code formatting ... the Community Version - GeoNet, The Esri Community would help readability

Re: Date return issue with ArcSDESQLExecute

BlakeTerhune — Thu, 07 Jan 2021 16:16:07 GMT

Try formatting the date in the SQL when you query it. Here's your query string but formatted with Python multiline string.

sqlString = """SELECT FORMAT([Timestamp], 'yyy-MM-dd HH:mm:ss.fff') as Timestamp, [RecordedValue], [GradeName], [ApprovalName] FROM {} WHERE (SensorID = '{}') AND ( Timestamp BETWEEN CONVERT(datetime, '{}') AND CONVERT(datetime, '{}') ) ORDER BY Timestamp """ .format(DB_TIMESERIES, senID, start, end)

Sorry, I work with Oracle databases so I apologize if something in that syntax is slightly off.

Re: Date return issue with ArcSDESQLExecute

CassKalinski — Thu, 07 Jan 2021 15:58:46 GMT

The actual code snippet then...

def _get_timeseries_data(start, end, senID): dRows = [] cnx = arcpy.ArcSDESQLExecute(DB_CONN) sqlString = "SELECT [Timestamp], [RecordedValue], [GradeName], [ApprovalName] \ FROM {} WHERE (SensorID = '{}') AND (Timestamp BETWEEN CONVERT(datetime, '{}') AND CONVERT(datetime, '{}'))\ ORDER BY Timestamp"\ .format(DB_TIMESERIES, senID, start, end) try: rows = cnx.execute(sqlString) for row in rows: logging.debug('TS query row: %s', row) # With pyodbc, used fTimestamp instead of row[0] in the append... # fTimestamp = row.Timestamp.strftime("%Y-%m-%d %H:%M") fValue = _format_readings(row[1]) dRows.append([row[0], fValue, row[2], row[3]]) except Exception as e: print('SQL call failed. SQL: %s. Error: %s', sqlString, e) del cnx return dRows

Re: Date return issue with ArcSDESQLExecute

CassKalinski — Thu, 07 Jan 2021 16:21:00 GMT

Excellent! Worked as advertised.
Thank you Blake!