from my content type, i add a date field called "start date" with unix timestamp type.

after i create a view than i add a expose filter into its view with my "start date", and set it up as "is greater than or equal to" from operator.

the problem is like when my node's "start date" value is 2014-July-01, than i use my view to setup the expose filter value same as 2014-July-01. the result come with nothing.

if i setup the filter with 2014-June-30, than the node's information will come out.

however, if i change the field type to ISO format, the result from view will be correct.

anyone knows how this happened?

Support from Acquia helps fund testing for Drupal Acquia logo

Comments

hmdnawaz’s picture

This also happens for me.
When I use UNIX Timestamp type date field in views filter then it displays wrong results.

when I change it to simple date type field then it works fine.

parisek’s picture

Title: Filter for UNIX Timestamp » Date filter for UNIX Timestamp show wrong results
Component: Miscellaneous » exposed filters

I can confirm I'm facing same problem

parisek’s picture

Title: Date filter for UNIX Timestamp show wrong results » Date filter for UNIX Timestamp show wrong results because bad timezone handling
Related issues: +#1983634: Date queries reset to UTC+0, regardless of site's time zone configuration in views_get_timezone?

I found, that everything is caused by bad timezone handling (date module do not save timestamp in UTC format as required, but as system timezone). In custom queries is everything fine, but views module uses function views_get_timezone() to reset timezone offset to zero (more information in related issues). This is problem only if timestamp falls to extreme values like "2015-07-01 00:00:00" which I cannot filter with views filter set to "2015-07-01".

I proposed solution, but I think this method is not enough universal to fulfill all possible situations (generally unix timestamp is outdated format, but I want to use it because entity metadata wrapper do not support DATETIME format). Search this code in date/date_api/date_api_sql.inc and add timezone offset.

function sql_field($field, $offset = NULL, $comp_date = NULL) {
...
       case DATE_UNIX:
            $offset = date('Z'); //get server timezone offset
            $field = "FROM_UNIXTIME($field+$offset)";
            break;
...
taylormadeapps’s picture

The patch by Parisek has worked for me. I was facing pretty much the same problem with the Views Date filters. My dates where in UNIX Timestamp format. Days of 1st of the month were showing up in the previous month when I was using monthly granularity.
Does anybody have any comments why I should not push for this to be submitted to the Date Module release branch.

LOBsTerr’s picture

I also faced the same issue, when I used filters in the view and offset is not taken into account. The proposed solution from comment #3 has solved the issue, but in this case it should be moved to date module issues.

taylormadeapps’s picture

Bump, anyone up for getting this patch into the next release of the date module.
I'm a noob to this community - so if any wants to humour me what I should do to progress this I'm happy to do the leg work.

dasj19’s picture

As the solution presented in #3 might have unwanted consequences, I did change the file date_views/includes/date_views_filter_handler_simple.inc
And passed the timezone offset to sql_field as a parameter instead.

$field = $this->date_handler->sql_field($field, NULL, $comp_date);
changed to
$field = $this->date_handler->sql_field($field, $comp_date->getOffset(), $comp_date);
parisek’s picture

Project: Views (for Drupal 7) » Date
Version: 7.x-3.x-dev » 7.x-2.x-dev
Component: exposed filters » Date API
Assigned: Unassigned » parisek
DamienMcKenna’s picture

Assigned: parisek » Unassigned
klonos’s picture