...

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



muneeba9071
11-28-2010, 01:38 PM
Hi,

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

jim_keller
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}'";

muneeba9071
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

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

muneeba9071
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?

djm0219
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.



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum