MySQL noob question

[jharker1987]

hi guys

 

I have a simple mysql table set up, along the lines of

 

Col 1 - Col 2 - Date_last_updated

--------------------------------

Som - Some - 06/11/2009 23:23:23

Bob - Bob - 06/11/2009 23:30:30

 

 

and so on...

 

I am trying to put an mysql statement together that reads along the lines of

 

SELECT * FROM table WHERE (date_last_updated > 01/01/01 23:23:23) AND (date_last_updated < 02/01/01 00:00:00)

 

 

how do I go about doing this?

 

thanks

 

(hope I was clear and concise)

[PFMaBiSmAd]

You can only do greater-than/less-than date comparisons when the date format has the fields making up the date ordered left to right, year, month, day. This is one reason why the mysql DATE and DATETIME formats use YYYY-MM-DD.

 

You first step will be to store the date/times using a DATETIME data type and then you must put the dates you trying to compare against in the a YYYY-MM-DD format. You will also need to use the mysql DATE() function on your DATETIME values so that the comparison will just use the date part.

[jharker1987]

Ok

 

thanks for the reply, I have taken into consideration what you have written and come up with the following code:

 

$res = mysql_query("SELECT * FROM `4c0ac655-41d1-4625-95bb-fe9a339728c5`");
$num = mysql_num_rows($res);

for($i = 0; $i < $num; $i++)
{
$time = mysql_result($res,$i,"DATE_TIME_UPDATE");
echo strtotime(date($time)) . " - " . $time . "<br />";
}

 

So the output looks something like this:

 

1231654254 - 01/11/09 06:10:54

 

So now that I have the unix version of the date/time, I can do a straight comparison.

 

Its important to note that I need the date and the time.

 

I have nailed this one yet, and I don't think its the most effective way to do things.  If anybody has anything to add I would be very grateful