Take the 2-minute tour ×
Drupal Answers is a question and answer site for Drupal developers and administrators. It's 100% free, no registration required.

I have an entity and one of it's properties is a timestamp, set when it was created.

I want to use EntityFieldQuery to load entities that were created today.

Normally if I was writing a query to do this I would use:

DATE_FORMAT(FROM_UNIXTIME(created_timestamp), "%Y-%m-%d") = "2014-03-17"

However I'm not aware of any method to use any MySQL functions in EntityFieldQuery, so instead I've used:

$day_start = strtotime(date('Y-m-d 00:00:00', $created_ts));
$day_end = strtotime(date('Y-m-d 23:59:59', $created_ts));
$query
    ->propertyCondition('created', $day_start, '>')
    ->propertyCondition('created', $day_end, '<');

Whilst this works it doesn't seem as efficient and certainly less succinct. Is there a function that I can use with EntityFieldQuery similar to dynamic queries where function that would allow me to do something like:

$query
    ->propertyWhere('DATE_FORMAT(FROM_UNIXTIME(created), "%Y-%m-%d")', "2014-03-17");
share|improve this question
    
You may or may not have problems with this general approach due to timezones (eg, database storing UTC and having $created_ts reflect local time). –  MPD Mar 17 at 13:51
    
Good point - I'll have to be aware of this and fix if/when required. –  Felix Eve Mar 17 at 14:02
add comment

2 Answers 2

up vote 1 down vote accepted

You can use below code , PropertyCondition accepts range values using BETWEEN operator.. Since timestamp is stored as int in database node table created column with data and time together below code works to fetch records of a particular date..

<?php
$day_start = strtotime(date('Y-m-d 00:00:00', $created_ts));
$day_end = strtotime(date('Y-m-d 23:59:59', $created_ts));
$query
    ->propertyCondition('created', array($day_start, $day_end), 'BETWEEN');
?>

Find more about propertyCondition in D.O

share|improve this answer
    
This unquestionably more succinct :) –  Felix Eve Mar 17 at 10:26
add comment

No there's no such method in EntityFieldQuery. If you take your code here:

$query
  ->propertyWhere('DATE_FORMAT(FROM_UNIXTIME(created), "%Y-%m-%d")', "2014-03-17");

Just imagine what Drupal would have to do to determine which property (column) you're referring to here. It would have to parse the SQL string and pull out the created string before it can get the field meta data - arguably an expensive and unnecessary step.

Since an EntityFieldQuery is only supposed to be used for query entities by property/field, the methods provided are (necessarily) limited. I think your less-succinct method is the best you'll get without subclassing and extending.

share|improve this answer
    
I was imagining that it wouldn't have to work out which field it was at all. As this is a property and not a field Drupal knows it will refer to a field in the base table of the entity, so I was hoping it could just attach the SQL to the generated query. Why does it need the field meta data? –  Felix Eve Mar 17 at 10:23
    
Sorry you're right, it doesn't, but there's still no propertyWhere or equivalent - I imagine to avoid unnecessary confusion. I've been developing in Drupal full time for 4 years and I've never had the need to do this, for example –  Clive Mar 17 at 10:26
    
Do you think this is a feature that adds enough benefit that it could / should be added in future (in core or contrib)? –  Felix Eve Mar 17 at 10:30
    
Sure, certainly wouldn't hurt to have that extra helper method and would make things a wee bit easier in this situation for some people. –  Clive Mar 17 at 10:34
1  
In despite it is a property and not a field, Drupal would need to parse the SQL string to add the table prefix. Also it should use an alias to grant no collision. –  jonhattan Mar 17 at 11:38
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.