Why sys_updated_on time returned in GET call is different than time that needs to be passed in delta query as paramter?

[Pratiksha7]

To fetch deltas, we are using sys_updated_on sysparm_query parameter in our API calls.

The problem is sys_updated_on returned by table API (eg. GET /table/cmdb_ci/sysID) and sys_updated_on required to pass as query param in delta query differs by 7 hours.

eg.
QUERY1. GET query:
curl -H "Accept:application/json" --user "user:password" "https://devXXXX.service-now.com/api/now/table/cmdb_ci_service?sysparm_fields=sys_updated_on" --request GET --header "Accept:appl;2D;2Dication/json"
=> {"result":[{"sys_updated_on":"2018-10-21 18:00:12"}]}

QUERY2. Delta Query (which should return expected result but does not):
curl -H "Accept:application/json" --user "user:password" "https://devXXXXX.service-now.com/api/now/table/cmdb_ci_service?sysparm_fields=sys_updated_on&sysparm_query=sys_updated_on%3Ejavascript:gs.dateGenerate('2018-10-21','18:00:00')" --request GET --header "Accept:appl;2D;2Dication/json"
=> {"result":[]}

QUERY3. Delta Query (which returns expected result):
curl -H "Accept:application/json" --user "userName:Password" "https://devXXXXX.service-now.com/api/now/table/cmdb_ci_service?sysparm_fields=sys_updated_on&sysparm_query=sys_updated_on%3Ejavascript:gs.dateGenerate('2018-10-25','11:00:00')" --request GET --header "Accept:appl;2D;2Dication/json"
=> {"result":[{"sys_updated_on":"2018-10-21 18:00:12"}]}

This looks like a timezone issue.
Why sys_updated_on value needs to be 7 hours less when passed in delta query(QUERY3) parameter than the one returned by GET query(QUERY1)?
Does the time zone in which sys_updated_on is stored is different than the one in which it's retruned in JSON response?
How do we know in which timezone sys_updated_on time should be  time passed in delta query?

To form delta query, I followed https://stackoverflow.com/questions/41011623/how-to-filter-servicenow-incident-records-based-on-sys-updated-on

[Ankur Bawiskar]

Hi Pratiksha,

When value is fetched using API from ServiceNow I think it returns system time zone i.e. GMT

Mark Correct if this solves your issue and also mark Helpful if you find my response worthy based on the impact.
Thanks
Ankur

[Pratiksha7]

Thanks for reply. I will check that.

But how do we know in which timezone time should be passed in delta query?

[Ankur Bawiskar]

Hi Pratiksha,

I think if you pass in GMT that should be fine. you need to check that once.

Regards

Ankur

[Shubhrajyoti]

Hi Ankur,

Thanks a ton for your help! The timestamp mentioned in the query is derived from an actual CI sys_updated_on value. The value is timezone agnostic. When we pass this same timestamp we don't get the proper results. In order to convert that value to timezone we need to know in which timezone servicenow instance is operating.