Better support for Date Module / Date Format

I found a similar problem as I wanted to have an x-axis with dates but they are shown in YYYY-MM-DDT00:00:00 format - can I change the format somewhere?

Greez,
Tobias

Tobias,

not yet, but this seems to be a high-demand request, so we converted it to a feature request and will try to implement for the next release.

Thank you.

Ah ha....This was exactly the issue I was having...It is one of the biggest points of interest for business....sales by date, visitors by date, issues by date, issue resolution by date, # of issues per month comparison, etc, etc...I'm a bit amazed that there are graphs that don't use dates as a criteria....possibly I have been working on business reporting for to long :)

I would also love to see this feature. Amcharts and this module are great, but that date is too long.

Jumping on the date support band wagon too!

+1 on date formatting options... in the meantime though, is there a workaround by going into the code and changing a function or variable or something?

Subscribing.

I'm surprised that there hasn't been more interest in this... Not being able to format dates on a chart seems like a pretty big deal to me. Any progress here?

Subscribing.

Changing it to Views GroupBy as there is a aggregation issue that must be dealt by Views GroupBy: in Views you only format the output, not the data as it's seen by the SQL query. This affects dates and all other data types.

So even if you set views to show date as Y-m-d for example, you still don't get aggregated data as the data as seen by the SQL has hour, minute and second which differ so don't end aggregated.

Formatting the date in the output works and Views Charts respects it so there is no issue in Views Charts.

I've patched views_groupby to make some of this possible. The problem: aggregation needs to occur at query level and the views date handler keeps the values in 2010-09-17 00:00:00 format. If you want to group by 2010-09-17 you're screwed.

The patch adds a new handler that gets used instead views_handler_field_date for all date fields. It's ok because it's backwards compatible. :)

The handler adds a new setting to the field: Aggregate format. This setting allows you to set the format used in the database query rather than in the rendering. You can then properly use the value as a grouping field. You can also still use the rendering format settings as you could previously.

NOTE: You can't get something from nothing. If your aggregate format removes hours you can't use hours in the rendered format.

Hope this saves someone's day.

Updated to be better. You need a patch for views_charts to bring the render format into play. Find that patch at:
#914654: Support field rewriting and special aliasing for labels

sammys: I added your patch, but can't seem to find the "Aggregate format" setting in any of my date fields. Any advice?

Subscribe

In my case, I needed to count records grouped by month of node created date. Here's a quick workaround solution that I've found on some forum using hook_views_pre_execute in a custom module:

<?php

function mymodule_views_pre_execute(&$view) {
  if ($view->name == 'MyViewName' && $view->current_display == 'myCurrentDisplay') {
    $search = array('GROUP BY node_created, term_data_name');
    $replace = array('GROUP BY YEAR(FROM_UNIXTIME(node_created)) + MONTH(FROM_UNIXTIME(node_created))');
    $view->build_info['query'] = str_replace($search, $replace, $view->build_info['query']);
    $view->build_info['count_query'] = str_replace($search, $replace, $view->build_info['count_query']);
  }
}

?>

This basically replaces the query's GROUP BY clause with the one that worked for me. This may help someone get things working until a better solution is implemented for date formats.

obrienmd: The Aggregate format setting is at the bottom of any date field in Views. See attached screenshot.

My problem is that this setting is not persisting. I enter m Y in the Aggregate format box, hit update, but if I try to edit the field again, the Aggregate format box is empty. The charts still use the standard Drupal date format (identified in the setting directly above the Aggregate format box, in my case D, d/m/Y - H:i)

I'm using Views 3, Views Group By 6.x-1.x-dev and used the patch in #12. The patch needed to Views Charts has already been committed in the latest version.

podox: Doesn't views 3 have built-in groupby?

Gah - yes, it does. I hadn't explored that. I guess Views GroupBy isn't designed (or necessary) for Views 3 and that may well be the source of my non-persisting field error.

FWIW, the built-in groupby appears to have the same limitation on Date field formats - it also uses the full timestamp.

Changing status...

Great patch, thanks Sammys.

The only thing I would say is the description for the Aggregation format textfield says strings for the PHP date function are accepted. When actually the views_date_sql_format() function that it passes through only accepts Y-m-d-H-i-s.

I need to group by 1st of every month like group by March 1, 2011; April 1, 2011; May 1, 2011 etc...Can that be achieved?

The patch is great. How could it be modified to work with CCK Datetime fields?

Best regards.
Alex

I need this patch to work with Views 2. Can anybody help me?

The patch #12 works great in D6, Views 2.
Don't forget to save the page module to register the new handler.
Nothing appears on the aggregation field, the setting is in the date field itself.

It seems that this worked for everyone who tested it.

@podox:
Hi you must change function in the patch 573264_12.patch:

  function option_definition() {
    $options = parent::option_definition();

    $options['format_query'] = array('default' => '');
    
    return $options;
  } 

to

  function option_definition() {
    $options = parent::option_definition();

    $options['query_format'] = array('default' => '');
    
    return $options;
  }

because there is typo error: from format_query to query_format
I am using this module with views 3.x and it working good.