Proper REST query for epoch

[JamesCrandall]

Trying to query a hosted feature service on its timestamp field (epoch format, long integer) by passing in a TIMESTAMP value.  Using this Where clause on the service query interface,

timestampField BETWEEN TIMESTAMP '2020-01-31 00:00:00' AND TIMESTAMP '2020-01-31 23:59:59'

However, it is returning features with a timestampField = 1580435690994 (epoch) which is:

GMT: Friday, January 31, 2020 1:54:50.994
Your time zone: Thursday, January 30, 2020 20:54:50.994 GMT-05:00

So, I'm unsure to properly query this service using the input datetime values.

[JamesCrandall]

So it appears that I should not include a timestamp on the input dates and simply format as yyyy-mm-dd then just allow the service to pick between midnight.  Therefore in order to return all features on 2020-01-31 the correct query should be:

timestampField BETWEEN '2020-01-31' AND '2020-02-01'

Please comment if you see differently.

[JamesCrandall · Accepted solution]

Update:

It looks like any datetime queries against a hosted feature service on AGOL should be based on GMT time.  There are some little things that can throw you off if not careful, for example it appears that adding the hosted feature service to a webmap viewer and applying queries with the built-in query builder will automatically adjust to your local time.

I think.

However, because I'm applying queries directly against the REST service query task, I need to make sure my input datetime values have been converted to GMT first.  I can't just assume that my desired query will work to return a result of all features on January 31, 2020:

timestampField BETWEEN TIMESTAMP '2020-01-31 00:00:00' AND TIMESTAMP '2020-01-31 23:59:59'

this is because GMT vs. my local time (Eastern) must account for a 05:00:00 difference and would need to be applied as this to return the desired result:

location_timestamp BETWEEN TIMESTAMP '2020-01-31 05:00:00' AND TIMESTAMP '2020-02-01 04:59:59'

This was validated against a feature service with known edit datetimes and a query based upon a very short time range, but please jump in and disagree or show me where I'm wrong!  Please!

Here's what's in the webmap viewer.  The OID's labelled are reflecting what I want to query, where the middle point of the 3 point features is the desired result so my query should return feature oid 91823:

Here's the REST query task.  The full query is converted to GMT from my localtime (look at the popup of the middle point is 2020-02-07 13:23:56 so I should return 1 feature with this query "location_timestamp BETWEEN TIMESTAMP '2020-02-07 18:23:55' AND TIMESTAMP '2020-02-07 18:23:58'"):

Here's the REST query result: 1 feature and the OID matches!