In Drupal 7, as it supports different databases, modules which run queries containing date conversions and comparisons (Views, Date and others) face with a number of bugs connected to different SQL syntax. At the same time these modules have a lot of almost identical code related to DB date handling. Date functions are a part of SQL and I think they should not be handled by modules. We need a kind of date abstraction layer within database layer, which will contain date formatting, conversion, and extraction routines. I don't know if current DB layer can be changed too much, but the idea can become a roadmap for Drupal 8.
Suggested functions can include:
- date formatting function;
- date diff;
- date addition and subtraction;
- date parts extraction;
- date comparison (however I'm not sure if we really need this one);
- timezone handling.
Functions can operate on PHP DateTime, DateTimeZone, DateInterval objects.
Comments
Comment #1
lmou CreditAttribution: lmou commentedSo in a nutshell: the use of my own tables and data in Drupal , will be made easier in Drupal 8.x?
Now, in Drupal 7.x, I can launch a query in a window, but if something goes wrong in this routine, I can forget my drupal-website, nothing works anymore. I had it ones, and had to remove the newliy created node directly in the database to bring my Site to live again.
Comment #2
peterx CreditAttribution: peterx commentedOne reason for using a different date time format is to have a date work past the Unix Y2038 bug. Here are the options in PostgreSQL: http://www.postgresql.org/docs/8.2/static/datatype-datetime.html
MySQL: http://dev.mysql.com/doc/refman/5.6/en/datetime.html
There is some overlap of function but not an exact match. It would be nice to tell D8 I want a 4 digit year then have D8 select, at the schema level, the smallest format from each database to fit 4 digit years. D8 could format all the input fields for 4 digit years and format all output displays for 4 digits.
Comment #3
d.novikov CreditAttribution: d.novikov commentedLet me show an example of what I'm speaking about. Compare these two pieces of code from Date and Views modules, respectively:
As you see, the code is almost identical. There are also similar pieces for pgsql, sqlite and mssql databases. Will it be better if we move such repeating functionality to database layer?
Comment #4
peterx CreditAttribution: peterx commentedYes, sharing the date formatting simplifies things and improves interoperability. What I am thinking about is having the format independent of the underlying date field. The code you quote appears to be for the standard Unix timestamp. The Views code specifies int. If I have code for a "long date", could I access the same replacement array?
The sql_format function could work for different types of date fields. My long date requirement would need only the specification of Y instead of y.
The views_date_sql_format is built for int fields. If views_date_sql_format could be rewritten to use sql_format for the replacement part, sql_format could be kept generic and applied to other types of date fields. If the change went the other way, toward the Views approach, the code could not be reused for other date formats.
When you simplify the existing code, it is easier to add extra features. If the date handling was in a class, the class could be extended for long dates and other requirements.
Whatever is decided on dates, I would like to be able to add a module and make use of the replace table without having to include it in my code or to know what database is used.
Comment #5
d.novikov CreditAttribution: d.novikov commentedSpeaking about the code samples, DATE_FORMAT() accepts date in different formats and returns a string. But it is just for demonstration, that we have very similar pieces of code in different modules, and these pieces can be moved to database abstraction layer, as they contain elements of SQL.
Comment #6
d.novikov CreditAttribution: d.novikov commentedI created a sandbox project for this: http://drupal.org/sandbox/d.novikov/1762750 (no more avail)
Comment #7
Cameron Tod CreditAttribution: Cameron Tod commentedComment #8
mgiffordWhy was this postponed?
Comment #9
d.novikov CreditAttribution: d.novikov commentedHey @mgifford,
If core contributors confirm this is still actual and can be included to the core, I'm ready to work on this and provide a patch or help other way.
Comment #10
mgiffordThanks @d.novikov - really would be good to know this is still relevant before working on it. I'm not familiar enough with this part of Core to say.
Comment #11
peterx CreditAttribution: peterx commentedThe next step is probably to backport any improvements made in D8, if any. D8 switched from alpha to beta which means the API should be stable. Did they include expanded date processing?
A quick test of D8 shows two date formats, date+time and date without time. Both are stored as varchar 20. Hmmm? If we did something smart for D7, it would have to be converted back to a string in D8. Perhaps this issue could lead to a backport of the varchar 20 date from D8.
Comment #12
mgiffordI don't think it's been fixed in D8 yet. It may not still actively be a problem in D8. I don't know.