How to retrieve datetime from database in Drupal? 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-&gt;fields('p', array('submissions', 'date_time'));
$query-&gt;condition('p.user_id', $user-&gt;uid);
$results = $query-&gt;execute()-&gt;fetchAll();
foreach($results as $r) {
    $rows[] = array(strtotime(date("H:i:s", $r-&gt;date_time))*1000, $r-&gt;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-&gt;fields('p', array('submissions', 'date_time'));
$query-&gt;condition('p.user_id', $user-&gt;uid);

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

$results = $query-&gt;execute()-&gt;fetchAll();
foreach($results as $r) {
  $rows[] = array(strtotime(date("Y-m-d", $r-&gt;date_time))*1000, $r-&gt;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.