Rewrite date field handler to allow apply date format in SQL (by default it is applied in PHP)

Hello,

I applied the patch and tested to see the new feature.
I was not clear on what/where to see for the new features.

I was hoping to be able to have for the exposed date filter control over how it is shown. Being able to specify [YYYY] [MM] [DD] or [MM] [DD] [YYYY] for the select list dropdown depending on the filter would be awesome.

Typically, date.module should do stuff like this by overriding the default handler.

subscribing

Could someone re-roll the patch so it applies on Date 6.x-2.x-dev?

Thank you!

Does the patch apply to the View module or Date module?

It would be great to allow custom date formats.

Thank you

Here is a patch for D7. I have found that in D7 version of Date API function date_get_format_types() does not exists, so I have deleted all calls and usage of it.

You already can use any custom format you want by creating a custom format at admin/config/regional/date-time. You can create any number of custom formats (Time, Date-only, whatever) using anything that php supports. I am not going to layer on top of that yet another way to configure formats.

I can't tell what the end goal is here. If this is just to be able to group dates, we don't do that by altering the query, the grouping is done using the php format in the pre-render phase. And that already works, we don't need to change anything.

I have ported only ability to use custom format in SQL. This is useful when you want to get distinct set of dates for the nodes. So it now allows views to generate query like this:

SELECT DISTINCT DATE_FORMAT((DATE_ADD('19700101', INTERVAL node.created SECOND) + INTERVAL 10800 SECOND), '%Y') AS node_created FROM {node} node

This is very nice feature for the date module. However there could be different approaches of making that. Need your advice.

P.S. There is another pending patch for views that fixed some problems with DISTINCT: http://drupal.org/node/1245010

And here is a patch in the proper format.

This is a patch to Views, there is nothing I can do with this patch. And I already noted in #10 that I can't see that you're doing anything here that isn't already possible.

I haven't find how we could make this using grouping for node post date.

Mytask is to show distinct set of node creation years.

I do following:
1) Create view
2) Enable grouping
3) Add Node Post date and apply custom format (Y) to it.
4) Add nid field and change groping settings to SUM.
5) Add some sorting, limit and filters

I get following query:

SELECT node.created AS node_created, SUM(node.nid) AS nid
FROM 
{node} node
WHERE (( (node.status = '1') AND (node.type IN  ('news_item')) ))
GROUP BY node_created
ORDER BY node_created DESC
LIMIT 5 OFFSET 0

I don't see any formatting in sql query. Is this a way how to do it you was talking about in comment #10?

P.S.

Agreed with you that it is more views issue.

In #12 I have uploaded wrong patch. Here is correct one.

I am going to rewrite it for views module soon.

Finally done!

I have rewritten patch for views 7.x-.x for date field handler. It adds an option to use format in query. This feature applicable for all format except of Time ago, Time hence and Time span formats.

Here is a patch!

+++ views_new//handlers/views_handler_field_date.inc	2011-09-16 01:28:51.919084753 +0300
@@ -45,9 +46,61 @@
   }
 
+  function query() {    ¶
+    $this->ensure_my_table();

+++ views_new//handlers/views_handler_field_date.inc	2011-09-16 01:28:51.919084753 +0300
@@ -45,9 +46,61 @@
+    }
+    ¶
+    if ($format === '') {

+++ views_new//handlers/views_handler_field_date.inc	2011-09-16 01:28:51.919084753 +0300
@@ -45,9 +46,61 @@
+    }
+    ¶
+    $this->add_additional_fields();

trailing whitespaces http://drupal.org/coding-standards#indenting

@podarok, thx for review.

Here is a new patch with fixed spaces in the end of lines.

root@pubuntu:/var/www/drupal-7.4/sites/all/modules# git clone --branch 7.x-3.x http://git.drupal.org/project/views.git
Cloning into views...
remote: Counting objects: 32992, done.
remote: Compressing objects: 100% (10979/10979), done.
remote: Total 32992 (delta 25435), reused 28802 (delta 21881)
Receiving objects: 100% (32992/32992), 12.29 MiB | 1.91 MiB/s, done.
Resolving deltas: 100% (25435/25435), done.
root@pubuntu:/var/www/drupal-7.4/sites/all/modules# cd views
root@pubuntu:/var/www/drupal-7.4/sites/all/modules/views# wget http://drupal.org/files/views-date-format-in-sql-844202-20.patch
--2011-10-28 14:55:12--  http://drupal.org/files/views-date-format-in-sql-844202-20.patch
Визначення імені drupal.org… 140.211.166.6, 140.211.166.21
З’єднуюсь з drupal.org|140.211.166.6|:80… під’єднано.
HTTP-запит надіслано, очікування відповіді… 200 OK
Довжина: 3823 (3,7K) [text/plain]
Збереження у: „views-date-format-in-sql-844202-20.patch“

