Handle date/time fields intelligently

Is the SQL column of a date type, or text? If the database column is a date type, this should work (have to admit I haven't tested it lately). If it's text, then we would need to add a means for you to indicate that the contents of the field should be treated as a date.

For background on why datetime works but not date, see the Schema module issue #468644: no Schema type for mysql type timestamp, date, and enum error messages and the core schema issue #200953: Schema API lacks the 'time' and 'date' type.

I'm going to do some testing now to make sure TW is getting as much out of datetime fields as it can without depending on the Date module.

Well, datetime fields don't work correctly in filters - through the schema API, MySQL columns of type datetime map to the schema type date, for which Table Wizard sets the views_handler_filter_date, but that handler is built for UNIX timestamps stored in int fields (as core modules like node do). I don't think there's a simple answer to handling native date/time types, this will take some thought.

Subscribing. I'm seeing a similar issue when reading the contents of a node table from another drupal instalation: the 'created' and 'changed' columns are technically int(11), but I'd like to treat them as timestamps.

I'm attempting to do the same, but with PostgreSQL. My table has columns with DATA TYPE of 'date' and I'm getting Schema warning about 'no Schema type for pgsql type date. If I use the DATA TYPE 'timestamp with time zone', I get this error: no Schema type for pgsql type timestamp with time zone. I have tried adding a column to my table that converts the dates to a column with DATA TYPE integer and using a UNIX Timestamp like the 'node' table uses for the 'created' and 'changed' columns, but Views still does not recognize the column as a date.

I really want to use Views' date filter to filter my data by date range, but it seems Views need to know that this column is of type, date, before that filter will show up.

I added another column that used PostgreSQL's DATA TYPE of just 'timestamp', not 'timestamp with time zone' and converted my 'date' column to a timestamp and I can now filter by date ranges. However, I get this error when I save the filter:

user warning: ERROR: operator does not exist: timestamp without time zone >= integer LINE 3: WHERE (salesdata.invoice_timestamp >= 1230789600) AND (sale... ^ HINT: No operator matches the given name and argument type(s). You might need to add explicit type casts. query: SELECT COUNT(*) FROM (SELECT salesdata.salesdata_id AS salesdata_id FROM salesdata salesdata WHERE (salesdata.invoice_timestamp >= 1230789600) AND (salesdata.invoice_timestamp <= 1241067600) ) count_alias in C:\xampp\htdocs\d6\sites\all\modules\views\includes\view.inc on line 729.

I found that I need to change the WHERE clause to the following which will convert the UNIX Timestamp to a PostgreSQL Timestamp:

WHERE invoice_timestamp > (TIMESTAMP WITH TIME ZONE 'epoch' + 1230789600 * INTERVAL '1 second')
	AND invoice_timestamp < (TIMESTAMP WITH TIME ZONE 'epoch' + 1241067600 * INTERVAL '1 second')

See this page for more information:
http://www.postgresql.org/docs/8.3/static/functions-datetime.html#FUNCTIONS-DATETIME-EXTRACT

I still need to find where to edit that WHERE clause in Drupal.

Generalizing the title.

I consider this the top feature priority for version 1.2.

No progress on my end. I don't have a lot of time for Drupaling over at least the next couple of weeks, and my priority for that time is the Migrate module. If someone else wants to tackle this in the meantime (and I think some Schema module work will be a prerequisite), please be my guest...

Wow when I first ran into this problem I did not think it would cause me this much trouble, I've got a field that tw is recognizing as datetime, am I barking up the wrong tree to try and have the table declared in function date_api_date_api_fields($field). I've been out of the programming game for a while and it's taking more time than I thought to get my head wrapped around this.

Anyone interested in helping mikeryan move this forward. We could really take advantage of this functionality with the date api for our pending projects. We are willing to pay for development....

I'm going to attempt to translate what me and bfsworks have gotten so far on this issue and link what we used. Maybe someone can point us in the right direction and hopefully it will help someone. Not sure if this should all be posted here or the date module but I'm thinking it will help more here.

First we modified the code in tw_tablebuild.inc so when the tw module detects our calldate maketime field it properly sets either the date_api_filter_handler or date_api_argument_handler like so.

/**
 * Return a views handler based on the column type.
 */
function _tw_views_handler($sqltype, $type) {
  preg_match('/^[a-zA-Z]+/', $sqltype, $matches);
  $coltype = tw_column_type($matches[0]);
  
  if ($coltype=="numeric"){
	if ($type=="filter" OR $type=="argument"){
	$filter = "views_handler_{$type}_numeric";
	}
	if ($type=="field"){
	$filter = "views_handler_field";
	}
     }
  elseif ($coltype=="datetime"){
	if ($type=="filter" OR $type=="argument"){
	$filter = "date_api_{$type}_handler";
	}
	if ($type=="field"){
	$filter = "views_handler_field_date";
	}
     } 
  else
	{
	if ($type=="filter" OR $type=="argument"){
	$filter = "views_handler_{$type}_string";
	}
	if ($type=="field"){
	$filter = "views_handler_field";
	}
     } 

  return $filter;
}

we then used jonathan1055's patch from here http://drupal.org/node/476774 which is allowing our date to be displayed properly. The date module filter handler is now kicking in as we can see the date module filter options when we click on it in views. The problem is when the form prompts for the datetime field to filter, the option and check box for our table wizard generated field is not there. When taking a look at the code that generates the "list" of available "date" fields I found where the issues is. On lines 123-134 of yoursite/sites/all/modules/date/includes/date_api_filter_handler.inc you will see a call to date_api_fields() which can be found in yoursite/sites/all/modules/date/includes/date_api_fields.inc. Here you will see a function that according to the comments "Identifies all potential date/timestamp fields." Well it does this initially by using views_fetch_fields() function which is explained here: http://drupalapi.org/api/function/views_fetch_fields/6 . The function requires a base table such as "node", which contains the reference to all standard drupal tables. So what about custom table data. Well the inspiring part was there are other modules that have custom tables that use a datetime filed format that have made their date available to views. There is an extensive thread here: http://forum.civicrm.org/index.php/topic,6320.msg34263.html . The Civi CRM group was able to do by adding code to the date module that would include any filed that was assigned the default date filed handler of a derivative there of. That can be found around lines 51-62 of date_api_fields.inc. This addition is supposed to do the trick but cannot for whatever reason get it to recognize our filed. We have even tried to make it a local table with no luck.

Sorry if it's patched together, I'm doing this in my spare time at work (which isn't much).

Am also interested to see where this goes.. it's unfortunate and disappointing that SchemaAPI for D6 doesn't already support date types.

I don't know if this will help, but I reached the same point - my datetime fields would not be recognized as such when setting up a view. So I traced things through for a while and decided that I needed to tell date_api the name of my base table. I ended up subclassing and extending the date_api filter and argument handlers so I could explicitly set the base class, then used my handlers instead of the date_api handlers directly.

class views_handler_filter_datetime extends date_api_filter_handler {
  function construct() {
    parent::construct();
    $this->definition['base'] = 'my_table';
  }
}

jtkeith, that looks very promising. If I remember correctly I think you would have to do the same thing for the argument_handler, I'm going to spend some time on it tonight.

Yes, I also did the argument handler (see below). For anyone who's reading this, the problem I was having is that definition['base'] was not being set at all, so it was defaulting later to 'node', which meant I was only seeing default date fields.

class views_handler_argument_datetime extends date_api_argument_handler {

  function construct() {
    parent::construct();
    $this->definition['base'] = 'my_table';
  }
}

And I implemented hook_date_api_fields (seemed necessary to me). Perhaps not, but I haven't had time to play around with it much now that things are FINALLY working :)

/**
 * Implementation of hook_date_api_fields
 * See: modules/date/date_api.module
 */

function my_module_date_api_fields ($field) {
  $values = array(
    // The type of date: DATE_UNIX, DATE_ISO, DATE_DATETIME.
    'sql_type' => DATE_DATETIME,
    // Timezone handling options: 'none', 'site', 'date', 'utc'.
    'tz_handling' => 'none',
    // Needed only for dates that use 'date' tz_handling.
    'timezone_field' => '',
    // Needed only for dates that use 'date' tz_handling.
    'offset_field' => '',
    // Array of "table.field" values for related fields that should be
    // loaded automatically in the Views SQL.
    'related_fields' => array(),
    // Granularity of this date field's db data.
    'granularity' => array('year', 'month', 'day', 'hour', 'minute', 'second'),
  );

  switch ($field) {
    case 'my_table.start_date_time':
    case 'my_table.end_date_time':
      return $values;
  }
}

jtkeith,

Have you implemented this with data made available from table wizard or is this a custom module? I have been around the block with the base table issue before but wanted to give it another go given your recent posts. In date_api_fields.inc I have statically set $base to my table name "ucdr" and it does not pull any datetime fields from my tablewizard table. We have compensated for Schema and the handlers and have everything working except getting the date module to recognise a datetime field inside a table wizard table for the filter form during view configuration. Any help would be greatly appreciated.

Thanks.

bfsworks,

I know you're working for the table wizard solution, but yeah, my issues came up in a custom module. The reason I posted here was that I was in the exact same place - accounted for everything I thought needed to be done, but the fields wouldn't show up. And then tore my hair out for quite a long time. It sure feels like a similar issue.

I have views-enabled an external DB that has its own base table (described as such to views) and that has no relation to node whatsoever. This is why my fields wouldn't show, and why I needed to let date_api know to look at my base table for date fields. So establishing that worked for me. It may be that you're setting $base, but the table to which you're referring isn't a base table? Just a guess on my part.

- John

Thanks John,

You might be right so that brings up a good question: What is the base table name for an external table though table wizard?

Anyone. Also John I would be very interested in the code you used to make an external db available etc.. This would eliminate the need for tw and then I should be able to pull off the date issue as you did.

Thanks in advance.

I followed the instructions here:
http://views-help.doc.logrus.com/help/views/api-tables

After doing that, I came across table wizard and thought maybe I'd taken the wrong path, but it's working for me now. I'll see if I can pull some example info together and get it to you.

I have the same problem than bfsworks, I created the view of the external db (tw) but I can not filter by date field. How can I get the filter to work?

luthien, did you manage to solve it?

Below is how I solved my problem without creating custom modules or adding extra code to the site. There is probably an easy way of doing the same but I could not find it. I hope my solution will help somebody else.

Problem: I created the view of the external db (using tw) but I could not filter by date field ("date" filtering in Views is not working)

Solution:
1) Create a View (MySQL view, not Drupal view) using the date fields from the external DB table.

Example: Suppose my external mysql table's name is: "myexternaltablename"

- myexternaltablename has two "date" type fields called: start_date and end_date

- create a view in your MySQL external database called drupal_myexternaltablename with the following fields:

start_date: date
start_date_ts: datetime
start_date_ut: int(10)
end_date: date
end_date_ts: datetime
end_date_ut: int(10)
year: int(4)

an example of how the data looks like:
start_date: 2009-12-16
start_date_ts: 2009-12-16 08:00:00
start_date_ut: 1260979200

The idea is to convert the date fields to unixtime stamp so the view can handle the filter properly. I also used the year and the datetime format for other displays.

2) Go to table wizard (admin/content/tw) and add the drupal_myexternaltablename mysql view.
3) Go to Drupal views (admin/build/views/edit/your_view_name) and create your view.
4) Use the fields from your external table to create the View fields.
5) Use your drupal_myexternaltablename date fields (unix time stamp) to create the filters
example:
Configure filter mydb.drupal_myexternaltablename: start_date_ut
Operator: is less than or equal to
click - An offset from the current time such as "+1 day" or "-2 hours and 30 minutes"
Value: now

