How can I query and order nodes with multiple date field values within a specific range?

Question

I'm trying to get nodes that have date fields (multiple instances in some cases) and display them sorted ascending by start time for a given date. For illustrative purposes, here are some events and their date field values (start time).

Event A

• 4/2/17 8:00AM
• 4/3/17 2:00PM

Event B

• 4/3/17 11:00AM
• 4/3/17 5:00PM

Event C

• 4/3/17 6:00PM

Event D

• 4/4/17 4:00AM

I'm using EntityFieldQuery to gather the events with dates on 4/3/17 and order them using this code;

$query
->entityCondition('entity_type', 'node')
->entityCondition('bundle', 'event')
->propertyCondition('status', 1)
->fieldCondition('field_datetime', 'value', array('2017-04-03/T00:00:00', '2017-04-04/T00:00:00'), 'BETWEEN')
->fieldOrderBy('field_datetime', 'value', 'ASC');

I get the nodes ordered like this;

A, B, C

but I want them ordered like this;

B, A, C

I guess the fieldOrderBy method is looking at all the date field values and ordering that way, which includes dates other than my target (4/3/17).

Anyone know how to accomplish this (with EntityFieldQuery or without)?

Answer 1

Are the date/time values stored as a varchar in your database? If I'm not mistaken, in that case the ordering is performed by comparing the String values (which is inline with the outcome of your example, i.e., A:2, B:5, C:6).

You could alter your query, using hook_query_alter(), and cast/convert the datatype to datetime (not sure whether every database supports this).

The easiest solution would be to sort the query result afterwards, using the usort() function and in your comparison function you can use PHP's DateTime object or the strtotime() to compare the values.