CartoDB date fields interpreted as OFTDate instead of OFTDateTime

[molnar]

In OGRCARTODBTableLayer::GetLayerDefnInternal() (ogrcartodbtablelayer.cpp), the type of each non-geometry field is determined by OGRPGCommonLayerSetType() (ogrpgdumplayer.cpp).

This function maps the "date" type to OFTDate. However, "date" fields in CartoDB are actually datetimes, in the format yyyy-mm-ddTHH:MM:SSZ.

Note that a field of type OFTDateTime is expected in OGRCARTODBLayer::BuildFeature() (ogrcartodblayer.cpp) when translating the JSON object (a field of type OFTDate is not explicitly handled.

[Even Rouault]

Is there really an issue here ? I can't reproduce any problem with my tests. For example try "ogrinfo cartodb:gdalautotest2 multiline"

If OGRPGCommonLayerSetType() is called (in authenticated mode), it is with a postgresql type, so "date" is really a OFTDate and a "timestamp...." is a OFTDateTime. In unauthenticate mode, in ogrcartodblayer.cpp:376, the "date" json type returned by the CartoDB API is converted as a OFTDateTime.

[molnar]

I see, that makes sense. I had assumed it was a simple oversight due to the confusing "date" definition that CartoDB has.

Regardless, I'm certainly getting OFTDate fields where I was getting OFTDateTime fields in GDAL 1.11 (according to my test suite).

I'll take a good hard look at what is happening on my end, and I'll report back to you if there is something reproducible.

[Even Rouault]

If you can provide a link to a dataset that demonstrates the issue, that would be easier to investigate.

[molnar]

Replying to rouault:

If you can provide a link to a dataset that demonstrates the issue, that would be easier to investigate.

I looked into this a bit more. It appears that the dataset in question must have been created via a different method than how datasets are created now, since the underlying PG data type of the Cartodb date field is PG date.

Here is the dataset, created via GDAL 1.11 on October 31st, 2014: ​https://safedev.cartodb.com/tables/alltypesandattrs/public/map (PG type of date_attr is date)

Here is the same data, in a dataset created via GDAL 2.0 today: ​https://safedev.cartodb.com/tables/alltypesandattrs_v2/public/map (PG type of date_attr is timestamptz)

I imagine this has to do with how tables were created in GDAL 1.11, or perhaps how the SQL API used to work in CartoDB. Anyway, I suppose that the interesting point is that there may be tables in the wild where the underlying type of date fields is really date instead of timestamptz.

Another note: The data in question comes from an original source that supplied OFTDate fields, not OFTDateTime. It's possible this only used to happen when creating a table with OFTDate data.

[Even Rouault]

From ​http://www.postgresql.org/docs/9.1/static/datatype-datetime.html, "date" in postgresql is really a date without time (and this is the case of your date : time is at 0). In 1.11 and still in trunk, "date" was used for OFTDate and "timestamp with time zone" for OFTDateTime. So I still don't see any issue. It may be the cartodb UI that displays it as a datetime.

[molnar]

Replying to rouault:

From ​http://www.postgresql.org/docs/9.1/static/datatype-datetime.html, "date" in postgresql is really a date without time (and this is the case of your date : time is at 0). In 1.11 and still in trunk, "date" was used for OFTDate and "timestamp with time zone" for OFTDateTime. So I still don't see any issue. It may be the cartodb UI that displays it as a datetime.

I understand.

You are correct that the "date" fields always display in their UI as a timestamp with time zone. Therefore, it seemed natural that any CartoDB "date" data coming out of those tables should retain timestamp with timezone format.

Anyway, it's not a big deal on my end. All of the fields we create now have an underlying PG "timestamptz" type, so users of the CartoDB UI will get back data in the format they would expect. When I raised the issue, I mistakenly thought that CartoDB "date" types were being interpreted as OFTDate, rather than PG "date" types.

[Even Rouault]

ok, so closing the ticket