how to compare datetime?

muratisik

Hello,
I am a newbei to php. I thought about asking this in the newbei section but it looked more likw a database question. I am trying to compare a user entered datetime with dates from a mysql table:

$date_s=$year_s."-".$month_s."-".$day_s." ".$hour_s.":".$min_s.":".$sec_s;
$date_e=$year_e."-".$month_e."-".$day_e." ".$hour_e.":".$min_e.":".$sec_e;

$result = mysql_query("select * from track where DateTime=>'$date_s' AND DateTime=< '$date_e'") or
die (mysql_error());

The $date_s and $date_e are perfectly formed by the first two lines since I checkd themn with echo....
The mysql_query line generates the following error whenever queried:

You have an error in your SQL syntax near '=>'2003-11-17 14:02:33' AND DateTime=< '2003-11-17 14:04:04'' at line 1

The column named DateTime is in the data type of datetime. I tried many diffrent forms of =< and => in case they might be the problem. However I got the same error. Is there anything extra to do to compare datetime format, since with very similar lines I can easily compare doubles or integers?

I also did something else. I divided the datetime coloumn into all parts like year-month and so on. Than I divided the time region into 8 parts like seconds completing to 60, then minutes completing to 60, then hours completing to 24 and so on:

$result1 = mysql_query("select * from track4 where $min_s<=minutes AND minutes<=60 AND

day=$day_s AND hours=$hour_s AND month=$month_s AND year=$year_s") or
die (mysql_error());

$result2 = mysql_query("select * from track4 where hours>$hour_s AND hours<=24 AND day=$day_s

AND month=$month_s AND year=$year_s") or
die (mysql_error());

$result3 = mysql_query("select * from track4 where day>$day_s AND day<=31 AND month=$month_s AND year=$year_s")

or
die (mysql_error());

$result4 = mysql_query("select * from track4 where $month_s<month AND month<$month_e AND year=$year_s") or
die (mysql_error());

$result5 = mysql_query("select * from track4 where $year_s<year AND year<$year_e") or
die (mysql_error());

$result6 = mysql_query("select * from track4 where day>=1 AND day<$day_e AND month=$month_e AND year=$year_e") or
die (mysql_error());

$result7 = mysql_query("select * from track4 where hours>=0 AND hours<$hour_e AND day=$day_e AND
month=$month_e AND year=$year_e") or
die (mysql_error());

$result8 = mysql_query("select * from track4 where minutes>=0 AND minutes<=$min_e AND
hours=$hour_e AND day=$day_e AND month=$month_e AND year=$year_e") or
die (mysql_error());

However a php-knowing friend of mine told me that I can send only two mysql_query to mysql in a single php code. However this works. Is there such a limit really and if limit is passed beyond (like I did with seven queries) is there any problem with that?

However since my actual goal is to do it in one line, is there way to get the first option working?

Have a nice day.

Murat Isik

Pig

MySQL has some funky syntax around comparing formatted timestamps. If you search the manual, you will find what you are looking for.

However, you can bypass the problem altogether by storing your dates as unix timestamps. Unix timestamps are superior in every way to formatted timestamps except for not being human readable, and having a limitted range (although it's limit is pretty big). Using this format allows you to easily compare times, add/subtract time, and format it any way you want using date(). Because it is expressed as a simple integer, you can always say >, <. Also, what if you needed to express your date in the format:
Mon 10/28/2003 14:56
If you stored it in a unix timestamp, you could format it anyway you want with no effort.
date("D m/d/Y H:i", $timestamp);
Store it as a timestamp in a varchar field, and use date() to display it.

Sxooter

Have you tried using between?

select * from table where date between 'startdate' and 'enddate'

muratisik

yes, that worked. thank you very much🙂

Sxooter

You're welcome. First time I saw between I was surprised that something so logical and intuitive had snuck into the SQL spec. 🙂