Week views - nodes are display in more then one week [#1588198]

Hello,

I created a node type with Date field with year, month and day. Then a created a view which displays created nodes. I use Page by date and contextual filter set to week. I use Use ISO-8601 week numbers.

I am not suer where the problem is but some nodes are in two weeks. Such nodes have day set to Monday. I checked their value in mysql table and it is set to "2012-05-14 00:00:00".

I copied SQL queries from the view.

Previous week

SELECT node.nid AS nid, node.type AS node_type, node.title AS node_title, uc_products.sell_price AS uc_products_sell_price, 'node' AS field_data_field_slozeni_node_entity_type, 'node' AS field_data_field_den_dostupnosti_node_entity_type, 'denni_nabidka:block' AS view_name
FROM 
 node
INNER JOIN og_membership as og_membership_node ON node.nid = og_membership_node.etid AND og_membership_node.entity_type = 'node'
LEFT JOIN field_data_field_den_dostupnosti as field_data_field_den_dostupnosti ON node.nid = field_data_field_den_dostupnosti.entity_id AND (field_data_field_den_dostupnosti.entity_type = 'node' AND field_data_field_den_dostupnosti.deleted = '0')
LEFT JOIN uc_products as uc_products ON node.vid = uc_products.vid
WHERE (( (og_membership_node.gid = '1' ) AND (DATE_FORMAT(field_data_field_den_dostupnosti.field_den_dostupnosti_value, '%Y-%m-%d %H:%i:%s') >= '2012-05-07 00:00:00' AND DATE_FORMAT(field_data_field_den_dostupnosti.field_den_dostupnosti_value, '%Y-%m-%d %H:%i:%s') <= '2012-05-14 00:00:00') )AND(( (node.status = '1') )))
ORDER BY node_type DESC

Current week

SELECT node.nid AS nid, node.type AS node_type, node.title AS node_title, uc_products.sell_price AS uc_products_sell_price, 'node' AS field_data_field_slozeni_node_entity_type, 'node' AS field_data_field_den_dostupnosti_node_entity_type, 'denni_nabidka:block' AS view_name
FROM 
 node
INNER JOIN og_membership as og_membership_node ON node.nid = og_membership_node.etid AND og_membership_node.entity_type = 'node'
LEFT JOIN field_data_field_den_dostupnosti as field_data_field_den_dostupnosti ON node.nid = field_data_field_den_dostupnosti.entity_id AND (field_data_field_den_dostupnosti.entity_type = 'node' AND field_data_field_den_dostupnosti.deleted = '0')
LEFT JOIN uc_products as uc_products ON node.vid = uc_products.vid
WHERE (( (og_membership_node.gid = '1' ) AND (DATE_FORMAT(field_data_field_den_dostupnosti.field_den_dostupnosti_value, '%Y-%m-%d %H:%i:%s') >= '2012-05-14 00:00:00' AND DATE_FORMAT(field_data_field_den_dostupnosti.field_den_dostupnosti_value, '%Y-%m-%d %H:%i:%s') <= '2012-05-21 00:00:00') )AND(( (node.status = '1') )))
ORDER BY node_type DESC

A node with date set to '2012-05-14 00:00:00' fulfils both WHERE conditions and is displayed in both weeks. The conditions should be change to

WHERE (( (og_membership_node.gid = '1' ) AND (DATE_FORMAT(field_data_field_den_dostupnosti.field_den_dostupnosti_value, '%Y-%m-%d %H:%i:%s') >= '2012-05-07 00:00:00' AND DATE_FORMAT(field_data_field_den_dostupnosti.field_den_dostupnosti_value, '%Y-%m-%d %H:%i:%s') < '2012-05-14 00:00:00') )AND(( (node.status = '1') )))

(I just deleted one equal sign before 2012-05-14 00:00:00)

I tested it.

Comment	File	Size	Author
#3	view_hours_test.txt	10.3 KB	audealexandre

Support from Acquia helps fund testing for Drupal Acquia logo

Comments

Comment #1

hctom

German

CreditAttribution: hctom commented 3 July 2012 at 14:02

I can confirm exactly the same problem for date field which do not collect time values.

Cheers

hctom

Comment #2

KarenS CreditAttribution: KarenS commented 12 August 2012 at 18:35

Status:

Active

» Postponed (maintainer needs more info)

Not enough information to reproduce a view with a problem. There are lots of things that could be happening, lots of possible kinds of date field settings and date values you could be using, lots of possible ways you might have constructed the view.

Comment #3

audealexandre CreditAttribution: audealexandre commented 13 May 2013 at 13:17

File	Size
view_hours_test.txt	10.3 KB

Hi,

I post a bug report in the calendar module which describes exactly this problem: http://drupal.org/node/1993402. Sorry if it was the wrong place. So I describes it here again, with more details, hoping it will help to solve the problem:

I use:
- calendar 7.x-.3.4
- data API 7.x-2.6

I use the calendar view to display opening times by week. Therefore, I created a content type called "opening_hours" with date values (begin_date and end_date) and display it in the calendar for the current week. The begin_date is always a monday, and the end_date always a sunday, both dates are part of the "filed_period_hours".

(The view is attached if it can help).

The contextual filter of the view is configured as follow:
Date : Date (node) (Contenu: Semaine(s) concernée(s) - date de début (field_period_hours)) (which is the begin_date)
- when the filter is not in the url: defaut filter: current date
- granularity: week
- dates to compare: date interval begin/end (but I also tried with one field and the query is wrong the same way)
- Add an identifier for multiple values: no (but it's the same with 'yes' in my case)
- Date field: Contenu: Semaine(s) concernée(s) - date de début (field_period_hours)
- Method: OR

The problem arise with 2 different contents with dates following each other:
ex:
content1: monday 2013-05-13 - sunday 2013-05-19.
content2: monday 2013-05-20 - sunday 2013-05-26.

With the 2013-W20 as contextual filter (week of May, 13-19), the content1 AND content2 are displayed.
Indeed, the view query writes:
"(DATE_FORMAT(field_data_field_period_hours.field_period_hours_value2, '%Y-%m-%d %H:%i:%s') >= '2013-05-13 00:00:00' AND DATE_FORMAT(field_data_field_period_hours.field_period_hours_value, '%Y-%m-%d %H:%i:%s')<= '2013-05-20 00:00:00') )"

in place of:
"(DATE_FORMAT(field_data_field_period_hours.field_period_hours_value2, '%Y-%m-%d %H:%i:%s') >= '2013-05-13 00:00:00' AND DATE_FORMAT(field_data_field_period_hours.field_period_hours_value, '%Y-%m-%d %H:%i:%s')<= '2013-05-19 00:00:00') )"
OR
"(DATE_FORMAT(field_data_field_period_hours.field_period_hours_value2, '%Y-%m-%d %H:%i:%s') >= '2013-05-13 00:00:00' AND DATE_FORMAT(field_data_field_period_hours.field_period_hours_value, '%Y-%m-%d %H:%i:%s')< '2013-05-20 00:00:00') )"
.

Thanks in advance for your help,