In the service there is field name "sysDate" with Date type.
Is there any way to select the record with minimum sysDate?
Here's my python code to query the REST API. In my case, I was querying for a survey date, so substitute it for "sysDate". In the query dictionary, the where clause should insure that the date field is not null, but it could be adjusted to search for a date after/before a specific date. Limit the output to 1 row with "resultRecordCount".
For additional help, see: Query (Feature Service/Layer)
import arcpy, urllib, urllib2, json, sys, time, datetime, collections
from datetime import datetime, timedelta
# URL, referrer and tokenurl may vary based on ArcGIS Online or your server setup
# Credentials and feature service information
username = "" # user name here
password = "" # password here
# Adjust URL as required
URL = "https://services.arcgis.com/<xxxx>/arcgis/rest/services/<layername>/FeatureServer/0/query"
referer = "http://www.arcgis.com/"
tokenurl = "https://www.arcgis.com/sharing/rest/generateToken"
# obtain a token
query_dict = { 'username': username, 'password': password, 'referer': referer }
query_string = urllib.urlencode(query_dict)
token = json.loads(urllib.urlopen(tokenurl + "?f=json", query_string).read())
if "token" not in token:
print(token['error'])
sys.exit(1)
# build query dictionary
query_dict = {
"where" : "SurveyDate IS NOT NULL",
"outFields" : "OBJECTID, Business, SurveyDate",
"orderByFields" : "SurveyDate ASC",
"returnGeometry" : "true",
"resultRecordCount" : "1",
"f": "json", "token": token['token'] }
# to select all fields use: "outFields" : "*",
# to select individual fields use comma delimited list: "outFields" : "OBJECTID, SurveyDate",
# a date certain: "where" : "SurveyDate >= DATE '2016-05-29 18:30:00'",
# if you do not want geometry: "returnGeometry" : "false",
# resultRecordCount only applies if supportsPagination is true
# results in json format
jsonResponse = urllib.urlopen(URL, urllib.urlencode(query_dict))
features = json.loads(jsonResponse.read(),
object_pairs_hook=collections.OrderedDict)[u'features']
# print json.dumps(features, indent=4, sort_keys=False) # formatted json
print 'ObjectID\tBusiness\tSurvey Date\tX-lon\tY-lat'
for feature in features:
# AGO uses GMT/UTC, you may wish to convert to local time
surveyTime = time.strftime('%c', time.localtime(feature['attributes']['SurveyDate']/1000))
print str(feature['attributes']['OBJECTID']) + '\t' + feature['attributes']['Business'] + \
'\t' + surveyTime,
# if you want geometry; AGO returns web mercator, reproject if necessary
# print "x: " + str(feature['geometry']['x'])+ "\ty: " + str(feature['geometry']['y'])
# WGS 1984 : (4326) Lat/Lon
# WGS 1984 Web Mercator (auxiliary sphere) : (102100) or (3857)
ptGeometry = arcpy.PointGeometry(arcpy.Point(feature['geometry']['x'],feature['geometry']['y']),
arcpy.SpatialReference(3857)).projectAs(arcpy.SpatialReference(4326))
print "\t" + str(ptGeometry.firstPoint.X) +"\t" + str(ptGeometry.firstPoint.Y)
print "\nCompleted"
Hello Randy,
I check the query object in ArcGIS Js API and get the idea:
query.orderByFields=["SystemDate "];
query.num=1; //Number of features to retrieve.
Then the queryTask will return the feature with minimum SystemDate.
Thank you so much for your help.
Have a nice day.