RE: Rest API date+time + sorting
- When querying the lastModifiedDate or createdDate from the Rest API, how do you get the time part – it just seems to return the date. And how do you get it to show up in ISO8601 format?
For example
/services/rest/query/v1/suiteql
{
“q”:”SELECT Id, lastModifiedDate from Item”
}
returns records like
{“links”:[],”id”:”26″,”lastmodifieddate”:”7/3/2019″}
why is the lastmodifieddate not in a date/time format?
2) When trying to sort by lastModifiedDate, an error comes (but without the order by clause it works…or using some other fields in order by it works).
Example
{
“q”:”SELECT Id, lastModifiedDate from Item order by LastModifiedDate asc”
}
returns
{“type”:”https://www.w3.org/Protocols/rfc2616/rfc2616-sec10.html#sec10.5.1″,”title”:”Internal Server Error”,”status”:500,”o:errorDetails”:[{“detail”:”An unexpected error occurred. Error ID: kg79hg1f15gm0xxrqjikt”,”o:errorCode”:”UNEXPECTED_ERROR”}]}
Use TO_CHAR to format your date as a string in the format you want.
So that gets the lastModfiiedDate in the result set in the right format, but I still can’t sort by it.
{
“q”:”SELECT Id, to_char(lastModifiedDate, ‘yyyymmddhh24miss’) as lastModifiedDate from customer order by lastModifiedDate”
}
returns
{“type”:”https://www.w3.org/Protocols/rfc2616/rfc2616-sec10.html#sec10.4.1″,”title”:”Bad Request”,”status”:400,”o:errorDetails”:[{“detail”:”Invalid search query. Detailed unprocessed description follows. Search error occurred: Invalid or unsupported search.”,”o:errorQueryParam”:”q”,”o:errorCode”:”INVALID_PARAMETER”}]}
but changing lastModifiedDate to something else in the order by doesn’t return an error. I even tried
{
“q”:”SELECT Id, to_char(lastModifiedDate, ‘yyyymmddhh24miss’) as lastModifiedDate from customer order by to_char(lastModifiedDate, ‘yyyymmddhh24miss’) desc”
}
You are on your own for the sorting, I haven’t gotten that to work.
Thanks – one final question. Are the table + field names as specified via SQL in REST the same as the ones that are returned via the metadata-catalog? For some reason I am able to query by certain tables in REST SQL (like the “item” table), but it doesn’t show up in the metadata-catalog list of objects.
For example
GET /services/rest/record/v1/item
returns
{
“type”: “https://www.w3.org/Protocols/rfc2616/rfc2616-sec10.html#sec10.4.5”,
“title”: “Not Found”,
“status”: 404,
“o:errorDetails”: [
{
“detail”: “Record type ‘item’ does not exist.”,
“o:errorUrl”: “/services/rest/record/v1/item”,
“o:errorCode”: “INVALID_VALUE”
}
]
}
BUT
POST /services/rest/query/v1/suiteql
{
“q”:”SELECT Id, to_char(lastModifiedDate, ‘yyyymmddhh24miss’) as lastModifiedDate from item”
}
returns valid records back
