Comparing and filtering dates in Drupal 7 Views

One of my Drupal projects was for a membership website that had different types of membership (via the (Membership Entity](https://www.drupal.org/project/membership_entity){:.weblink} module):

  • Family membership for up to two adults (18+ years old) and any number of children (<18 years old).
  • Adult membership, for a single adult (18+ years old).

The site needed to find children that turned 18, so that their membership could be changed accordingly.

I decided to use a View for this, narrowing it by people currently aged 18 who were aged <18 when their membership period started (because this will also handle other types of membership in the future e.g. Child membership). This turned out to be more complex than I expected.

Filter by a single date field

Filtering to those aged 18 or over was easy.

Field:

User: Date of birth

Filter:

Field: User: Date of birth
Operator: 'Is less than or equal to'
Relative date: today - 18 years

Calculation with two date fields

Filtering to determine whether someone was under 18 at the start of the membership requires that you compare two date fields. This proved difficult, though luckily not impossible for my purposes.

My first approach started with the assumption that I could create a field that calculated the age of a person when their membership started and then use it as a filter. Turns out you can’t do this, but I’ll document it anyway because it could be useful in the future.

I used a ‘Global: Math expression’ field, to subtract the date of birth from the membership start date.

This does not work with date fields if you use their normal date formatters, because these dates are strings and PHP will take the first number of each string to do the subtraction e.g. ‘07/08/2015’ - ‘10/08/1997’ == -3.

The dates need to be converted into Unix timestamps in order to accurately do the calculation, which leads us to the following fields:

Field: User: Date of birth
Formatter: Date and time
Choose how users view dates and times: Timestamp

To create a Timestamp option for the formatter view you need to create a new date formatter.

  1. Go to Configuration -> Date and time -> Formats tab (admin/config/regional/date-time/formats).
  2. Add format.
  3. Format string: U. The U format is the number of “Seconds since the Unix Epoch (January 1 1970 00:00:00 GMT)” i.e. a Unix timestamp.
  4. Go to Configuration -> Date and time (admin/config/regional/date-time).
  5. Add date type.
  6. Give it a name (Date type) and select the timestamp format you just created.
Field: Membership term: Start date
Date format: Custom
Custom date format: U

As mentioned above, the U format is the number of “Seconds since the Unix Epoch (January 1 1970 00:00:00 GMT)” i.e. a Unix timestamp.

To do the calculation we use a ‘Global: Math expression’ field:

Field: Global: Math expression
Expression: [start] - [field_date_of_birth]

The field will show a timestamp that give the age of the person when their membership started.

Unfortunately you can’t filter by ‘Math expression’ fields, because they are calculated after the database query.

Calculation and filtering with two date fields

To do a calculation with two date fields and use the result in a views filter, I used the Views PHP module.

You could use the fields described in the last section, in which case your Views PHP filter code (via the ‘Global: PHP’ filter) is as follows:

($row->start - $row->field_date_of_birth >= 568025000) ? true : false;

568025000 is 18 years in seconds, because we’re working with timestamps.

Alternatively ditch the complexity of those timestamps and just use the normal date fields:

Field: User: Date of birth
Field: Membership term: Start date

Views PHP filter (Global: PHP) code:

(strtotime($row->start) - strtotime($row->field_date_of_birth) >= 568025000) ? true : false;

References

Last modified: 10/08/2015 Tags: ,

This website is a personal resource. Nothing here is guaranteed correct or complete, so use at your own risk and try not to delete the Internet. -Stephan

Site Info

Privacy policy

Go to top