PLATFORM: PHP & mySQL
I am storing the date+time in database in the following format: date("Y-m-d H:i:s"). An example of a value that I have in my DB is : 2010-01-05 07:36:33. In my script, I have set the timezone as date_default_timezone_set("America/Chicago");
THE PROBLEM:
I read about the UNIX_TIMESTAMP somewhere and I was using that in my query. The value of UNIX_TIMESTAMP on a date value from the DB, seems to be different from the strotime(DB date Value).
EXAMPLE:
Consider that one of the DB values for the date column in my DB is 2010-01-05 07:36:33 Now if I fetch this date in the following way:
$result = mysql_query("SELECT date, UNIX_TIMESTAMP(date) AS ut_date FROM table");
$row = mysql_fetch_row($result);
//The result of this is:
$row['date'] = 2010-01-05 07:36:33
$row['ut_date'] = 1262657193
strtotime($row['date']) gives 1262698593
For my further calculations within my application, I need to work with strtotime(date). I have many comparisons to do that way. My problem would have solved, had the UNIX_TIMESTAMP(date) was same as strtotime(date). One of the sample query that I need to work with, is:
$gap = 1; // time in minutes
$tm2 = date ("Y-m-d H:i:s", mktime (date("H"),date("i")-$gap,date("s"),date("m"),date("d"),date("Y")));
$target = strtotime($tm2);
$result2 = mysql_query("UPDATE table2 SET stat = 0 WHERE UNIX_TIMESTAMP(today_login_time) < $target ");
The above is giving me incorrect results. If I try to replace UNIX_TIMESTAMP with strtotime in the above query, it gives me an error as the function strtotime seems to be PHP function and not respective mySQL function. Is there a respective mySQL function for the strtotime ? How do I solve the above problem? The code to solve the above problem is highly appreciated.
Thank you in advance.
