Validation Error on datastore_search when sorting timestamp fields

[wardi]

CKAN Version if known (or site URL)

2.7+

Please describe the expected behaviour

The datastore_search API should be able to perform a distinct or sort on fields with a timestamp data type. A data table or data explorer resource view should be able to reorder rows based on a timestamp field.

Please describe the actual behaviour

A validation error is returned from the datastore_search API when performing a distinct or sort on fields with a timestamp data type. The data table and data explorer view can't reorder rows based on a timestamp field.

The problem seems to be that timestamps are converted to char than to json before sorting.
https://github.com/ckan/ckan/blob/ckan-2.7.3/ckanext/datastore/backend/postgres.py#L1726-L1729

What steps can be taken to reproduce the issue?

Call the datastore_search endpoint with distinct=true and sorting on a timestamp field. /api/3/action/datastore_search?resource_id={resource_id}&fields="DateOpened"&sort="DateOpened"&distinct=true

Returned Statement

SELECT array_to_json(array_agg(j))::text FROM (
  SELECT DISTINCT to_json(to_char("DateOpened", 'YYYY-MM-DD"T"HH24:MI:SS')) as "DateOpened"
  FROM "resource_id" 
  ORDER BY "DateOpened" asc LIMIT 100 OFFSET 0
) AS j"

Returned Validation Error

Could not identify an ordering operator for type json
LINE 5:  ORDER BY "DateOpened" asc LIMIT 100 OFFSET...
HINT:  Use an explicit ordering operator or modify the query.

[wardi]

datastore_search needs some expanded tests to cover this case and a bunch of other combinations of types, sorting and records_format values to catch all the edge cases

[amercader]

@smotornyuk can you prioritize this review so we can get this on 2.8.1? cheers