I've been trying to discern the correct procedure to handle a MySQL datetime column for about 2 weeks now and although I take a step each day it is still slow and confusing.
Perhaps I should explain the bigger picture.
A third party application is storing its data in a MySQL database. The data is updated very frequently (say every 30 seconds). There are lots of tables involved and lots of data. I have no control whatsoever over the structure of the tables.
My ultimate goal is to write a module specific to the application but I first need to prove the concept.
My plan is to use existing modules to prove the concept (also demonstrate the power of drupal) and ease the development of the final module.
So here is what I'm doing so far.
I have arranged for the application to write its tables directly into the Drupal database to keep things simple.
I'm using the Data module to adopt those tables so I can create Views for them.
This should be simple enough but as is always the case with such things it isn't.
The first problem I've come across is caused by the fact that the third party application creates tables containing 'datetime' columns.
This broke the Data module when trying to create a View for the table. I've fixed that and posted a patch on the relevant issue. Its not 'datetime' specific, in fact its handling, what appear to be, modules that aren't obeying the rules properly.
The next problem is what are the correct views handlers to use?
All the ones I choose come back with an error when the table is viewed. I thought the Date module would help with this but it seems not.
So I have the following questions.
Are there existing Views handlers to deal with 'datetime' fields from a MySQL table?
If there are where are they and I'll find out why they don't show up in the Data module and post patches for that.
If there are no such handlers in existence I will create them. I'm going to need a little help getting started and doing it the "Drupal way". I'll even create handlers for any other un-supported MySQL types as I go.
I think this covers a number of use cases where data needs to be incorporated into Drupal.
In my case the ultimate goal is a module and the Data and Views modules will go a long way to creating the code for that module by creating the templates for the schema and the views thereby saving loads of work.