Hello,

I have a table called "drupal_timestamps" with column "timestamp". Structure is as mentioned below.

Timestamp
12345567671
12345567672
12345567673
12345567674
12345567675
12345567676

Requirement : I have to,

Select from drupal_timestamps where timestamp if of any monday of calender. And it needs to be done with DB_SELECT ONLY.

p.s : Fetching from DB table should produce result and no external PHP code to be used after fetching record from DB.

Kindly Help !

Comments

Rob230’s picture

SELECT * FROM drupal_timestamps WHERE DAYOFWEEK(FROM_UNIXTIME(timestamp)) = 2;

DAYOFWEEK gives a number from 1 to 7 where 1 = Sunday.

Ayesh’s picture

Here is how you can use it in a db_select()-returned object.

//$query = db_select();//initialize the object as you need, add conditions, etc.
$query->where('DAYOFWEEK(FROM_UNIXTIME(timestamp)) = :dayid', array(':dayid' => 2));
kunalkursija’s picture

Hi Rob & Ayesh,

I Just quickly tried in devel PHP on users table & That did solve my problem, Thank you :) . This is what i tried...

@ayesh :

// Selecting from users table where users were created on a monday
$query = db_select("users","u");
$query->fields("u",array("uid","created"));
$query->where('DAYOFWEEK(FROM_UNIXTIME(created)) = :dayid', array(':dayid' => 3));
$output = $query->execute()->fetchAll();

// printing the users.
echo "<pre>";
print_r($output);
echo"</pre>";

But i did not understood what

:dayid

is..

Is that some sort of alias ? if yes then fetching record did not yield any thing called dayid in output.

Ayesh’s picture

That is the parameter. This way, Drupal DB API can sanitize your variable (3 in this case) before running the query.