How to use the mysql "LIKE" syntax with a timestamp?

Question

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

Is there some clever mysql function that can help me with this?

Answer 1

Why not convert the timestamp generated by strtotime() into a MySQL formatted date? That way you're comparing apples to apples.

$mysql_formatted_date = date("Y-m-d", $timestamp);

Edit: If the timestamp is in the database use MySQL's date_format() to accomplish the same thing:

...    
WHERE DATE_FORMAT("y-m-d", timestamp_field) = $date_format

Answer 2

I had an issue with this before.

You're best to generate a start and end date then use the BETWEEN function instead.

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';

Of course you would just pull your dates from the DB using strtotime and append the time stamps - depends how you used date()

Hope this helps :)

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')

Answer 4

You can use MySQL's DATE() function to extract date part of the timestamp:

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

If you have problem submitting '2010-01-25' from PHP, you can use PHP's date function with 'Y-m-d' as parameter to only get the date part.

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

Looking at your question closely, it seems you'll need both of those. First use PHP's date function to get only the date part and then use MySQL's date to match only those records.