I'm getting wrong results in my views for a list of events. Here is how to replicate it.

I created a content type called "Event" with a field named "Date" with the following settings:
Granularity: selected Year, Month and Day
Time zone handling: No time zone conversion

I created a views to display all my events for the month given. Export of the view attached.

My regional settings timezone is set to "America/Chicago". and here is the query result

SELECT DISTINCT node.nid AS nid, field_data_field_event_date.field_event_date_value AS field_data_field_event_date_field_event_date_value, 'node' AS field_data_field_event_date_node_entity_type, 'node' AS field_data_title_field_node_entity_type
FROM 
{node} node
LEFT JOIN {field_data_field_track} field_data_field_track ON node.nid = field_data_field_track.entity_id AND (field_data_field_track.entity_type = :views_join_condition_0 AND field_data_field_track.deleted = :views_join_condition_1)
LEFT JOIN {field_data_field_event_date} field_data_field_event_date ON node.nid = field_data_field_event_date.entity_id AND (field_data_field_event_date.entity_type = :views_join_condition_2 AND field_data_field_event_date.deleted = :views_join_condition_3)
WHERE (( (node.status = '1') AND (node.type IN  ('event')) AND (field_data_field_track.field_track_value = 'E') AND (DATE_FORMAT(ADDTIME(STR_TO_DATE(field_data_field_event_date.field_event_date_value, '%Y-%m-%dT%T'), SEC_TO_TIME(-18000)), '%Y-%m') = '2011-07') ))
ORDER BY field_data_field_event_date_field_event_date_value ASC
LIMIT 10 OFFSET 0

In my result for July, I'm getting August 1 events too. Then in August, I dont see my August 1 event.

If i set my regional timezone to UTC, here is the query

SELECT DISTINCT node.nid AS nid, field_data_field_event_date.field_event_date_value AS field_data_field_event_date_field_event_date_value, 'node' AS field_data_field_event_date_node_entity_type, 'node' AS field_data_title_field_node_entity_type
FROM 
{node} node
LEFT JOIN {field_data_field_track} field_data_field_track ON node.nid = field_data_field_track.entity_id AND (field_data_field_track.entity_type = :views_join_condition_0 AND field_data_field_track.deleted = :views_join_condition_1)
LEFT JOIN {field_data_field_event_date} field_data_field_event_date ON node.nid = field_data_field_event_date.entity_id AND (field_data_field_event_date.entity_type = :views_join_condition_2 AND field_data_field_event_date.deleted = :views_join_condition_3)
WHERE (( (node.status = '1') AND (node.type IN  ('event')) AND (field_data_field_track.field_track_value = 'E') AND (DATE_FORMAT(STR_TO_DATE(field_data_field_event_date.field_event_date_value, '%Y-%m-%dT%T'), '%Y-%m') = '2011-07') ))
ORDER BY field_data_field_event_date_field_event_date_value ASC
LIMIT 10 OFFSET 0

In my result for July, I'm getting the right events

Problem
If i don't select a timezone of the date field, then i think the filter should not rely on the site's timezone as well.

It maybe the same as http://drupal.org/node/1017866

CommentFileSizeAuthor
calendarlist.txt10.14 KBprimerg

Comments

jason89s’s picture

Version: 7.x-2.0-alpha3 » 7.x-2.x-dev
Status: Active » Closed (duplicate)

Yep, same thing as that issue. This is the best "steps to reproduce" I've seen, as I haven't had time to work on reproducing it myself recently so I'm going to point to this for KarenS's request for how to reproduce the problem.