Question about REST API query using sys_created_on

Bajeet
Kilo Contributor

I have this record:

{ "u_implemented": "false", "number": "INNO003008", "sys_id": "36745829db0223004c53d426ca9619b6", "u_description": "Sune Boye Riis was on a bike ride with his youngest son, enjoying the sun slanting over the fields and woodlands near their home north of Copenhagen, when it suddenly occurred to him that something about the experience was amiss. Specifically, something was missing.\r\n\r\nIt was summer. He was out in the country, moving fast. But strangely, he wasn't eating any bugs.", "u_idea_type": "process improvement", "sys_updated_by": "admin", "sys_created_on": "2018-11-27 22:02:32", "sys_mod_count": "1", "sys_updated_on": "2018-11-27 22:03:05", "sys_tags": "", "u_string_2": "The Insect Apocalypse Is Here What does it mean for the rest of life on Earth?", "sys_created_by": "admin" }

When I send this as the sysparm_query, it doesn't return the above record:

`sys_updated_on>javascript:gs.dateGenerate('2018-11-27','22:02:00')^ORsys_created_on>javascript:gs.dateGenerate('2018-11-27','22:02:00')^ORDERBYDESCsys_created_on`

Is there a problem with my query? The record has a `sys_created_on` value of "2018-11-27 22:02:32" and a `sys_updated_on` value of "2018-11-27 22:03:05". Shouldn't that be returned by the above query?

1 ACCEPTED SOLUTION

Best option will be is to set UTC time zone on service account (user record) that you are going to use for REST calls.

View solution in original post

11 REPLIES 11

Mike Patel
Tera Sage

query looks good. Can you send me full endpoint you are using to get the results.

Bajeet
Kilo Contributor

Hi Mike. Thanks for taking a look. This is the endpoint.

GET https://dev59078.service-now.com/api/now/table/x_293365_test_1_innovate?sysparm_query=sys_updated_on%3Ejavascript%3Ags.dateGenerate('2018-11-27'%2C'21%3A59%3A59')%5EORsys_created_on%3Ejavascript%3Ags.dateGenerate('2018-11-27'%2C'21%3A59%3A59')%5EORDERBYDESCsys_created_on&sysparm_limit=1.

Can you make sure on your x_293365_test_1_innovate table, you have "allow access to this via web services" checked.

find_real_file.png

also you have &sysparm_limit=1 in code so that will give you only 1 record back in result so remove "&sysparm_limit=1".

Bajeet
Kilo Contributor

Looks like that is checked.

I should have added more context also. I can retrieve records using that endpoint. That specific one doesn't get returned with that query. I get these results back with that query, but not the one created on 2018-11-27 22:02:32:

{
    "result": [{
            "u_implemented": "false",
            "number": "INNO003011",
            "sys_id": "917d2ee2db1223004c53d426ca961983",
            "u_description": "Our customers rely on us to help them navigate the complexities of the increasingly competitive cloud wars. Should they use Amazon Web Services (AWS)? Google Cloud Platform (GCP)? Microsoft Azure? How should they tune their workload for different offerings? Which is more reliable?\r\n\r\nWe are committed to building a cloud neutral product, and we run test clusters on all three leading US cloud providers. As we were testing features for our 2.1 release, we noticed something interesting: AWS offered 40% greater throughput than GCP.",
            "u_idea_type": "process improvement",
            "sys_updated_by": "admin",
            "sys_created_on": "2018-12-13 22:00:17",
            "sys_mod_count": "0",
            "sys_updated_on": "2018-12-13 22:00:17",
            "sys_tags": "",
            "u_string_2": "AWS Outperforms GCP in the 2018 Cloud Report",
            "sys_created_by": "admin"
        },
        {
            "u_implemented": "false",
            "number": "INNO003010",
            "sys_id": "313d2a26db1223004c53d426ca961944",
            "u_description": "Two trains collided on the Williamsburg Bridge between Manhattan and Brooklyn in 1995, killing a train operator and injuring dozens of riders. The accident, the fourth rear-end crash in a two-year span, led to stricter rules that kept trains from going too fast.\r\n\r\nMore than two decades later, those rules have slowed down trains more than is necessary for safety, which contributes to a system plagued by delays.\r\n\r\nNow the subway's leader, Andy Byford, is changing the rules in some areas to speed up trains as part of a major effort to improve service for frustrated riders. Over the weekend, the speed limit was raised on parts of two lines in Brooklyn — the N and R trains — from 15 miles per hour to as much as 30 miles per hour. Other lines will be sped up in coming months.",
            "u_idea_type": "cost savings",
            "sys_updated_by": "admin",
            "sys_created_on": "2018-12-13 21:59:24",
            "sys_mod_count": "0",
            "sys_updated_on": "2018-12-13 21:59:24",
            "sys_tags": "",
            "u_string_2": "Why Your Subway Train Might Start Moving Faster",
            "sys_created_by": "admin"
        },
        {
            "u_implemented": "false",
            "number": "INNO003009",
            "sys_id": "375ca666db1223004c53d426ca961907",
            "u_description": "The latest test flight by Sir Richard Branson's Virgin Galactic successfully rocketed to the edge of space and back.\r\n\r\nThe firm's SpaceShipTwo passenger rocket ship reached a height of 82.7km, beyond the altitude at which US agencies have awarded astronaut wings.\r\n\r\nIt marked the plane's fourth test flight and followed earlier setbacks in the firm's space programme.\r\n\r\nSir Richard is in a race with Elon Musk and Jeff Bezos to send the first fee-paying passengers into space.\r\n\r\nHe founded the commercial spaceflight company in 2004, shortly after Mr Musk started SpaceX and Jeff Bezos established Blue Origin.",
            "u_idea_type": "team building",
            "sys_updated_by": "admin",
            "sys_created_on": "2018-12-13 21:58:28",
            "sys_mod_count": "0",
            "sys_updated_on": "2018-12-13 21:58:28",
            "sys_tags": "",
            "u_string_2": "Branson's Virgin Galactic reaches edge of space",
            "sys_created_by": "admin"
        },
        {
            "u_implemented": "false",
            "number": "INNO003005",
            "sys_id": "88c214e9db0223004c53d426ca961967",
            "u_description": "Friend, you can look up the answer to this question as well as I can. The American Academy of Pediatrics website spells it out for you: No screen time for kids under about two, no more than an hour a day for kids between two and five. Kids should have limits and \"high quality\" media, and you should make a habit of watching videos / playing games with them. Talk to your kids about porn. (Sorry.)",
            "u_idea_type": "process improvement",
            "sys_updated_by": "admin",
            "sys_created_on": "2018-11-27 21:58:22",
            "sys_mod_count": "1",
            "sys_updated_on": "2018-12-13 22:23:41",
            "sys_tags": "",
            "u_string_2": "Our in-house Know-It-Alls answer questions about your interactions with technology. This is an update on 12/13/2018.",
            "sys_created_by": "admin"
        }
    ]
}

Could it have to do with timezones? It doesn't seem like that should come into play because when I make these requests from my server, the server follows UTC time. However, I did notice in the REST API Explorer, I can set the date back enough to account for the timezone offset and get the record I've been mentioning.