PHP mySQL - UNIX_TIMESTAMP is not equal to strtotime

Question

Hi all,

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.

Answer 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

Answer 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)

Answer 3

Since the difference is exactly 11.5 hours, it seems you're having issues with timezones.

Answer 4

The format date("Y-m-d H:i:s") does not accurately describe a date and time because it does not include a timezone. Since no timezone is provided, both the web server and database are using their timezones which are clearly different.

You should consider storing the dates in the database as a unix timestamp. A unix timestamp is just the number of seconds that have elapsed since the Unix Epoch (January 1 1970 00:00:00 GMT). That is not effected by timezones or daylight-savings. This way, you will only have to worry Timezones and daylight-savings when converting to an actual calendar day and time.