PHP and MYSQL Find Records Based on Date

Question

I have been trying to pull records based on a specified date. In the DB I have a column where I store unix timestamps. The user has the option to select records based on a date. The user inputs: 08/08/2016 (for example)

How would I write my SQL statement to handle this?

$query .= "`".$o."` = '".date("Y-m-d",strtotime($v))."' AND ";

Here we see the line where part of the SQL statement is built, because more than one column could be targeted by the user during their search request.

Somehow I need to be able to turn $o (which is a column storing unix timestamps) into the Y-m-d format for comparison, as shown above and make this work in an SQL statement.

Any help would be appreciated.

EDIT

Here is what worked for me:

$query .= "".$o.">= '".strtotime($v)."' AND".$o."< '".(strtotime($v)+(24*60*60))."' AND ";

Possible duplicate of [Convert Unix timestamp into human readable date using MySQL](http://stackoverflow.com/questions/6267564/convert-unix-timestamp-into-human-readable-date-using-mysql) — aynber, Aug 08 '16 at 18:17

score 0 · Answer 1 · answered Aug 08 '16 at 18:21

You can do something like:

"WHERE DATE(`$o`) = '".date("Y-m-d",strtotime($v))."'"

However this won't use indexes, so if you have a large table it will be slower. If you want to be able to use indexes you can do:

"WHERE `$o` >= ".date("Y-m-d",strtotime($v))."
  AND `$o` < ".date("Y-m-d",strtotime($v))." + INTERVAL 1 DAY"

score 0 · Answer 2 · answered Aug 08 '16 at 18:29

0

You can also try this approach and use UNIX_TIMESTAMP on MySQL.

$v = '2016-08-04';
$query .= "'".$o."' = UNIX_TIMESTAMP('$v') AND ";

answered Aug 08 '16 at 18:29

Kent Aguilar

5,048
1
33
20

PHP and MYSQL Find Records Based on Date

2 Answers2