That fixed the problem of filtering the date from an external database. The rest is just to add as many date filters as needed. I used the quicktabs module (http://drupal.org/project/quicktabs) to display my pages and I was able to create tabs like:

current events | upcoming events | past events | etc...

that were filtered using the date field. So basically, you need the date field to be unixtime stamp type in order to filter it with the Drupal Views Filter.

luthien:

I am trying your solution from #28, but it appears that TW doesn't see a PRIMARY KEY my MYSQL View (see attached). Do you know what I could be doing wrong?

You might want to look at a patch I made to the Views Date field handler. You can see it here: Allow date handler to cope with 'datetime' fields

Hello dsasser,

my table wizard had the same problem but I ignored. The view worked well after using the procedure I specified above.

Give it a try to bendiy's patch, maybe that will solves the problem with the date filter.

subscribe

subscribing, it would be nice to have a possibility to treat int fields as a timestamp(date) in the view

+1

I have a Unix timestamp field - int(11) - that is already populated with epoch times similar to what the view-based solution (#28) would do. However, when I select this field I do not get the date-type filter selections (+1day etc) - I only get less than/greater than etc... How did you get the date filter options with an integer field?

Todd, please select the filter first, then you specify your "query" - An offset from the current time such as "+1 day" or "-2 hours -30 minutes". For example, I needed to filter all the future events, so I added a filter where start_date_ut was greater than today. Drupal takes care of the conversion, just add the time you need in the input box.

Yes, but when I connect to a table-wizard table with the unix timestamp in an int(11) field, I do not get the radio buttons for date & offset - I simply get the drop-down for "greater than/less than etc" and the "value" field. How are you telling Drupal that this table-wizard int(11) field is actually a date type? Or are you using "timestamp" in mysql, etc?

Many thanks...

Hi Todd, if you followed the process described in #28, you should see the offset field. The date_ut unix timestamp from the mysql view is an int(10), example: date_ut: 1260979200. Once you select that field in your drupal view, the date filters are visible.

I'm using Views 6.x-3.0-alpha3, what version are you using?

I am using 2.11 - I even changed the int(11) to an int(10) but still don't get the date options in the filter section. There must be something somewhere that has to be set distinctly in order for my view to know this integer field is intended to be used as a date - otherwise it would have date filtering options for just any old integer field, no?

I see to remember sticking with 2.11 because I didn't want alpha in production and/or some of my views add-ons were not yet compatible with 3.x?

Dang, I upgraded to 3.0 alpha3 and nothing happened - still no date-related filter options for my MySQL int(10) field being used as an epoch date.

Plus now all normal datetime fields that *were* working with 2.11 now all say Dec 31st, 1969 as reported in #852880: TW 6.x-1.2 formatting all datetime rows as 12/31/1969 - 8:33pm

Reverting title.

Table Wizard is being deprecated in favor of the Data module going forward. You'll find that, although it doesn't currently support native datetime fields, it does have a better framework for controlling how fields are handled in views (for example, with an integer field containing timestamps you can choose views_handler_field_date as the field handler).

Mike, thanks for the info, I wasn't aware of that fact.

I even went and literally created a field called date_ut just in case the extension "_ut" somehow made a difference, knowing it wouldn't...

I'm back on 2.11 but I'm still missing something really obvious that changes the filter fields for my int(10) field from "normal" to the date differential stuff.

I can't imagine this would matter, but I have a "real" underlying table, not a MySQL view?