ESRIJSON services and datetime fields

[jamesstott]

Would just like to start by saying ogr_fdw is a fantastic extension. Thanks for all the hard work.

I have a question about esriFieldTypeDate fields when I connect to ESRIJSON services.

When using IMPORT FOREIGN SCHEMA ogr_all, the foreign table defines the date time fields as character varying (8). This means opening the foreign table often fails with the error:

"ERROR: value too long for type character varying(8)".

I can get it to open if I change the field length to minimum 14. The dates are then displayed as numbers.

After some digging online, I found I could run the following to get the dates in a datetime format if I create a view.

to_timestamp((table.field::bigint / 1000)::double precision

I just wanted to check if this is the expected behaviour? Is there a way to get these fields recognised as timestamps without having to create a view?

If I use ogrinfo, it says it is a string attribute. If I open the service in QGIS, it recognises that it is a date time field.

An example service is this one, where the fields DatoTil and DatoFra are esriFieldTypeDate fields.

https://kart.miljodirektoratet.no/arcgis/rest/services/artnasjonal2/FeatureServer/1/query?where=objectid+%3D+objectid&outfields=*&orderByFields=OBJECTID+ASC&f=json

[pramsey]

So on the core question, of recognizing the esriFieldTypeDate, we are at the mercy of the OGR driver, which from your experimentation seems not to recognize it. So fixing this would "just" involve fixing the OGR driver.

A second-order issue is that, in respecting the field length information from OGR, we are creating a situation where we get a broken set-up, whereas if we'd ignored the field length, you could have gotten a working, if untyped, field right from the import. @robe2 perhaps we have had this argument in the past, but I feel like preserving the field length metadata causes more problems than it solves, if it solves any. I'd kind of like to remove it.