100%[====================================================================================================================>] 3 823       13,5K/s   в 0,3s     

2011-10-28 14:55:13 (13,5 KB/s) - „views-date-format-in-sql-844202-20.patch“ збережено [3823/3823]

root@pubuntu:/var/www/drupal-7.4/sites/all/modules/views# patch < views-date-format-in-sql-844202-20.patch 
can't find file to patch at input line 5
Perhaps you should have used the -p or --strip option?
The text leading up to this was:
--------------------------
|diff --git a/handlers/views_handler_field_date.inc b/handlers/views_handler_field_date.inc
|index b122b66..ae31205 100644
|--- a/handlers/views_handler_field_date.inc
|+++ b/handlers/views_handler_field_date.inc
--------------------------
File to patch: handlers/views_handler_field_date.inc
patching file handlers/views_handler_field_date.inc
root@pubuntu:/var/www/drupal-7.4/sites/all/modules/views# 

view for testing
with patch

$view = new view;
$view->name = 'date_formatters_in_qsl';
$view->description = '';
$view->tag = 'default';
$view->base_table = 'node';
$view->human_name = 'Date Formatters in QSL';
$view->core = 7;
$view->api_version = '3.0';
$view->disabled = FALSE; /* Edit this to true to make a default view disabled initially */

/* Display: Master */
$handler = $view->new_display('default', 'Master', 'default');
$handler->display->display_options['access']['type'] = 'perm';
$handler->display->display_options['cache']['type'] = 'none';
$handler->display->display_options['query']['type'] = 'views_query';
$handler->display->display_options['query']['options']['query_comment'] = FALSE;
$handler->display->display_options['exposed_form']['type'] = 'basic';
$handler->display->display_options['pager']['type'] = 'full';
$handler->display->display_options['style_plugin'] = 'default';
$handler->display->display_options['row_plugin'] = 'fields';
/* Field: Content: Post date */
$handler->display->display_options['fields']['created']['id'] = 'created';
$handler->display->display_options['fields']['created']['table'] = 'node';
$handler->display->display_options['fields']['created']['field'] = 'created';
$handler->display->display_options['fields']['created']['alter']['alter_text'] = 0;
$handler->display->display_options['fields']['created']['alter']['make_link'] = 0;
$handler->display->display_options['fields']['created']['alter']['absolute'] = 0;
$handler->display->display_options['fields']['created']['alter']['external'] = 0;
$handler->display->display_options['fields']['created']['alter']['replace_spaces'] = 0;
$handler->display->display_options['fields']['created']['alter']['trim_whitespace'] = 0;
$handler->display->display_options['fields']['created']['alter']['nl2br'] = 0;
$handler->display->display_options['fields']['created']['alter']['word_boundary'] = 1;
$handler->display->display_options['fields']['created']['alter']['ellipsis'] = 1;
$handler->display->display_options['fields']['created']['alter']['strip_tags'] = 0;
$handler->display->display_options['fields']['created']['alter']['trim'] = 0;
$handler->display->display_options['fields']['created']['alter']['html'] = 0;
$handler->display->display_options['fields']['created']['element_label_colon'] = 1;
$handler->display->display_options['fields']['created']['element_default_classes'] = 1;
$handler->display->display_options['fields']['created']['hide_empty'] = 0;
$handler->display->display_options['fields']['created']['empty_zero'] = 0;
$handler->display->display_options['fields']['created']['hide_alter_empty'] = 0;
$handler->display->display_options['fields']['created']['date_format'] = 'custom';
$handler->display->display_options['fields']['created']['custom_date_format'] = 'Y';
$handler->display->display_options['fields']['created']['query_format'] = 1;
/* Filter criterion: Content: Published */
$handler->display->display_options['filters']['status']['id'] = 'status';
$handler->display->display_options['filters']['status']['table'] = 'node';
$handler->display->display_options['filters']['status']['field'] = 'status';
$handler->display->display_options['filters']['status']['value'] = 1;
$handler->display->display_options['filters']['status']['group'] = 0;
$handler->display->display_options['filters']['status']['expose']['operator'] = FALSE;

returned SQL with patch #20

SELECT DATE_FORMAT((DATE_ADD('19700101', INTERVAL node.created SECOND) + INTERVAL 10800 SECOND), '%Y') AS node_created
FROM 
{node} node
WHERE (( (node.status = '1') ))
LIMIT 10 OFFSET 0

