CODEWITHSUNDEEP
phpmysql
ackerchez
ackerchez

Reputation: 1744

php date and mysql timestamp comparison

How can I do a select from a db field that uses a timestamp when all I have from php is a date? 

$date = 2012-10-03;

something like:

"select value from table where completed_date = $date"

only the value for completed_date is really something like "2012-10-03 02:16:10" what I really would like is all values that are the same day as $date and for the query to almost disregard the time aspect of the timestamp for this query. Is that possible?

Thanks!

Upvotes: 1

Views: 6217

Answers (6)

Mike Brant
Mike Brant

Reputation: 71384

I like coder1984's answer as one that doesn't require a schema change. I would however suggest just adding a field in your database that is a date field if this is how you are typically going to query the data.

Upvotes: 0

Marc B
Marc B

Reputation: 360672

Given that your provided date is already a valid mysql datestamp, it can be as simple as:

SELECT ... WHERE DATE(completed_date)='2012-10-03'

Upvotes: 2

Tim Withers
Tim Withers

Reputation: 12059

If completed_date is a timestamp field, not varchar, then you could do something like this:

"SELECT value FROM table WHERE completed_date>=? AND completed_date<?"

And bind in the parameters:

$date="2012-10-03";
$start=date("Y-m-d G:i:s",strtotime($date));
$end =date("Y-m-d G:i:s",strtotime($date.' +1day'));

Upvotes: 0

Kermit
Kermit

Reputation: 34055

If your database field is a Unix timestamp, you would use FROM_UNIXTIME detailed here.

If your databse field (col) is of datetime type, you would SELECT ... WHERE col LIKE '2012-10-03%'.

If your database field (col) is of date type, you would SELECT ... WHERE col = '2012-10-03'.

Upvotes: 0

Teena Thomas
Teena Thomas

Reputation: 5239

you need to try something like this,

"select value from table where completed_date like '$date%'"

Upvotes: 1

poudigne
poudigne

Reputation: 1766

use DATE_FORMAT to format mysql date: 

"select value from table where DATE_FORMAT(completed_date,'%Y-%m-%d') = $date"

Upvotes: 0

Related Questions