I'm passing a date (without hours or seconds) or date range as a contextual argument to a View.

When the underlying date object in the content field is a normal date, then an argument (start) date that is identical to the field date of the viewed content matches, as you'd expect.
When the underlying date object in the field is a Unix timestamp, then it doesn't match.

So if I have a view of, say, articles, to which I have added a Date (Unix timestamp) field and have entered 11 Mar 2012 for a number of article nodes and then filter the view via a default fixed contextual argument of 20120311, then the content does not show up in the View. I have to go back a day and enter 20120310--20120311 for the content to appear.
When the date field is a 'plain' Date, then 20120311 or even 20120311--20120311 does match 11 Mar 2012 and the content does show up in the View, as expected.

Rik

PS: Keep up the great work on a great module!

CommentFileSizeAuthor
#5 basic page date fields.jpg202.1 KBrdeboer
#5 date field settings.jpg244.14 KBrdeboer
#5 contextual filter.jpg243.99 KBrdeboer
#5 views definition.jpg284.39 KBrdeboer
#5 views output.jpg292.72 KBrdeboer

Comments

rdeboer’s picture

rdeboer
Location Melbourne
commented
Title: Start date not inclusive for Dates based on Unix Timestamp » Start date not inclusive for Dates based Unix Timestamp

The Date (ISO) works perfectly too, i.e. this where clause as generated by Views via the Date module works as expected:

...
WHERE (( (DATE_FORMAT(STR_TO_DATE(field_data_field_date_iso.field_date_iso_value, '%Y-%m-%dT%T'), '%Y-%m-%d') >= '2012-03-11' AND DATE_FORMAT(STR_TO_DATE(field_data_field_date_iso.field_date_iso_value, '%Y-%m-%dT%T'), '%Y-%m-%d') <= '2012-03-11') ))

But when a Date (Unix Timestamp) is used, the equivalent STR_TO_DATE function, i.e FROM_UNIXTIME, does not produce the right input for DATE_FORMAT when used as a start date.

karens’s picture

karens commented
Status: Active » Postponed (maintainer needs more info)

First of all '20120311' is not the format used for date arguments, it is '2012-03-11'. But even using the wrong format I can get the right results in the latest code.

Start by getting the very latest code (the 'dev' version) and try again. If you still have trouble there I need specific details about how to reproduce the problem, which is going to include details about what timezone you have for the site timezone, exactly how you configured the date field, and exactly how you set up the view. Along with an example of the specific value you entered into the date field.

karens’s picture

karens commented
Status: Postponed (maintainer needs more info) » Closed (cannot reproduce)

No response, closing.

rdeboer’s picture

rdeboer
Location Melbourne
commented

Sorry for the delay. Re-opening as this is still an issue in 7.x-2.6.

Happy to use format_date($timestamp, 'custom', 'Y-m-d') rather than format_date($timestamp, 'custom', 'Ymd') -- both work perfectly for plain and ISO dates and both demonstrate the problem with Unix dates.

Basically when I programmatically pass a date range from, say, 24 Oct to 25 Oct, then it gets interpreted in the Views query as if I passed 25 Oct to 26 Oct.

I will prepare a reproduceable test case.

rdeboer’s picture

rdeboer
Location Melbourne
commented
Title: Start date not inclusive for Dates based Unix Timestamp » Start and End dates refer to next date for Dates based on Unix Timestamp
Version: 7.x-2.2 » 7.x-2.6
Status: Closed (cannot reproduce) » Active
StatusFileSize
new views output.jpg292.72 KB
new views definition.jpg284.39 KB
new contextual filter.jpg243.99 KB
new date field settings.jpg244.14 KB
new basic page date fields.jpg202.1 KB

Following on from #4 above.

Here's a test case (see screenshots)

a) I added to core's Basic Page content type, two fields: DateISO and DateUnix. Year/Month/Day only, no hours/mintuse/seconds. No End Collection.
b) I created two pieces of content by this type. On the first I set both the DateISO and DateUnix to 25 Oct 2012, on the second I set both these dates to 26 Oct 2012
c) I created a View that displays Basic Pages in a table format.
d) On the View I added a Contextual Filter for the DateUnix field with a date range set to 24 to 25 Oct
e) I observe the output and find that I'm seeing the content for 26 Oct, 1 day beyond the filter range.

If I set the Context Filter for DateISO with exactly the same date range it shows me only the content for 25 Oct, which is what I expect.
views output
The timezone in all of the above is Australia/Melbourne

rdeboer’s picture

rdeboer
Location Melbourne
commented

PS: the reason why this is espicially important is that the Views Global Filter module relies on the contextual filter.

rdeboer’s picture

rdeboer
Location Melbourne
commented
Issue summary: View changes

Mention hours & seconds

anou’s picture

anou
Primary language French
Location Collonges au Mont d'Or
commented
Issue summary: View changes

Hello,
did you find any solution for date field based on Unix Timestamp?

rdeboer’s picture

rdeboer
Location Melbourne
commented

@anou:
Nope.

anou’s picture

anou
Primary language French
Location Collonges au Mont d'Or
commented

I've used hook_views_query_alter to make it "right".

My context is:

  • 1 content type with a date field based on Unix Timestamp, no end date.
  • 1 view, page display, with contextual filter - e.g.: 2015-07-03 - (Date (node)) compare to that field.

Here is my simple (and repetitive code):

/**
 * Implements hook_views_query_alter(&$view, &$query)
 */
function MYMODULE_views_query_alter(&$view, &$query) {
  if( $view->name == 'MYVIEWSNAME' && $view->current_display == 'page') {
    $arg = &$query->where['date']['conditions'][0]['value'][':node_date_argument'];
    $arg1 = &$query->where['date']['conditions'][0]['value'][':node_date_argument1'];
    
    $tmp = strtotime($arg) - (24*60*60); //argument minus one day to trick the timestamp field
    $tmp1 = strtotime($arg1) - (24*60*60); //same trick ;-)
    
    //and back to date
    $arg = format_date($tmp, 'custom', 'Y-m-d');
    $arg1 = format_date($tmp1, 'custom', 'Y-m-d');

  }
}

And thanks RdeBoer to answer me so fast on this old issue ;-).

rdeboer’s picture

rdeboer
Location Melbourne
commented

Thank you for your solution!