View Full Version : Comparing UNIX time within a MySQL query.

11-28-2010, 01:38 PM

I have a table where the date is stored as UNIXTIME. I would now want to search the database for specific dates but the problem is some of the dates are stored in the database with hours and minutes. For example the following dates are saved in the Database:

10-11-2010 04:00 PM stored as (1291046400)

When I want to search for the date 10-11-2010 I want it to show the above date, is there any built-in MySQL functions that I can use to achieve this?

Help is much appreciated. Thanks in advance

11-28-2010, 02:11 PM
You can call strtotime($date_string) to get the UNIX timestamp of a date string, like:

$date_string = '10-11-2010 04:00';
$date_timestamp = strtotime($date_string);
$query = "SELECT * FROM myTable WHERE timestamp='{$date_timestamp}'";

11-28-2010, 02:22 PM
I know strtotime(), the problem is I don't want to pass the hours/minutes and just want to search by date. Thanks

11-28-2010, 02:34 PM
$date_string = '10-11-2010 04:00';
$date_only = date('Ymd',strtotime($date_string));
$query = 'SELECT * FROM `myTable` WHERE DATE_FORMAT(FROM_UNIXTIME(`timestamp`),"%Y%m%d") = ' . $date_only;

11-28-2010, 02:40 PM
LOL, I am saying I don't have the hours and minutes, my $date_string looks like

$date_string = '10-11-2010';

Does it make sense?

11-28-2010, 02:53 PM
That isn't what you said but it shouldn't matter. strtotime will still work. Since you are ignoring the time anyway and not using it it won't matter what strtotime will use as the default time.