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

[ARG645 · Accepted solution]

Two things here,

1. When you fetch the value for sys_updated_on from ServiceNow, you will get the value in GMT Format 

2. So i believe the problem you are trying to solve is, if you use the same timeStamp for another query, you are not getting appropriate results. This may be because you have to pass in a Local Time. For which you may need the Local TimeZone information. 

To find out the Local timezone : 

The Time Zone of the user account which is being used for Authentication  IS THE TIMEZONE YOU NEED. 

Go to the sys_user record of the User and check the field Time Zone. 

Pass in the date/time respect to that TimeZone in your Delta Query etc etc......

[Shubhrajyoti]

Thanks Aman for the explanation. Just a follow up question: Some users have timezone field set as empty/null that means the Service Now instance timezone is used in these cases. Do you know any API which gives details of the Service Now instance including timezone information? 

[ARG645]

Good question. As you said, if no timezone is specified for a user, the system timezone is used. To know the system timezone, take a look at the system property glide.sys.default.tz

The system property may have a blank value, in that case America/Los Angeles is used as the default

So , you can use something like below in the script 

gs.getProperty("glide.sys.default.tz","US/Pacific");//The second paramter is the default return value if the system property is empty

Reference: 

system time zone

[Shubhrajyoti]

Thanks for the reply! However, what we prefer is an external REST API which can give instance timezone information. We are trying to avoid creating any scripts inside service now. I was exploring the REST api explorer but could not get any API for this purpose.

[ARG645]

//Endpoint 

<instance>/api/now/table/sys_properties?sysparm_fields=value,name&name=glide.sys.default.tz

If the above endpoint return the below output, then override the value with US/Pacific in your external application. 

{
    "result": [
        {
            "name": "glide.sys.default.tz",
            "value": ""
        }
    ]
}

Please mark the answer as Helpful/Correct if applicable. So that it might help others in future.