and without patch

SELECT node.created AS node_created
FROM 
{node} node
WHERE (( (node.status = '1') ))
LIMIT 10 OFFSET 0

looks like working for me
so RTBC?

Some comments but in general it looks fine!

+++ b/handlers/views_handler_field_date.incundefined
@@ -45,9 +46,61 @@ class views_handler_field_date extends views_handler_field {
+      $date_format_options = $this->options;
+      $date_format = $date_format_options['date_format'];

Better directly access $this->options['date_format']. That's much easier to understand.

+++ b/handlers/views_handler_field_date.incundefined
@@ -45,9 +46,61 @@ class views_handler_field_date extends views_handler_field {
+      $default_views_formats = array('raw time ago', 'time ago', 'raw time hence', 'time hence', 'raw time span', 'inverse time span', 'time span');
+
+      if (!in_array($date_format, $default_views_formats)) {
+        switch ($date_format) {
+          case 'short':
+            $format = variable_get('date_format_short', 'm/d/Y - H:i');
+            break;
+
+          case 'medium':
+            $format = variable_get('date_format_medium', 'D, m/d/Y - H:i');
+            break;
+
+          case 'long':
+            $format = variable_get('date_format_long', 'l, F j, Y - H:i');
+            break;
+
+          case 'custom':
+            $format = $date_format_options['custom_date_format'];
+            break;
+
+          default:
+            $format = variable_get('date_format_' . $date_format, '');
+         }
+      }

Can't you do this whole check on validation and add this behaviour to the #depedency as well?

+++ b/handlers/views_handler_field_date.incundefined
@@ -45,9 +46,61 @@ class views_handler_field_date extends views_handler_field {
+    } else {

Please add a new line for the else

+++ b/handlers/views_handler_field_date.incundefined
@@ -74,11 +127,11 @@ class views_handler_field_date extends views_handler_field {
-            return format_date($value, $format, $custom_format, null, 'en');
+            return ($this->options['query_format']) ? $value : format_date($value, $format, $custom_format, null, 'en');
           }
-          return format_date($value, $format, $custom_format);
+          return ($this->options['query_format']) ? $value : format_date($value, $format, $custom_format);
         default:
-          return format_date($value, $format);
+          return ($this->options['query_format']) ? $value : format_date($value, $format);

It's sort of a problem that we loose the feature of translating the value, but i'm not sure whether it can be supported.

Together with nobody__ we have updated this patch. Following things have been done:

1) Added ability to process grouping and aggregation into query() method.
2) Fixed formatting errors.
3) Added validation and dependency to the options form.

Here is a patch.

Here is a previous patch with fixed spacing issues.

Looks like there are separate module for this: http://drupal.org/project/views_date_format_sql, which is used by 22 sites.

Any ideas to commit this functionality to the Views module?

Let ask module maintainer for #25 merging codebase from this patch and views_date_format_sql

@dealancer thanks for the heads up. @podarok did you attach this thread to my project "Views Date Format SQL" by accident?
I briefly read the patch. Looks like almost the same code. Anything particular that needs to be added/fixed in "Views Date Format SQL"?

(compare the patch to: http://drupalcode.org/project/views_date_format_sql.git/blob/HEAD:/inclu... )

I think so, so I am changing back the project.

Can anyone review my patch?

I have been trying to get this date formatting in the SQL query working on my site and have gotten nowhere with it.

I have tried the Views Date Format SQL module plus other patches around the site and found they only work for the core date fields like Post Date. They don't seem to work for fields from the Date module.

Can someone point me in the right direction to get this working. Is it a Views issue or a Date module issue?

The sorting in "Views Date Format SQL" and this patch is for date fields. The Date module provides different fields with custom handlers. Support for the Date module can be added easily. A feature patch or sponsoring is welcome.

Refiling. Patch looks reasonable, but I still don't understand why it wasn't left closed after KarenS pointed out it wasn't necessary.

Thanks for a question and updating category. I will shed light on this.

Issue and patch have been involved since issue was created. Now patch uses Drupal date formats as suggested by KarenS. So the initial patch is outdated.

The new patch allows date format (including custom) to be applied in SQL. By default it is applied in PHP layer. Benefit is a possibility to group by e.g. Year, but without this patch groupping works by second.

I tryed to write some tests for that though they aren't working yet as expected.

I somehow get the intentation that there are timezone issues on that patch.

Just look at the failures of the test, i think this is actually not what we would accept.

Here is a new one.

This patch is years old-- is it worth updating or is everyone just using views_raw_sql?

This patch needs reroll.

Patch rerolled.