Reputation: 7255
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.
Upvotes: 2
Views: 10363
Reputation: 33342
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.
Upvotes: 0
Reputation: 96189
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
Upvotes: 7
Reputation: 6887
Since the difference is exactly 11.5 hours, it seems you're having issues with timezones.
Upvotes: 1
Reputation: 16015
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)
Upvotes: 1