PHP mySQL - UNIX_TIMESTAMP is not equal to strtotime
Solution 1
Probably the timezones of your MySQL server and your php instance differ. e.g.
echo date('Y-m-d H:i:s T', 1262657193);
prints on my machine 2010-01-05 03:06:33 CET
(note the CET timezone, that's UTC+1) while your MySQL server interprets the same unix timestamp as 2010-01-05 07:36:33
see http://dev.mysql.com/doc/refman/5.1/en/time-zone-support.html
and http://docs.php.net/datetime.configuration#ini.date.timezone
Solution 2
you don't need to convert the sql timestamp to unix for a where query. just use DATE_FORMAT it makes things much simpler:
WHERE DATE_FORMAT(`today_login_time`, "%Y-%m-%d") < '.date('Y-m-d', $my_tsp)
Solution 3
Since the difference is exactly 11.5 hours, it seems you're having issues with timezones.
Devner
Updated on June 14, 2022Comments
-
Devner almost 2 years
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.