How do I retrieve a DateTime column from my MySQL database using Drupal Database API + PHP. The DateTime format is in YYYY-MM-DD HH:MM:SS(2013-02-20 23:59:59). I want to retrieve all the rows in the database that match a specific date, and then store the hour inside an array.

For example, I want to grab all the data from January 1, 2013. I then only want to store hours 00:00 to 23:59 inside my array, not the entire date.

I do not want to use any modules to accomplish this.. because I need to code it inside my custom module.

Thanks for your help!

Current Code:

$query = db_select('Project', 'p');
$query->fields('p', array('submissions', 'date_time'));
$query->condition('p.user_id', $user->uid);
$results = $query->execute()->fetchAll();
foreach($results as $r) {
    $rows[] = array(strtotime(date("H:i:s", $r->date_time))*1000, $r->submissions);

I plan to display the data on my Flot line graph.


If you want the benefits of a SelectQuery, but with the more fine-grained control over the SQL, you can always use the where() method to inject (sanitised) raw SQL into the WHERE clause. e.g

$query = db_select('Project', 'p');
$query->fields('p', array('submissions', 'date_time'));
$query->condition('p.user_id', $user->uid);

// Fetch only records where date_time is greater than 30 days ago
$query->where('date_time > DATE_ADD(NOW(), INTERVAL -30 DAY)');

$results = $query->execute()->fetchAll();
foreach($results as $r) {
  $rows[] = array(strtotime(date("Y-m-d", $r->date_time))*1000, $r->numberOfPackages);

I don't know of any 'structured' way to get those sort of advanced conditions into a SelectQuery object so resorting to plain ol' SQL is necessary sometimes.

  • I know this question is more PHP related but... after retrieving the rows with the matching date, do you know how to format it so that I only store the Hour:Minutes:Seconds in my array? – John Smith Feb 20 '13 at 9:37
  • 1
    date('H:i:s', $r->date_time) should do the trick - there's a list of all the available formats in the docs if you're interested – Clive Feb 20 '13 at 9:47

The code below works for me for fetching a group, so you can quickly adapt it for your own purpose.

$query = db_select('og')
    ->fields('og', array('etid'))
    ->condition('gid', $gid,'=');
$results = $query->execute();

$result = $results->fetchAssoc();
  • This doesn't address the question... – Clive Feb 20 '13 at 9:29

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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