FROM_UNIXTIME called on datetime field when generating view

This might be the problem I am experiencing as well: http://drupal.org/node/260550 ; I filed it under views and it was moved back to date.

The FROM_UNIXTIME is found both date_api_sql.inc and line 1070 of views.module.

Move to the latest dev version and see if you still have problems. There have been a number of changes since beta 1.

I just released a new 6.2-beta2 version for Date, so update to the latest code for Date and also get the latest -dev version of the Views module, which has some date-related changes that are needed for the Date views handling. If you use the Calendar module, get the new 6.2-beta2 version of that as well.

If you still have problems after that, open a new issue marked with the beta2 version so I know it's still a problem there.

In beta3 this problem is still here.

In beta3 this problem is still here.

That's the problem -- date_views.inc is no longer needed and has been removed, but you have an older copy floating around and it is doing the wrong things. I just made a new commit that creates a blank date_views.inc file so this won't happen.

I have the same problem as onhover with FROM_UNIXTIME applied to MySQL datetime datatypes in beta3, and as far as I can tell, there is no date_views.inc in my installation (I started with a fresh install using beta3).

The relevant part of date_api_sql.inc reads:

    146   function sql_field($field, $offset = NULL) {
    147     if (strtoupper($field) == 'NOW') {
    148       // NOW() will be in UTC since that is what we set the db timezone to.
    149       $this->local_timezone = 'UTC';
    150       return 'NOW()';
    151     }
    152     switch ($this->db_type) {
    153       case 'mysql':
    154       case 'mysqli':
    155         switch ($this->date_type) {
    156           case DATE_UNIX:
    157             $field = "FROM_UNIXTIME($field)";
    158             break;
    159           case DATE_ISO:
    160             $field = "STR_TO_DATE($field, '%Y-%m-%%dT%T')";
    161             break;
    162           case DATE_DATETIME:
    163             break;
    164         }
    165         break;

However, it seems as though the type of the date field (line 155) is not recognized as DATE_DATETIME even though it is stored as a MySQL datetime type. I tried to find out where/how $this->date_type was assigned its value, but due to unfamiliarity with both Drupal and PHP I didn't get very far along that track.

For reference, the generated SQL in my case is:

SELECT node.nid AS nid,
   node_data_field_start_time.field_start_time_value AS node_data_field_start_time_field_start_time_value,
   node.type AS node_type,
   node_data_field_start_time.field_start_time_value2 AS node_data_field_start_time_field_start_time_value2,
   node_data_field_start_time.nid AS node_data_field_start_time_nid,
   node_revisions.teaser AS node_revisions_teaser,
   node_revisions.format AS node_revisions_format,
   node.title AS node_title
 FROM node node 
 LEFT JOIN content_type_program node_data_field_start_time ON node.vid = node_data_field_start_time.vid
 LEFT JOIN node_revisions node_revisions ON node.vid = node_revisions.vid
 WHERE (node.type in ('program')) AND (FROM_UNIXTIME(node_data_field_start_time.field_start_time_value) >= NOW())
 ORDER BY node_data_field_start_time_field_start_time_value ASC

where node_data_field_start_time.field_start_time_value is a MySQL datetime field defined through CCK.

Beta3 is out of date, you need to use the latest -dev code. And this is a closed issue.

If beta3 is outdated, the best thing to do is release beta4, don't you think?

I've just created a feature request about that.