I've recently upgraded my test site to Drupal 6.14 and Date 6.x-2.4 (and also XML Sitemap 6.x-1.1 from a rc version, and Phone CCK 6.x-2.9); had already been using Calendar 6.x-2.2 and Views 6.x-2.6. PHP 5.2.8
the test site drp6.rvuuf.org
should be displaying at least one current event in a few places:
on home page (near top): Next Service
on home page (near bottom: Upcoming Events
Event Calendar (link in nav menu) [1 event in Sep; 4 in Oct]
however, these views are coming up empty.
(for comparison, the production site rvuuf.org -- still on Drupal 6.13 and Date 6.x-2.3 -- works fine.
although drp6.rvuuf.org is an old db snapshot, I had updated ~5 events to occur after now.
I've gone thru the Troubleshooting steps 1-7
picking a fairly simple view (NextService), I went thru and re-examined&re-updated the filter & sort settings & saved the view. emptied caches under Performance & Views; also rebuilt permissions (I had seen that suggested in a related post). when I hit the Preview button, there's still no event displayed. however, if I copy/paste the SQL into phpAdmin, it returns a single item (node 2182) as it should. i.e., drp6.rvuuf.org/node/2182
so it seems to me that the query fields & date formats are ok.
(the same SQL is generated in my test (Drupal 6.14/Date 6.x-2.4) and production systems (Drupal 6.13/Date 6.x-2.3).
SELECT node.nid AS nid,
node_data_field_from.field_from_value AS node_data_field_from_field_from_value
FROM node node
INNER JOIN term_node term_node_value_0 ON node.vid = term_node_value_0.vid AND term_node_value_0.tid = 94
LEFT JOIN content_type_calevent node_data_field_from ON node.vid = node_data_field_from.vid
WHERE ((node.status <> 0) AND (node.type in ('calevent')) AND (term_node_value_0.tid = 94))
AND (DATE_FORMAT(ADDTIME(STR_TO_DATE(node_data_field_from.field_from_value, '%Y-%m-%dT%T'), SEC_TO_TIME(-25200)), '%Y-%m-%d') >= '2009-09-17' AND DATE_FORMAT(ADDTIME(STR_TO_DATE(node_data_field_from.field_from_value, '%Y-%m-%dT%T'), SEC_TO_TIME(-25200)), '%Y-%m-%d') <= '2009-09-23')
ORDER BY node_data_field_from_field_from_value ASC
similar results with my other views -- the SQL copied from the view's Preview then pasted into phpAdmin returns entries. but nothing is appearing in the Preview or the view itself. I think I had tried downgrading to Date 6.x-2.3 but still nothing. suggestions?
Steve
Comments
Comment #1
saweyer commentedI went ahead and cloned my 6.13 setup and production database & started to upgrade things in my dev environment. when I upgraded only to Drupal 6.14 (w/o updating to latest Date or other recommended security modules), i.e., still with Date 6.x-2.3, I'm seeing the problem of a valid SQL query (that works when pasted into phpAdmin) but no results actually appearing in the views.
what's interesting is that for the 2 multi-sites that this installation supports, one doesn't work (no results in views), while the other does works! One difference is that in my original site (Created 2-1/2+ yrs ago), I had created 2 separate From & To date fields in my CCK "CalEvent" type; this is the one that doesn't display results (though the SQL query works). for the 2nd site that I added much later, my CalEvent uses a single Date field (using both From & To), which works in 6.14.
Any idea about why such a difference in using dates like this would matter? It appears it's not a problem with Date 6.x-2.4 at all, and more with Drupal 6.14 & how it interacts w/ Date (any version) ?
(Eventually I may migrate my older events to new definition, but would rather not have to do that now, esp. w/o understanding what's going on, and whether this conversion is necessary)
Steve
Comment #2
saweyer commentedok. I've cloned the production database into my test environment.
then upgraded my test site Date 6.x-2.4 (along with latest Phone, Nodewords & XML Sitemap) but stayed with Drupal 6.13 -- views involving dates works fine (in both my sites).
I then upgraded test site to Drupal 6.14 -- Calendar view, and the two views on my home page now appear empty.
since both Date 6.x-2.3 & Date 6.x-2.4 worked with Drupal 6.13, this appears not to be a Date problem per se -- but likely something introduced in Drupal 6.14 affecting the view results. I'll shift my issue there.
Steve
Comment #3
szb100 commentedI think I have the same issue.
A long time ago, someone posted a 'fix' which basically changed date_api_sql.inc from
$field = "STR_TO_DATE($field, '%Y-%m-%%dT%T')";
to
$field = "STR_TO_DATE($field, '%Y-%m-%%d%T')";
On my site this works. You remove the 'T' you get the results, you put it back, you don't.
I can't for the life of me understand this though, and it can't be a common issue else no-one would get date based views to work on CCK date fields.
Can anyone help (or point me in the right direction?)
Updated the version as the behaviour is the same in the latest.
Comment #4
damienmckennaUnfortunately the Drupal 6 version of the Date module is no longer supported. That said, we appreciate that you took time to work on this issue. Should this request still be relevant for Drupal 7 please feel free to reopen it. Thank you.