Date return issue with ArcSDESQLExecute

971
4
Jump to solution
01-07-2021 07:35 AM
CassKalinski
Occasional Contributor

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

1 Solution

Accepted Solutions
BlakeTerhune
MVP Regular Contributor

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.

View solution in original post

4 Replies
DanPatterson
MVP Esteemed Contributor

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


... sort of retired...
BlakeTerhune
MVP Regular Contributor

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.

CassKalinski
Occasional Contributor

Excellent! Worked as advertised.
Thank you Blake!

 

CassKalinski
Occasional Contributor

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