How to use mysql "LIKE" syntax with timestamp?

Question

I want to allow my users to search the database for a string that was sent on a specific day. The date is entered into the field in the database using a function date()that is great, but when I use the php function strtotime, of course, the dates are not exactly the same.

Is there some kind of smart mysql function that can help me?

Answer 1

I had a problem with this before.

It is best to create a start and end date, and then use the BETWEEN function.

So something like this:

$start_date = "2009-01-01 00:00:00";
$end_date = "2009-01-01 23:59:59";

$sql = "SELECT * FROM table WHERE date BETWEEN '$start_date' AND '$end_date' AND id = 'x';

, , strtotime - , ()

, :)

Answer 2

MySQL DATE() :

select ... from table ... where DATE(date_column) = '2010-01-25';

'2010-01-25' PHP, PHP 'Ymd' .

$d = date('Y-m-d', strtotime(...));

, , . PHP, , MySQL .

Answer 3

PHP:

$timestamp_from_php = strtotime('December 25, 2009');

SQL:

select
    `fields`
from
    Table t
where
    date_format('Y-m-d', t.`datetime_field`) = date_format('Y-m-d', '$timestamp_from_php')