Store datetime fields with a Datetime type instead of a string

[ricardogsilva]

Currently pycsw sets up a database using the sqlalchemy.Text type for most columns.

In my opinion it would be nicer if we could use other sqlalchemy types, specially the sqlalchemy.DateTime type when creating the database. This would make the transition from python datetimes to the database smoother. The current situation gets around this by trying not to use python datetime and using strings instead.

For an example, the pycsw.core.metadata._parse_sos method actually converts a python datetime object (as returned by owslib.sos.SensorObservationService.contents[...].begin_position) into a string (lines number 718 and 719), so that it can be manipulated further down the road.

There is also a safeguard in pycsw.core.util.getqattr to always convert a datetime.date or datetime.datetime to a string. This function is used whenever something is to be written to the database.

I propose that we change this around:

• instead of converting datetimes to strings, we'd convert any (date based) string into a datetime as early as possible
• transform the sqlalchemy type of the fields in the core metadata model that represent dates and datetimes to a sqlalchemy.DateTime type.
• store datetimes in the database

Benefits:

• it is possible to use some other frameworks to directly query pycsw's database using datetime fields getting nice representations. In my concrete use case, I can use django's admin interface to visualize the temporal extent of a record using a datetime widget
• We can work with the native type of this type of data instead of using strings
• We can rely on the database backends to correctly interpret datetimes in queries instead of relying on a text based query for stuff like filtering for temporal extent begin and end datetimes.

I have an initial draft with the changes necessary for implementing this proposal in my local repository. It seems to work OK with sqlite and postgis, but I can only test with normal records. I will prepare a pull request so that it may be evaluated.

[kalxas]

This proposal breaks backward compatibility to our existing deployments, so it is not that easy to handle.
Similar discussion has happened in the early days of pycsw and we chose text representation as a simple/safe way to implement things.
It would require something more than a PR, it would need an RFC and a vote from the steering committee to do such a change.

[ricardogsilva]

OK, I will investigate a bit more on alternative solutions and look at how other projects are doing this. I'll prepare an RFC if it will seem to be a worthy change

[kalxas]

I would recommend to post the above to the pycsw-devel mailing list for further discussion.